1. Data Redaction Guide
  2. Considerations for Oracle Data Redaction
  3. Oracle Data Redaction General Security Guidelines

5.1 Oracle Data Redaction General Security Guidelines

It is important to understand the general security guidelines for using Oracle Data Redaction.

  • Oracle Data Redaction is not intended to protect against users who run ad hoc SQL queries that attempt to determine the actual values by inference. Inference refers to the process that is designed to find actual data by repeatedly trying queries. For example, to find the users who earn the highest salaries, an intruder could use the following query where column salary is redacted: 

    SELECT FIRST_NAME, LAST_NAME, SALARY 
FROM HR.EMPLOYEES 
WHERE SALARY > 16000 
ORDER BY SALARY DESC;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                               0
Neena                Kochhar                            0
Lex                  De Haan                            0

  • 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 privilege.

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

5.1.1 REDACTION_COLUMNS Data Dictionary View Behavior When a View Is Invalid

When an Oracle Data Redaction policy exists on a column of a view, and the view becomes invalid, the Data Redaction policy remains visible in the REDACTION_COLUMNS data dictionary view.

For example, a view can become invalid if one of its columns refers to a column that was dropped from a table upon which the view depends.

The column continues to be visible in the REDACTION_COLUMNS data dictionary view because the Data Redaction policy is not automatically dropped when the view becomes invalid.

Instead, the decision on whether to drop the Data Redaction policy is taken when the view is subsequently altered.

This approach was chosen in preference to automatically dropping the Data Redaction policy when the view becomes invalid because it is less error-prone and presents less risk of accidentally displaying actual data from the underlying table.

By deferring the decision to when the view is being altered, it allows the view to be recompiled after the column is restored to the table. After the column is restored to the table and the view is recompiled, then the view becomes valid and still has its Data Redaction policy in place.

On the other hand, if the invalid view definition was subsequently replaced with a valid view definition which no longer contains the column that the Data Redaction policy was previously defined on, it is at that point that the Data Redaction policy is automatically dropped. The REDACTION_COLUMNS data dictionary view is then updated to no longer show the column (since it is no longer part of the new view's definition).

5.1.2 Dropped Oracle Data Redaction Policies When the Recycle Bin Is Enabled

You should check if the recycle bin is enabled before you drop Oracle Data Redaction policies.

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, you can run the SHOW PARAMETER RECYCLEBIN command in SQL*Plus.

Related Topics