Privilege delegation

Privilege delegation solves the problem due to the centralization of privileges to a limited number of authorizers. When you grant a privilege to a user by using a WITH ADMIN OPTION or WITH GRANT OPTION clause in the GRANT statement, you allow the user to grant the same privilege to other users. The revocation of object privileges extends to dependent users. For example, assuming that user A has granted privileges to user B and user B has granted privileges to user C, when you revoke an object privilege from user A, the object privilege is also revoked from user C. The revocation of system privileges does not extend to dependent users.

Examples

1. Log in to an Oracle tenant of a cluster as the SYS user. Create users A, B, and C, and grant all the users the CREATE SESSION system privilege and user A the GRANT ANY PRIVILEGE privilege.

   obclient > CREATE USER A IDENTIFIED BY ******;
   obclient > CREATE USER B IDENTIFIED BY ******;
   obclient > CREATE USER C IDENTIFIED BY ******;
   obclient > GRANT CREATE SESSION TO A;
   obclient > GRANT CREATE SESSION TO B;
   obclient > GRANT CREATE SESSION TO C;
   obclient > GRANT GRANT ANY PRIVILEGE TO A;
   

2. Create a table named test under the SYS user.

   obclient > CREATE TABLE test (id number(10),name varchar2(10));
   Query OK, 0 rows affected (0.050 sec)
   

3. Grant user A the privilege to query the test table of the SYS user.

   obclient > GRANT SELECT ON sys.test TO A;
   

4. Log in as user A and grant user B the privilege to query the test table using the WITH GRANT OPTION clause, so that user B can grant the same privilege to other users.

   obclient > GRANT SELECT ON sys.test TO B with grant option;
   

5. Switch to user B and query the test table.

   obclient > SELECT * FROM sys.test
   

6. Log in as user B and grant user C the privilege to query the test table.

   obclient > GRANT SELECT ON sys.test TO C ;
   

   If you grant the privilege to user B without using the WITH GRANT OPTION clause, the following error message is returned when user B attempts to grant the same privilege to user C:

   obclient > GRANT SELECT on sys.test TO C ;
   ORA-01031: insufficient privileges
   

7. Switch to user C and verify whether you can query the test table. If yes, privilege delegation is successful.

   obclient > SELECT * FROM sys.test;