_____________________________________________________________________________________________________________________
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
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment