Testing query rewrite definitions

Learn how to test query rewrite definitions against sample input and verify that the rewrite definitions behave as expected.

1. Open Protect > Security Policies > Query Rewrite Builder.
2. Click Set Up Test to open a dialog and select query rewrite definitions for testing.
   1. Drag and drop items from the Available query rewrite definitions field to the Test query rewrite definitions field.
   2. Drag and drop items with the Test query rewrite definitions field to order multiple definitions as you would within an access policy.
   3. Click Save to close the dialog when you are finished.
3. Type or paste test queries into the test field.

   For example, to test a rewrite definition preventing the use of SELECT * from statements (see Creating query rewrite definitions), enter sample queries such as:

   SELECT * from DEPARTMENT
   SELECT * from EMPLOYEE
   SELECT FIRSTNME, case 
   when SALARY > 150000 then 'high' 
   when SALARY > 100000 then 'medium'
   when SALARY > 80000 then 'fair' 
   else 'poor' 
   end from EMPLOYEE
   DELETE from EMPLOYEE where EMPNO=100
   INSERT into TEMP_EMP SELECT * from EMPLOYEE

4. Click Run Test to process the sample queries and review the results.

   For example, the sample queries provided in the previous step return the following results:

   Table 1. Query rewrite test results

   Original SQL	Rewritten SQL	Changed
   SELECT * from DEPARTMENT	SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX from DEPARTMENT	YES
   SELECT * from EMPLOYEE	SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX from EMPLOYEE	YES
   SELECT FIRSTNME, case when SALARY > 150000 then 'high' when SALARY > 100000 then 'medium' when SALARY > 80000 then 'fair' else 'poor' end from EMPLOYEE	SELECT FIRSTNME, case when SALARY > 150000 then 'high' when SALARY > 100000 then 'medium' when SALARY > 80000 then 'fair' else 'poor' end from EMPLOYEE	NO
   DELETE from EMPLOYEE where EMPNO=100	DELETE from EMPLOYEE where EMPNO=100	NO
   INSERT into TEMP_EMP SELECT * from EMPLOYEE	INSERT into TEMP_EMP SELECT * from EMPLOYEE	NO

   Important: Rewrite definitions are based on syntax, so any statement with the form SELECT * from [OBJECT] will match the example. For instance, both SELECT * from DEPARTMENT and SELECT * from EMPLOYEE statements match our example. Query rewrite definitions can be restricted to specific objects using access policy rules. See Defining a security policy to activate query rewrite for instructions.
5. Continue entering sample queries to test your rewrite definitions.
   Click Set Up Test to change or reorder the rewrite definitions used for the test.