Granting the Right to Administer an Object-Level Privilege

Grant the privilege to allow a user to pass a specific object privilege on to other users.

Prerequisites

At least one of these conditions:

You created the table.
You have been granted privileges on the table with the ADMIN OPTION.
You have been granted LOAD and TRUNCATE object privileges.
You have been granted the MANAGE ANY OBJECT PRIVILEGE system privilege. If the LOAD or TRUNCATE object privilege is granted using the WITH GRANT OPTION clause, the grantee can then grant the object privilege to other users, but is limited to those tables specified in the original GRANT statement. Under this scenario, the grantee does not need the MANAGE ANY OBJECT PRIVILEGE system privilege.

Task

To grant the right to grant a privilege to another user, enter:

GRANT Object_privilege _name 
  ON table_name
  TO userID [,...]
  WITH GRANT OPTION

Example:

This statement grants the privilege to Mary to perform deletes on the table Sales:

GRANT DELETE ON Sales TO Mary

This statement grants the right to Joe to both perform deletes on the table Sales and grant the DELETE privilege to other users:

GRANT DELETE ON Sales TO Joe
WITH GRANT OPTION