List roles and users

from the Artful MySQL Tips List

A MySQL account is a row in the mysql.user table, identified by a unique authorisation ID. An authorisation ID is a tuple consisting of unique `user` and `host` values in one row of the mysql.user table. Since MySQL 8, a role is a set of privileges defined with an authorisation ID in a MySQL user account that is locked, expired, and without a password. Role syntax is set out here. A role may be active, or not. Here is a query to list roles:

select u.user as 'role name', if(e.from_user is null,0,1) as 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='';

List roles grouped by user:

SELECT any_value(User) as 'Role Name', 
       IF(any_value(from_user) is NULL,'No', 'Yes') Active,
       Count(to_user) as 'Assignments'        
FROM mysql.user 
LEFT JOIN mysql.role_edges ON from_user=user
WHERE account_locked='Y' AND password_expired='Y' 
AND authentication_string='' GROUP BY(user);

List active roles with users:

SELECT 
  from_user as Roles, 
  GROUP_CONCAT(to_user SEPARATOR ', ') as Users
FROM mysql.role_edges 
GROUP BY from_user;

MySQL stores role assignments as a directed graph in the mysql.role_edges table; "edges" in the name indicates the table implements an edge list, so each row in that table defines a from_user/from_host tuple, ie an authorisaton ID [i]from[/i] which privileges are being assigned, and a to_user/to_host tuple, ieanother authorisation ID [i]to[/i] which privileges are being assigned. Behind ROLE syntax, a role is granted to an authorisation ID by adding a directed edge to the role_edges table:

from_host          : `host` of mysql.user row defining the role
from_user          : `user` of mysql.user row defining the role
to_host            : `host` of mysql.user row being to whom the role is assigned
to_user            : `user` of mysql.user row being to whom the role is assigned
with_admin_option  : 'Y' or 'N';

Thus this query shows the user accounts to which the role 'DailyAdmin' has been assigned ...

select u.user, u.host
from mysql.user      u
join role_edges e on e.from_user=u.user and e.from_host=u.host
where u.user = 'DailyAdmin';

List all users assigned to one or more roles:

SELECT to_user Users, GROUP_CONCAT(from_user SEPARATOR ', ') Roles
FROM mysql.role_edges 
GROUP BY to_user;

Last updated 11 Jul 2024

Return to the Artful MySQL Tips page