PROBLEM:

While creating a table using CTAS, got below error:

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

CAUSE & SOLUTION

The error indicates, that the objects which we are trying to access is redacted column, i.e DBMS_REDACTIONS function has been applied to it.

Fir, t find the list of redacted table/columns:

10:21:13 SYS@BSSTDM SQL> select OBJECT_OWNER,OBJECT_NAME,COLUMN_NAME from redaction_columns;

OBJECT_OWNER                   OBJECT_NAME                    COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
DBACLASS                        CREDIT_CARD_INFO               ENROLL_DATE
DBACLASS                        CREDIT_CARD_INFO               CARD_NO

4 rows selected.

We can check the status of redaction policies also:

SQL> select OBJECT_OWNER,OBJECT_NAME,POLICY_NAME,ENABLE from redaction_policies;

OBJECT_OWNER                   OBJECT_NAME                    POLICY_NAME                    ENABLE
------------------------------ ------------------------------ ------------------------------ -------
DBACLASS                        CREDIT_CARD_INFO               TEST_P                         YES

2 rows selected.

We can see two columns (ENROLL_DATE, CARD_NO) of the table has been redacted.So to be able to acess these objects user should have EXEMPT REDACTION POLICY privilege.

Lets grant that privilege and try the CTAS operation.

SQL>grant EXEMPT REDACTION POLICY  to RAJ;

grant succeeded

SQL>connect raj/raj
connected

SQL> create table test as select * from DBACLASS.CREDIT_CARD_INFO;

Table created.

NOTE – DBA role already includes this EXPEMPT REDACTION POLICY privilege.