|A MySQL account is a row in the |
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
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 2019