Revoking an Object-Level Privilege

Remove the ability of a user to use a specific object-level privilege or 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
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 who that user in turn granted the privilege to also has their privilege revoked. For example, you granted UserA the SELECT privilege with the WITH GRANT OPTION clause. UserA then grants the SELECT privilege to UserB. If you revoke the SELECT privilege from UserA, it is also revoked for UserB.

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 re-grant 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 re-grant the object-level privilege without administrative rights, execute:
```
GRANT object_privilege_name 
  ON table_name
  TO userID [,...]
```

Example:

This example assumes that Joe was granted the right to both perform deletes on the table Sales, and grant the DELETE privilege on the table to other users.

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

REVOKE DELETE ON Sales FROM Joe

This statement re-grants the privilege only, with no administrative rights:

GRANT DELETE ON Sales TO Joe