|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
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
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'
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:
from_user as Roles,
GROUP_CONCAT(to_user SEPARATOR ', ') as Users
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.
ROLE syntax, a role is granted to an authorisation ID by adding a directed edge to the
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
GROUP BY to_user;