Skip to main content

View roles

You can view the roles granted to a user, the roles contained in a role, and the privileges contained in a role.

View the roles and privileges granted to a user​

tip

A user can always use the privileges directly granted to it, but cannot use the privileges indirectly obtained through roles. A user can only use the privileges of roles activated in the current session.

To view the roles and privileges granted to user test1, execute the following statement:

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

To further display the privileges contained in the roles granted to a user, you can add a USING clause to specify the privileges contained in the role.

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​

To view the roles and privileges granted to role role1, execute the following statement:

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 default roles activated when a user logs in​

If the Global variable activate_all_roles_on_login is enabled to automatically activate all roles granted to a user upon login, or if the SET DEFAULT ROLE or ALTER USER statement is used to set the default roles activated upon login, you can query the mysql.default_roles view to confirm whether the default roles activated upon login are set correctly.

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 roles activated in the current session​

To view the roles activated in the current session for the current user, execute the following statement:

SELECT current_role();

The query result is as follows:

+--------------------------------+
| current_role()                 |
+--------------------------------+
| `developer`@`%`,`employee`@`%` |
+--------------------------------+
1 row in set

If the query result is NONE, it indicates that no roles are activated for the current user. Only the privileges of activated roles can be used by the user.

View the authorization relationships between roles and users​

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

In the query result above:

  • The first line indicates that the employee role grants all its privileges to the test2 user and allows the test2 user to grant the role to other users or roles.

  • The second line indicates that the employee role grants all its privileges to the role1 role and allows the role1 role to grant the role to other users or roles.

  • The third line indicates that the developer role grants all its privileges to the test1 user, and the test1 user cannot grant the role 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 the activated roles​

To view the table-level privileges of the activated roles granted to the current user in the current session and the table-level privileges of other roles granted to these roles, execute the following statement:

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 indicates that the root user grants the Drop, Update, and Select privileges on the test.tbl2 table to the developer role.

For more information about the fields in the role_table_grants view, see information_schema.role_table_grants.

View the column privileges of the activated roles​

To view the column privileges of the activated roles granted to the current user in the current session and the column privileges of other roles granted to these roles, execute the following statement:

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

In the query result above:

  • The first line indicates that the employee role has the Select and Insert privileges on the c1 column of the test.tbl1 table.
  • The second line indicates that the employee role has the Insert privilege on the c2 column of the test.tbl1 table.

For more information about the fields in the role_column_grants view, see information_schema.role_column_grants.

View the routine privileges of the activated roles​

To view the routine privileges of the activated roles granted to the current user in the current session and the routine privileges of other roles granted to these roles, execute the following statement:

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 indicates that the root user grants the Execute and Alter Routine privileges on the test.add_numbers routine to the developer role.

For more information about the fields in the role_routine_grants view, see information_schema.role_routine_grants.

References​

Contents

Edit this page