MySQL 8.0: Listing Roles

As you may already know, MySQL 8.0 is coming with SQL Roles . I’ve already quoted them before in some posts (here and here). Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). Of course he also made some remarks on things he would have done differently. The main point is about making the difference between users and roles.

In most OpenSource RDBMS, a role is in fact an alias for a user but without a login.

As I always like to say, if there is not solution, there is no problem, isn’t it ? 😉

So let’s have a look at how we could differentiate our roles and list them.

ROLES & USERS Creation

I will first create two different roles and one user. As the syntax is very easy, I think this doesn’t require more explanation.

Firs the roles, one to read the data and one able to write data:

mysql> CREATE ROLE dbt3_reader;
mysql> GRANT SELECT ON dbt3.* to dbt3_reader;

mysql> CREATE ROLE dbt3_writer;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON dbt3.* TO dbt3_writer;

Now the user and I will assign it the dbt3_reader role:

mysql> CREATE USER dbt3_user1 IDENTIFIED BY 'password';
mysql> GRANT dbt3_reader TO dbt3_user1;

Listing the ROLES

So now the “real problem”, how could we list only the roles and not the users ?

It’s possible to achieve this with the following query:

mysql> SELECT DISTINCT User 'Role Name', if(from_user is NULL,0, 1) Active 
       FROM mysql.user LEFT JOIN role_edges ON from_user=user 
       WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';
+-------------+--------+
| Role Name   | Active |
+-------------+--------+
| dbt3_reader |      1 |
| dbt3_writer |      0 |
+-------------+--------+
2 rows in set (0.00 sec)

Indeed ROLES are locked accounts, without passwords and expired. It’s also possible to list a user in it, but this means that you removed the password of a user that you have locked and that the password expired…. a bit too much isn’t it ? 😉

On the query above, Active means that there is at least one user having that role assigned.

This query might be a good candidate for a new SYS view.

What is a ROLE ?

But the real question, is “what is a role ?” Because we can also grant a user to another like this:

mysql> CREATE USER dbt3_user2 IDENTIFIED BY 'password2';
mysql> GRANT dbt3_user1 TO dbt3_user2;

It can become very complicated… the manual answers this question very well:

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.
[…]
As has been hinted at earlier for SHOW GRANTS, which displays grants for user accounts or roles, accounts and roles can be used interchangeably. You can treat a user account like a role and grant that account to another user or a role. The effect is to grant the account’s privileges and roles to the other user or role.

And for better usability, I encourage you to prefix all your roles with ‘r_‘, so db3_reader becomes r_db3_reader.

Then listing the roles becomes much easier.