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

• Failed privilege delegation

  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.

     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.

• 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 '**1***';
     Query OK, 0 rows affected (0.058 sec)