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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

4 Comments

  1. Fred,
    Thanks for the tip, although you have got *our* roles wrong: I am the one who writes hacks to compensate for missing features, while you should provide real features to prevent me from writing hacks. 🙂
    This trick would work in some cases, but not always.
    Let me explain my complaint in a better way. What I need is an unequivocal way of listing roles, not only because I want to tell roles from users, but also to keep track of what was created as a role and what was created as a user.
    I am able to run “CREATE TABLE”, “CREATE VIEW”, “CREATE USER”, and I can list tables, views, and users. However, I can say “CREATE ROLE” but I can’t list roles.
    The cleanest solution would be a table containing roles. An easy compromise would be a new field in the user table saying that a given account is a role.

    • Hi Giuseppe, I do understand you 🙂 (and always liked to write hacks too). In others DBs a user is just a role that can login and the list of roles is just a view of all “users/roles” that cannot login… exactly what I’m trying here. Would a query like ‘select * from sys.roles’ be enough ?
      Now if you want that when we specify ‘create role’ we add a meta information somewhere in the DD and then an other command like SHOW ROLES uses that info… it might be possible but will require some investigation.

  2. […] ロールを実装しました。ロールは、権限の集合に名前を付けたもので、目的はユーザアクセス権限管理を単純化することです。ロールをユーザに付与したり、権限をロールに付与したり、ロールを作成/削除したり、セッションでどのロールが使用できるか決めたりできます。こちらのFrederic Descampsのブログ記事をご覧ください。 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.