Defining a security policy to activate query rewrite

Learn how to create access policy rules using your query rewrite definitions with live queries.

Before you begin

To complete this task, you need to have created and tested one or more query rewrite definitions, and you need to be familiar with creating security policies.

Procedure

  1. Open Protect > Security Policies > Policy Builder.
  2. Create a new policy or modify an existing policy to use your query rewrite definitions.
    Tip: Consider creating a new policy for testing query rewrite definitions. Add your rewrite rules to existing security policies once you are satisfied with the behavior of the test policy.
  3. Click Edit Rules to begin adding rewrite rules to the selected policy, then select Add Rules > Add Access Rule.
    Note: Query rewrite rules are always classified as access rules.
  4. Add a rule with a QUERY REWRITE: ATTACH rule action. Be sure to check the Continue to next rule checkbox. This rule identifies the specific session parameters that must be matched in order to trigger a query rewrite session, for example a specific database user name or client IP address.
  5. Add a rule with one or more QUERY REWRITE: APPLY DEFINITION rule actions and select the query rewrite definition(s) you would like to apply. This rule identifies the specific objects or commands that must be matched in order to apply the rewrite definitions and modify the source query.

    For example, you can limit the data that displays back to a user when a SELECT * from EMPLOYEE query is issued. To do so, set the Object field to EMPLOYEE and create a query rewrite definition to replace the * with a list of defined columns for the data you want the user to have access to.

  6. Add a rule with a QUERY REWRITE: DETACH rule action. This detaches the query rewrite session and prevents further monitoring of session traffic. The conditions set for the detach rule should not be the same as the attach rule.
  7. To install the new policy, return to the Policy Finder, select your security policy, and choose Select an installation action > Install and Override. Click OK when asked to confirm installation of the policy.
  8. Log in to your database server and run test queries to verify that your access policy rewrite rules are functioning as intended.
    1. Log in to your database server.
    2. Issue queries that should trigger (or should not trigger) the installed access policy rules and match the criteria of your query rewrite definitions.

      For example, if you set the Object to EMPLOYEE and you issue SELECT * from EMPLOYEE, you should only see results for the columns you defined for * in the query rewrite definition. In contrast, if you issue a SELECT * from DEPARTMENT, you should see all column data returned for the DEPARTMENT object.

    3. Verify that the results reflect the rewritten SQL.