IBM Guardium

 View Only

Behind the scenes of Query rewrite FunctionBehind the scenes of Query rewrite Function Query rewrite is a functionality provided by the Guardium that allows databased queries to be rewritten based on the query rewrite definition and security policy. The q

By Jay Basnet posted Fri December 13, 2024 01:34 AM

  

Query rewrite is a functionality provided by the Guardium that allows databased queries to be rewritten based on the query rewrite definition and security policy. The queries entered by the user are rewritten based on the policy and the rewrite definition on the fly and the user receives the result based on the rewritten query.

Rewriting query can be used to control the access of data based on user’s privilege. Some basic access control examples are:

-              Limiting access to rows by adding a WHERE clause

-              Limiting access to columns by modifying the SELECT list

-              Restricting database activities by rewriting SQL statements to do nothing.

-              Restricting what users can do by modifying query verbs

-              Restricting what users can do by modifying query objects  

Query rewrite is intended to work with Microsoft SQL, DB2 and Oracle. For the query rewrite to work for any of the databased mentioned, the “QUERY_REWRITE_INSTALLED” parameter should be enabled. There are 3 access policy rules related to the query rewrite.

-              QUERY REWRITE: ATTACH - when the attach rule is triggered every subsequent query is watched by query rewrite.

-              QUERY REWRITE: APPLY DEFINITION – the apply definition rule contains the rewrite definition that needed to be applied. If the query matches the definition it is rewritten.

-              QUERY REWRITE: DETACH – when the detach rule is triggered query rewrite stops watching activity

The attach rule is not necessary when the “QUERY_REWRITE_DEFAULT_STATE” is enabled, all the query is watched by query rewrite while it is enabled. It can still be detached by DETACH rule and would require ATTACH rule to be triggered for the query rewrite to continue watching it.

Example of query rewrite and how the result would change:

Original query

Expected result based on original query

Rewritten query

Result from rewritten query

SELECT * FROM TEST

Entire test table

SELECT * FROM TEST WHERE ID<20

row of the table where id is less than 20

SELECT * FROM TEST

Entire test table

SELECT NAME FROM TEST

Only Name column of the table

Query rewrite and firewall:

Both query rewrite and firewall can be enabled for default state, while both are enabled the query rewrite rules take precedence over firewall rules. Rules like session terminate will not be triggered if query rewrite is watching database.

0 comments
2 views

Permalink