Oracle Data Redaction and DML and DDL Operations

Oracle Data Redaction affects DML and DDL operations, especially for users who issue SQL against tables with redacted columns.

Note the following:

• If a redacted column appears as the source in a DML operation and the target is not a redacted column, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with an ORA-28081: Insufficient privileges. The command references a redacted object error unless you have the EXEMPT REDACTION POLICY privilege.

• If a redacted column appears as the source in a DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with an ORA-28081: Insufficient privileges. The command references a redacted object error unless you have the EXEMPT REDACTION POLICY privilege.

• Oracle Data Pump issues these kinds of operations, so you may also need to grant the EXEMPT REDACTION POLICY privilege, at the schema or system level to a user if they need to perform schema-level exports of tables that have redacted columns. This privilege is included in the DATABASE_EXP_FULL_DATABASE role. See Oracle Data Redaction Policy and Object Transfers with Oracle Data Pump for more information.

• Internally, Oracle Data Mining issues DML and DDL operations, so you may need to grant the EXEMPT REDACTION POLICY privilege to a user if the user must create data mining models on tables that have redacted columns.

In the following examples, c1 does not have a redaction policy defined on it while c2 does.

Example 4-1 DML will result in ORA-28081 error

insert into t1(c1) select c2 from t2;
update t1 set c1 = (select c2 from t2);
delete from t1 where c1 in (select c2 from t2);

Example 4-2 DDL will result in ORA-28081 error

create table t3 as select * from t2;

Related Topics

• Privileges for Managing Oracle Data Redaction Policies