This topic describes how to view the activated roles of a user, roles granted to a role, and privileges of a role.
View the roles and privileges granted to a user
Notice
A user can always use privileges that are directly granted, but can only use indirect privileges of an active role in a session.
Use the following statement to view the roles and privileges granted to a user named test1:
obclient [oceanbase]> SHOW GRANTS FOR test1;
The query result is as follows:
+-------------------------------------------------+
| Grants for test1@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1' |
| GRANT `employee`@`%`,`role1`@`%` TO `test1`@`%` |
+-------------------------------------------------+
2 rows in set
If you want to view the privileges of a role granted to the test1 user, use a USING clause.
obclient [oceanbase]> SHOW GRANTS FOR test1 USING role1;
The result is as follows:
+-------------------------------------------------+
| Grants for test1@% |
+-------------------------------------------------+
| GRANT ALTER SYSTEM ON *.* TO 'test1' |
| GRANT UPDATE, SELECT ON `dbtest`.* TO 'test1' |
| GRANT `employee`@`%`,`role1`@`%` TO `test1`@`%` |
+-------------------------------------------------+
3 rows in set
View the roles and privileges granted to a role
Use the following statement to view the roles and privileges granted to a role named role1:
obclient [oceanbase]> SHOW GRANTS FOR role1;
The query result is as follows:
+-------------------------------------------------------+
| Grants for role1@% |
+-------------------------------------------------------+
| GRANT ALTER SYSTEM ON *.* TO 'role1' |
| GRANT SELECT ON `dbtest`.* TO 'role1' |
| GRANT SELECT ON `test1`.`tbl1` TO 'role1' |
| GRANT `employee`@`%` TO `role1`@`%` WITH ADMIN OPTION |
+-------------------------------------------------------+
4 rows in set
View the roles that become active by default when a user logs on
If you have specified to activate all roles granted to a user when the user logs on by using the global variable activate_all_roles_on_login, or have specified the roles that become active by default when the user logs on by using the SET DEFAULT ROLE or ALTER USER statement, you can query the mysql.default_roles view to check whether the settings are as expected.
obclient [oceanbase]> SELECT * FROM mysql.default_roles;
The result is as follows:
+------+-------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-------+-------------------+-------------------+
| % | test1 | % | role1 |
| % | test1 | % | employee |
+------+-------+-------------------+-------------------+
2 rows in set
For more information about the fields in the mysql.default_roles view, see mysql.default_roles.
View the active roles in the current session
After you log on as a user, you can view your active roles in the current session by using the following statement:
obclient [oceanbase]> SELECT current_role();
The query result is as follows:
+--------------------------------+
| current_role() |
+--------------------------------+
| `developer`@`%`,`employee`@`%` |
+--------------------------------+
1 row in set
If the query result is NONE, none of your roles are active. You can use only the privileges of an active role.
View authorization relationships between roles and users
obclient [oceanbase]> SELECT * FROM mysql.role_edges;
The query result is as follows:
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| % | employee | % | test2 | Y |
| % | employee | % | role1 | Y |
| % | developer | % | test1 | N |
+-----------+-----------+---------+---------+-------------------+
3 rows in set
The query result is described as follows:
The first row indicates that all privileges of the
employeerole are granted to thetest2user, who is allowed to grant theemployeerole to other users or roles.The second row indicates that all privileges of the
employeerole are granted to therole1role, who is allowed to grant theemployeerole to other users or roles.The third row indicates that all privileges of the
developerrole are granted to thetest1user, who cannot grant thedeveloperrole to other users or roles.
For more information about the fields in the mysql.role_edges view, see mysql.role_edges.