Some queries related to MySQL Roles

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:

• MySQL 8.0 Roles and GraphML
• MySQL 8.0: Listing Roles

In this post, I will share you some queries I find useful when using MySQL Roles.

Listing the 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 |
+------------------+--------+-----------------+

Listing the active Roles with the users

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             |
+-------------+------------------------+

Listing all users assigned to one or multiple Roles

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