Object Privileges  «Prev  Next»

Lesson 7Revoking Privileges
ObjectiveRevoke Users Object Privileges

Revoke Users Object Privileges

Things change, and people make mistakes. There may be times when you want to take away a privilege that has been granted to a user.
The REVOKE command allows you to accomplish this goal.

REVOKE command

You can use the REVOKE command to take away any previously granted privilege.
The syntax for the REVOKE command is very similar to the syntax for the GRANT command, as shown in the MouseOver below.
  1. A required keyword
  2. privilege is the name of a specific privilege. The ALL keyword means that all privileges will be revoked on the target object from the specified user.
  3. The name of the table or view to which the privileges apply
  4. user is the name of an existing Oracle user. The keyword PUBLIC refers to a special user group that applies the privilege to all users of the database.

Revoke Command - Syntax

Conditions for REVOKE

You can only revoke a privilege that you have granted. Even if another user has granted a privilege on an object that you own, you cannot revoke it.
You can revoke privileges selectively on a column, but the privilege must have been explicitly granted to the column. You cannot grant a privilege to an entire table and then revoke the privilege for a column in the table.
you want to revoke the GRANT option from a user or role, you have to take two separate actions. First you have to REVOKE the privilege itself, and then regrant the privilege without using the GRANT option.

Example

To revoke the INSERT privilege on the COIN table that was granted in the previous
REVOKE INSERT ON COIN 
FROM SCOTT;

Notice that you can grant ALL privileges for a table to a user and then selectively revoke individual privileges. The ALL keyword is simply a way of specifying all privileges with a single word, so the individual privileges can still be taken away.
The next lesson is the module wrap-up, which includes a quiz that will indicate what you have learned and discover topics that you might want to review in a more detailed manner.