Revoking an Object-Level Privilege

Remove the ability of a user to use a specific object-level privilege, or to grant the privilege to other users.

Prerequisites

Grantor must have at least one of these conditions:

Be the original grantor of the privilege that is being revoked or,
Have the MANAGE ANY OBJECT PRIVILEGE system privilege.

Task

If you revoke a privilege from a user who has been granted a privilege with the WITH GRANT OPTION clause, then everyone to whom that user granted the privilege also has his or her privilege revoked. For example, you granted User1 the SELECT privilege with the WITH GRANT OPTION clause. User1 then grants the SELECT privilege to User2. If you revoke the SELECT privilege from User1, it is also revoked from User2.

The REVOKE command applies to the object-level privilege itself, not to any administrative right granted on the privilege. Therefore, you cannot revoke administrative rights only and leave the object-level privilege intact. To correctly remove a user's administrative rights only to an object-level privilege, you must first revoke the privilege and then regrant the privilege without the WITH GRANT OPTION clause.

To revoke an object-level privilege, including any administrative privilege, execute:
```
REVOKE object_privilege_name 
  ON table_name
  FROM userID [,...]
```
(Optional) To then regrant the object-level privilege without administrative rights, execute:
```
GRANT object_privilege_name 
  ON table_name
  TO userID [,...]
```

Example:

This example assumes that Joe has been granted the right to both perform deletions on the Sales table, and to grant the DELETE object-level privilege on the table to other users.

This statement revokes all DELETE object-level privileges on the table Sales, which by definition includes any administrative rights:

REVOKE DELETE ON Sales FROM Joe

This statement regrants the object-level privilege only, with no administrative rights:

GRANT DELETE ON Sales TO Joe