6 Guidelines for Using Oracle Data Redaction

6.1 General Usage Guidelines

• Oracle Data Redaction is not intended to protect against attacks by privileged database users who run ad hoc queries directly against the database.

• Oracle Data Redaction is not intended to protect against users who run exhaustive SQL queries that attempt to determine the actual values by inference.

• Oracle Data Redaction relies on the database and application context values. For applications, it is the responsibility of the application to properly initialize the context value.

• Oracle Data Redaction is not enforced for users who are logged in using the SYSDBA administrative privilege.

• Certain DDL statements that attempt to copy the actual data out from under the control of a data redaction policy (that is, CREATE TABLE AS SELECT, INSERT AS SELECT) are blocked by default, but you can disable this behavior by granting the user the EXEMPT REDACTION POLICY system privilege.

• Oracle Data Redaction does not affect day-to-day database operations, such as backup recovery, Oracle Data Pump exports and imports, Oracle Data Guard operations, and replication.

6.2 Writing Policy Expressions That Depend on SYS_CONTEXT Attributes

Be careful when writing a policy expression that depends only upon a SYS_CONTEXT attribute that is supposed to be populated by an application, because the application might not always populate that attribute. If the user somehow connects directly (rather than through the application), then the SYS_CONTEXT attribute would not have been populated. If you do not handle this NULL scenario in the code for your policy expression, you could unintentionally reveal actual data to the querying user.

For example, suppose you wanted to create a policy expression that intends to redact the query results for everyone except users who have the client identifier value of SUPERVISOR. The following expression unintentionally enables querying users who have NULL as the value for their CLIENT_IDENTIFIER to see the real data:

SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') IS NOT 'SUPERVISOR';

A more rigorous policy expression is as follows. This expression redacts the result of the query if the client identifier is not set, that is, it has a NULL value.

SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') IS NOT 'SUPERVISOR' OR IS NULL;

Remember that in SQL, comparisons with NULL are undefined, and are thus FALSE, but redaction only takes place when the policy expression evaluates to TRUE.

6.3 Creating Policies on Materialized Views

You can create Oracle Data Redaction policies on materialized views and on their base tables. However, ensure that the creator of the materialized view, or the user who performs the refresh of the materialized view, is not blocked by any Data Redaction policies. In other words, the user performing the materialized view creation or refresh operations should be exempt from the Data Redaction policy. As a best practice, when you create a new materalized view, treat it as a copy of the actual table, and then create a separate Data Redaction policy to protect it.

6.4 Dropping Policies When the Recycle Bin Is Enabled

If you drop a table or view that has an Oracle Data Redaction policy defined on it when the recycle bin feature is enabled, and if you query the REDACTION_COLUMNS or REDACTION_POLICIES data dictionary views before you purge the recycle bin, then you will see object names such as BIN$... (for example, BIN$1Xu5PSW5VaPgQxGS5AoAEA==$0). This is normal behavior. These policies are removed when you purge the recycle bin.

To find if the recycle bin is enabled, run the SHOW PARAMETER RECYCLEBIN command in SQL*Plus.