Privilege delegation

Privilege delegation resolves the issue of centralized authorization. By specifying WITH ADMIN OPTION or WITH GRANT OPTION during authorization, users can be granted the permission to delegate corresponding privileges to other users. When revoking object privileges, the corresponding privileges that the user has delegated to other users will also be revoked. For example, if user A grants privilege to user B and user B grants privilege to user C, when user A revokes privilege from user B, privilege of user C will also be revoked. Revoking system privileges does not revoke delegated privileges.

Examples

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

   obclient > CREATE USER A IDENTIFIED BY **1***;
   obclient > CREATE USER B IDENTIFIED BY **1***;
   obclient > CREATE USER C IDENTIFIED BY **1***;
   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 on as user A, and grant user B the privilege to query the test table using the WITH GRANT OPTION cause, 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 on as user B and grant user C the privilege to query the test table.

   obclient > GRANT SELECT ON sys.test TO C;
   

7. Switch to user C and query the test table.

   obclient > SELECT * FROM sys.test;
   

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

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