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 was revoked from the user, but reference constraints were also dropped.
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;
no rows selected
Without REFERENCES object privilege, you will get ORA-01031: Insufficient Privileges whenever you want to add a constraint which references a foreign object.