A user's privileges include both those granted directly and those indirectly obtained through roles. For most operations, having the required privilege, whether granted directly or indirectly through a role, is sufficient.
Indirectly obtain privileges by being granted a role
If you grant a role to a user or another role of the user, the user indirectly obtains the privileges of the granted role.
Notice
You can always use privileges that are directly granted to you, but you may not be able to use privileges obtained indirectly through roles. You can only use the privileges of the role that is activated in the current session. For more information about how to activate a role, see Activate a role.
Prerequisites
You have the roles to be granted 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 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 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
Grant the
developerrole to thetest1user.obclient [oceanbase]> GRANT developer TO test1;If you want to use the
test1user to grant the role to another user, add theWITH ADMIN OPTIONclause.obclient [oceanbase]> GRANT developer TO test1 WITH ADMIN OPTION;Use the
SHOW GRANTS ... USINGstatement to view the privileges obtained by thetest1user from thedeveloperrole.obclient [oceanbase]> 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 required role to another role
Grant the
developerrole to theemployeerole.obclient [oceanbase]> GRANT developer TO employee;Use the
SHOW GRANTS ... USINGstatement to view the privileges obtained by thetest1user from theemployeerole.obclient [oceanbase]> 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 setThe result indicates that the
test1user obtained theALTER SYSTEMprivilege from theemployeerole. Then, execute the following statement to view the roles or privileges granted to theemployeerole:obclient [oceanbase]> SHOW GRANTS FOR employee;The result is as follows and it indicates that the
ALTER SYSTEMprivilege is granted from thedeveloperrole.+--------------------------------------------------+ | 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 Role management overview.