Skip to content

How to Resolve ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

  • by

ORA-01981

Tried to revoke object privileges from an user, but it failed with ORA-01981.

SQL> revoke all on HR.EMPLOYEES from OE
revoke all on HR.EMPLOYEES from OE
*
ERROR at line 1:
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

ORA-01981 means that one of the object privilege you want to revoke is bound by foreign constraints, use CASCADE CONSTRAINTS to waive it.

The special object privilege is REFERENCES.

Let's see what column we reference in this case.

SQL> select a.constraint_name, b.column_name from dba_constraints a, dba_cons_columns b where a.owner = 'OE' and a.r_owner = 'HR' and b.table_name = 'EMPLOYEES' and a.r_constraint_name = b.constraint_name;

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
ORDERS_SALES_REP_FK            EMPLOYEE_ID
CUSTOMERS_ACCOUNT_MANAGER_FK   EMPLOYEE_ID

As we can see, there're 2 foreign keys reference to the same column.

Solution

To solve ORA-01981, we add CASCADE CONSTRAINTS to REVOKE FROM statement.

SQL> revoke all on HR.EMPLOYEES from OE cascade constraints;

Revoke succeeded.

As a result, not only REFERENCES privilege is revoked from the user, but reference constraints are also removed.

Without REFERENCES object privilege, you will get ORA-01031: Insufficient Privileges whenever you want to add a constraint which references a foreign object.

Leave a Reply

Your email address will not be published. Required fields are marked *