MySQL Roles are becoming more and more popular. Therefor, we receive more and more questions related to them.
First I encourage you to read this previous 2 posts:
In this post, I will share you some queries I find useful when using MySQL Roles.
The first query allows you to list the Roles created on your MySQL Server and if they are assigned to users, and how many:
SELECT any_value(User) 'Role Name', IF(any_value(from_user) is NULL,'No', 'Yes') Active, count(to_user) 'Assigned #times' 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);
And this is the output:
+------------------+--------+-----------------+
| Role Name | Active | Assigned #times |
+------------------+--------+-----------------+
| dbt3_reader | Yes | 2 |
| dbt3_update | Yes | 1 |
| dbt3_writer | Yes | 1 |
| dbt3_full_reader | No | 0 |
+------------------+--------+-----------------+
The following query list all active Roles with the list of users they are assigned to:
SELECT from_user Roles, GROUP_CONCAT(to_user SEPARATOR ', ') Users FROM mysql.role_edges GROUP BY from_user;
That will returns something similar to this:
+-------------+------------------------+
| Roles | Users |
+-------------+------------------------+
| dbt3_reader | dbt3_user1, dbt3_user2 |
| dbt3_update | dbt3_user1 |
| dbt3_writer | dbt3_user3 |
+-------------+------------------------+
And finally, this query list all users having at least a Role assigned to them. Each users have the list of Roles they have assigned:
SELECT to_user Users, GROUP_CONCAT(from_user SEPARATOR ', ') Roles FROM mysql.role_edges GROUP BY to_user;
The above query will return something like:
+------------+--------------------------+
| User | Roles |
+------------+--------------------------+
| dbt3_user1 | dbt3_reader, dbt3_update |
| dbt3_user2 | dbt3_reader |
| dbt3_user3 | dbt3_writer |
+------------+--------------------------+
I hope you will find those queries useful and that you enjoy the MySQL Roles in MySQL 8.0