Fix ORA-01927: cannot REVOKE privileges you did not grant

advertisements

_____________________________________________________________________________________________________________________

Error Description: 

Revoke privilege operation/SQL command failed with following error message ORA-01927: cannot REVOKE privileges you did not grant 

Solution Description: 

As the error message explains the privilege is not granted by the user which is trying to revoke. Only the grantor, sys, system or any DBA privileged user can revoke the privilege granted by a user/grantor. You can verify the privilges assigned to a user by fetching the details from these 3 tables. 

user_sys_privs 

user_tab_privs 

user_role_privs 

See one example here

advertisements

 

SQL> connect test/test

Connected.

SQL> select * from tab;

no rows selected

SQL> create table tab_all as select * from all_objects where 1=2;

Table created.

SQL> grant insert on tab_all to sthomas;

Grant succeeded.

SQL> conn test1/test1

Connected.

SQL> revoke insert on test.tab_all from sthomas;

revoke insert on test.tab_all from sthomas

*

ERROR at line 1:

ORA-01927: cannot REVOKE privileges you did not grant

A DBA privileged user can revoke any privileges.

SQL> conn / as sysdba

Connected.

SQL> grant dba to test1;

Grant succeeded.

SQL> conn test1/test1

Connected.

SQL> revoke insert on test.tab_all from sthomas;

Revoke succeeded.

  1* select GRANTEE, TABLE_NAME, GRANTOR, PRIVILEGE from user_tab_privs

SQL> /

GRANTEE              TABLE_NAME           GRANTOR              PRIVILEGE

-------------------- -------------------- -------------------- --------STHOMAS              TAB_ALL              TEST                 INSERT

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE            ADM

------------------------------ -------------------- ---

TEST                           UNLIMITED TABLESPACE NO

SQL>  select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- -

TEST                           CONNECT                        NO  YES NO

TEST                           RESOURCE                       NO  YES NO

_____________________________________________________________________________________________________________________

Website Stats