Database Reference
In-Depth Information
Revoke Object Privileges
. To revoke an object privilege, you must
either have granted the privilege originally or you must have the
GRANT ANY OBJECT PRIVILEGE system privilege.
As with the GRANT command, let's go through a sequence of steps
demonstrating use of the REVOKE command. Let's revoke privileges from
the two users, PRINCE and ARIEL.
First, connect as PRINCE.
CONNECT PRINCE/CHARMING@OLTP;
Now we can revoke an object privilege that was granted by PRINCE.
Revoke the SELECT privilege on the MYHORSES table from ARIEL.
ARIEL will no longer be able to read PRINCE's MYHORSES table.
REVOKE SELECT ON MYHORSES FROM ARIEL;
Next we can connect to the SYSTEM user and revoke a system privilege
granted earlier.
CONNECT SYSTEM/password@OLTP;
We have decided that PRINCE should not be allowed to create views.
REVOKE CREATE VIEW FROM PRINCE;
What happens to ARIEL's ability to create views (granted by PRINCE)
when PRINCE loses his privilege to create views? System privileges remain
until specifically revoked from a user, even if the granting user loses the
privilege. We can verify this fact by connecting to ARIEL.
CONNECT ARIEL/MERMAID@OLTP;
Now create a view on the MUSIC.ARTIST table by running the next
command. ARIEL has the ability to SELECT from that table because the
object privilege was granted to PUBLIC. This verifies that even though
PRINCE has been denied the ability to create views, ARIEL has not.
