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.
Execute 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 return 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
Execute 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 in
If you have specified to activate all roles granted to a user when the user logs in by using the global variable activate_all_roles_on_login, or have specified the roles that become active by default when the user logs in 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 return 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 in 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, which is allowed to grant theemployeerole to other users or roles.The second row indicates that all privileges of the
employeerole are granted to therole1role, which is allowed to grant theemployeerole to other users or roles.The third row indicates that all privileges of the
developerrole are granted to thetest1user, which cannot grant thedeveloperrole to other users or roles.
For more information about the fields in the mysql.role_edges view, see mysql.role_edges.
View the table-level privileges of activated roles
Execute the following statement to view the table-level privileges of activated roles granted to the user in the current session, and the table-level privileges of other roles granted to these roles.
obclient [information_schema]> SELECT * FROM information_schema.role_table_grants;
The query result is as follows:
+---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
| GRANTOR | GRANTOR_HOST | GRANTEE | GRANTEE_HOST | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
| root | % | developer | % | def | test | tbl2 | Drop,Update,Select | NO |
+---------+--------------+-----------+--------------+---------------+--------------+------------+--------------------+--------------+
1 row in set
The query result shows that the root user has granted the developer role the DROP, UPDATE, and SELECT privileges on the test.tbl2 table.
For more information about the fields in the role_table_grants view, see information_schema.role_table_grants.
View the column privileges of activated roles
Execute the following statement to view the column privileges of activated roles granted to the user in the current session, and the column privileges of other roles granted to these roles.
obclient [information_schema]> SELECT * FROM information_schema.role_column_grants;
The query result is as follows:
+---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTOR | GRANTOR_HOST | GRANTEE | GRANTEE_HOST | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
| NULL | NULL | employee | % | def | test | tbl1 | c1 | Select,Insert | NO |
| NULL | NULL | employee | % | def | test | tbl1 | c2 | Insert | NO |
+---------+--------------+----------+--------------+---------------+--------------+------------+-------------+----------------+--------------+
2 rows in set
The query result is described as follows:
The first row indicates that the
employeerole has theSELECTandINSERTprivileges on thec1column of thetest.tbl1table.The second row indicates that the
employeerole has theINSERTprivilege on thec2column of thetest.tbl1table.
For more information about the fields in the role_column_grants view, see information_schema.role_column_grants.
View the routine privileges of activated roles
Execute the following statement to view the routine privileges of activated roles granted to the user in the current session, and the routine privileges of other roles granted to these roles.
obclient [information_schema]> SELECT * FROM information_schema.role_routine_grants;
The query result is as follows:
+---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+
| GRANTOR | GRANTOR_HOST | GRANTEE | GRANTEE_HOST | SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+
| root | % | developer | % | def | test | add_numbers | def | test | add_numbers | Execute,Alter Routine | NO |
+---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+
1 row in set
The query result shows that the root user has granted the developer role the EXECUTE andALTER ROUTINE privileges on the test.add_numbers routine.
For more information about the fields in the role_routine_grants view, see information_schema.role_routine_grants.