Error 'ORA-28081: Insufficient privileges - the command references a redacted object' occurs when performing DML/DDL by a schema on a table where one of the column has a redaction policy enabled (Doc ID 1922132.1)

Last updated on MARCH 15, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Error 'ORA-28081: Insufficient privileges - the command references a redacted object' occurs when performing DML/DDL by a schema on a object where one of the column has a redaction policy enabled.

If a redacted column appears as the source in a DML or DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with the error:

   ORA-28081 "Insufficient privileges - the command references a redacted object."

unless you have the EXEMPT REDACTION POLICY system privilege.

Below example demonstrates this behavior.

 

 

POLICY_NAME                    OBJECT_NAME
------------------------------ ------------------------------
redact_com_pct                 EMPLOYEES

SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMMISSION_PCT                                     NUMBER(38)

SQL>
SQL> insert into employees values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into employees values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from employees;

COMMISSION_PCT
--------------
             0
             0
             0


SQL> show user;
USER is "HR"


#### Redacted column COMMISSION_PCT appearing as source in DDL statement######################

SQL>connect hr/sys;


SQL> create table testing2 as select * from employees;
create table testing2 as select * from employees
                                *
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.


SQL> connect /as sysdba
Connected.
SQL> grant exempt redaction policy to hr;

Grant succeeded.

SQL>
SQL> connect hr/sys;
Connected.
SQL>
SQL>
SQL> create table testing2 as select * from employees;

Table created.


## Create a procedure to insert data in hr.employees. Executing the procedure errors out.Redaction policy is created on COMMISSION_PCT column.
Here COMMISSION_PCT is source column for the DML operation.Hence raising ora-28081.


SQL>connect hr/sys;

SQL> CREATE OR REPLACE PROCEDURE testp(isl in varchar2 := 'K')  IS
BEGIN
if isl = 'K' then
insert into hr.employees
select COMMISSION_PCT
from hr.employees;
end if;
END;
/  

Procedure created.

SQL>
SQL>
SQL>
SQL> exec  testp;
BEGIN testp; END;

*
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.
ORA-06512: at "HR.TESTP", line 4
ORA-06512: at line 1


## Grant user hr exempt redaction policy to execute the procedure.

SQL> grant exempt redaction policy to hr;

Grant succeeded.

SQL> connect hr/sys;
Connected.

SQL>
SQL> exec  testp;

PL/SQL procedure successfully completed.


Changes

Adding a redaction policy to an object

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms