Grant indirect privileges

User privileges are divided into direct and indirect privileges. Direct privileges are directly granted to a user. Indirect privileges are granted to the roles of a user. Most operations can be performed with direct or indirect privileges.

Obtain the privileges granted to roles

If you grant a role to a user or another role of the user, the user indirectly obtains the privileges of the granted role.

Prerequisites

To grant a role to a user or role, you must have the granted role and the ADMIN OPTION privilege. For more information about how to view your privileges, see View user privileges.

Background information

Assume that a user named test1 has been granted the employee role. You can execute the following statement to view the privileges granted to the employee role:

obclient [oceanbase]> SHOW GRANTS FOR employee;

The return result is as follows:

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

Assume that you have the developer role. You can execute the following statement to view the privileges of the developer role:

obclient [oceanbase]> SHOW GRANTS FOR developer;

The return result is as follows:

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

Examples

If you want to grant all operation privileges of the developer role to the test1 user, perform the following steps:

• Grant the required role to the user

  1. Grant the developer role to the test1 user.

     obclient [oceanbase]> GRANT developer TO test1;
     

     If you want to use the test1 user to grant the role to another user, add the WITH ADMIN OPTION clause.

     obclient [oceanbase]> GRANT developer TO test1 WITH ADMIN OPTION;
     

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

     obclient [oceanbase]> SHOW GRANTS FOR test1 USING developer;
     

     The return result is as follows:

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

• Grant the required role to another role

  1. Grant the developer role to the employee role.

     obclient [oceanbase]> GRANT developer TO employee;
     

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

     obclient [oceanbase]> SHOW GRANTS FOR test1 USING employee;
     

     The return 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
     

     The result indicates that the test1 user obtained the ALTER SYSTEM privilege from the employee role. Then, execute the following statement to view the roles or privileges granted to the employee role:

     obclient [(none)]> SHOW GRANTS FOR employee;
     

     The result is as follows and it indicates that the ALTER SYSTEM privilege is granted from 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-related operations, see Manage roles.