Indirect authorization

User privileges include both directly granted privileges and privileges obtained indirectly through roles. Most operations require privileges, whether directly granted or indirectly obtained through roles.

Obtain privileges indirectly by being granted roles

When a role is granted to a user or another role of the user, the user indirectly gains the privileges included in that role.

tip

Users can always use the privileges directly granted to them, but they may not necessarily be able to use the privileges obtained indirectly through roles. Users can only use the privileges of roles activated in the current session. For more information about activating roles, see Activate a role.

Prerequisites

When granting a role to another user or role, the current user must have the role and the ADMIN OPTION privilege to successfully grant the role.

Background information

Assume that the current user is test1, and they have been granted the employee role. To view the privileges included in the employee role, execute the following statement:

SHOW GRANTS FOR employee;

The result is as follows:

+--------------------------------------------------+
| Grants for employee@%                            |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'employee'                 |
| GRANT UPDATE, SELECT ON `dbtest`.* TO 'employee' |
+--------------------------------------------------+
2 rows in set

The current user has the developer role. To view the privileges included in the developer role, execute the following statement:

SHOW GRANTS FOR developer;

The result is as follows:

+------------------------------------------+
| Grants for developer@%                   |
+------------------------------------------+
| GRANT ALTER SYSTEM ON *.* TO 'developer' |
+------------------------------------------+
1 row in set

Example

You need to grant all operation privileges of the developer role to the user test1. You can achieve this through indirect authorization as follows:

• Grant the role to the user

  1. The current user grants the developer role to the user test1.

     GRANT developer TO test1;

     If you want the user to be able to grant the role to others, you can add the WITH ADMIN OPTION clause.

     GRANT developer TO test1 WITH ADMIN OPTION;

  2. Use the SHOW GRANTS ... USING statement to view the privileges obtained by the user through the developer role.

     SHOW GRANTS FOR test1 USING developer;

     The result is as follows:

     +-----------------------------------------------------+
     | Grants for test1@%                                  |
     +-----------------------------------------------------+
     | GRANT ALTER SYSTEM ON *.* TO 'test1'                |
     | GRANT `developer`@`%`,`employee`@`%` TO `test1`@`%` |
     +-----------------------------------------------------+
     2 rows in set

• Grant the role to the user's role

  1. Grant the developer role to the employee role.

     GRANT developer TO employee;

  2. Use the SHOW GRANTS ... USING statement to view the privileges obtained by the user test1 through the employee role.

     SHOW GRANTS FOR test1 USING employee;

     The result is as follows:

     +-----------------------------------------------+
     | Grants for test1@%                            |
     +-----------------------------------------------+
     | GRANT ALTER SYSTEM ON *.* TO 'test1'          |
     | GRANT UPDATE, SELECT ON `dbtest`.* TO 'test1' |
     | GRANT `employee`@`%` TO `test1`@`%`           |
     +-----------------------------------------------+
     3 rows in set

     According to the query result, the user test1 obtained the ALTER SYSTEM privilege through the employee role. You can then view the roles or privileges granted to the employee role.

     SHOW GRANTS FOR employee;

     The result is as follows, indicating that the ALTER SYSTEM privilege was obtained through the developer role.

     +--------------------------------------------------+
     | Grants for employee@%                            |
     +--------------------------------------------------+
     | GRANT USAGE ON *.* TO 'employee'                 |
     | GRANT UPDATE, SELECT ON `dbtest`.* TO 'employee' |
     | GRANT `developer`@`%` TO `employee`@`%`          |
     +--------------------------------------------------+
     3 rows in set

References

For more information about role management, see Manage roles.