The Geek Diary

How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS

by

List the active and inactive roles available on the system

Active Role: a roll that has at least 1 user account assigned to the role:

mysql> SELECT DISTINCT u.User 'Role Name', if(e.from_user is NULL,0, 1) Active
  FROM mysql.user u
    LEFT JOIN role_edges e ON e.from_user=u.user
  WHERE u.account_locked='Y' AND u.password_expired='Y' AND u.authentication_string='';
+-----------+--------+
| Role Name | Active |
+-----------+--------+
| myrole    |    1   |
| myrole2   |    0   |
+-----------+--------+
2 rows in set (0.00 sec)

Roles assigned to a user

You can use the mysql.role_edges table to see which roles are assigned to which users:

mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------+-----------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST   | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+---------+-------------------+
| %         | myrole    | localhost | myuser  | N                 |
+-----------+-----------+-----------+---------+-------------------+
1 row in set (0.00 sec)

Global Privileges for a user/role

To find out what specific GLOBAL privileges a user/role has, you can access the INFORMATION_SCHEMA.USER_PRIVILEGES table:

mysql> select * from information_schema.user_privileges where GRANTEE='\'mysql.infoschema\'@\'localhost\''; 
+--------------------------------+---------------+----------------+--------------+ 
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | 
+--------------------------------+---------------+----------------+--------------+ 
| 'mysql.infoschema'@'localhost' | def           | SELECT         | NO           | 
+--------------------------------+---------------+----------------+--------------+ 
1 row in set (0.00 sec)

Privileges for a user/role

For the actual permissions associate with a role/user you will want to look at the underlying system tables associated with the CREATE USER, GRANT and REVOKE commands.You must understand that the CREATE USER, GRANT and REVOKE commands are the recommended method of interacting with these system tables for privileges and access. MySQL DOES NOT recommend direct modification of the underlying system tables. If you choose to modify the underlying system tables anyway – IT IS DONE AT YOUR OWN RISK!

You May Also Like