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!