Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Why this command does not work?

user-opn20Apr 10 2023

I am using oracle xe 21, sql developer, connection user system.

GRANT SELECT ON C##USER1.TABLE1 TO C##USER1; 
REVOKE SELECT ON C##USER1.TABLE1 FROM C##USER1;

While grant works, revoke does not work:

ORA-01927: cannot REVOKE privileges you did not grant 
01927. 00000 -  "cannot REVOKE privileges you did not grant" 
*Cause:    You can only revoke privileges you granted.
*Action:   Don't revoke these privileges.

Please bear with me:
I used this command in system sql workbench and both worked:
GRANT SELECT ANY TABLE TO C##USER1;
REVOKE SELECT ANY TABLE FROM C##USER1;

Then I tried these commands:
GRANT SELECT ON C##USER1.TABLE1 TO C##USER1;
It actually granted access to C##USER1 to its TABLE1.
And now when I try to revoke this particular privilege, it throws an error:
REVOKE SELECT ON C##USER1.TABLE1 FROM C##USER1;

After that I tried using this to revoke the privilege that seems to be "bug":
REVOKE SELECT ANY TABLE FROM C##USER1;
Then I tried this command in the C##USER1 sql workbench:
select*from C##USER1.TABLE1;
And it actually shown the table.
This means that the command GRANT SELECT ON C##USER1.TABLE1 TO C##USER1;broke the SQL privilege system?
It seems there is no way you can revoke the GRANT SELECT ON C##USER1.TABLE1 TO C##USER1;

This post has been answered by Solomon Yakobson on Apr 10 2023
Jump to Answer
Comments
Post Details
Added on Apr 10 2023
4 comments
431 views