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;