Testing query rewrite definitions

Edit online

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

Before you begin

To complete this task, you need to have created one or more query rewrite definitions.

Procedure

  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.

What to do next

When you are satisfied with the test results, create a security policy to begin using your query rewrite definitions with live queries.