Privilege delegation

Privilege delegation addresses the challenge of centralized authorization. By specifying with admin option or with grant option during the granting process, grantors allow users to delegate those same privileges to other users. Importantly, if a user's object or system privileges are revoked, any privileges they've delegated remain unchanged, preventing a cascading revocation.

Examples

• Scenario: Privilege delegation fails

  1. Log on to a MySQL tenant of a cluster as the root user.

  2. Create user A, and grant privileges to this user without using the with grant option clause.

     obclient> GRANT ALL PRIVILEGES ON *.* TO A IDENTIFIED BY '******';
     Query OK, 0 rows affected (0.034 sec)
     

  3. Log on to the cluster as user A, create user B, and grant user B the same privileges as user A. However, an error is returned.

     obclient> GRANT ALL PRIVILEGES ON *.* TO 'B' IDENTIFIED BY '******';
     ERROR 1227 (42501): Access denied; you need (at least one of) the GRANT privilege(s) for this operation
     

     Notice

     If you grant a privilege to a user without using the WITH GRANT OPTION clause, the user cannot grant the same privilege to other users.

• Scenario: Successful privilege delegation

  1. Log on to a MySQL tenant of a cluster as the root user.

  2. Grant privileges to user A using the with grant option clause.

     obclient> GRANT ALL PRIVILEGES ON *.* TO A with grant option;
     

  3. Log on to the cluster as user A, create user B, and grant user B the same privileges as user A.

     A success message is returned.

     obclient> GRANT ALL PRIVILEGES ON *.* TO 'B' IDENTIFIED BY '******';
     Query OK, 0 rows affected (0.058 sec)