Infrastructure at your Service

Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL.
The most important advantage is to define only once a role that includes a “set of permissions”, then assign it to each user, avoiding wasting time declaring them individually.

In MySQL, a role can be created like a user, but without the “identified by” clause and without login:

mysqld2-([email protected]) [(none)]> CREATE ROLE 'r_sakila_read';
Query OK, 0 rows affected (0.03 sec)
mysqld2-([email protected]) [(none)]> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| r_sakila_read    | %         |                                                                        |
| multi_admin      | localhost | $A$005$E?D/>efE+Rt12omzr.78VnfR3kxj8KLG.aP84gdPMxW7A/7uG3D80B          |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| root             | localhost | {u]E/m)qyn3YRk2u.JKdxj9/6Krd8uqNtHRzKA38cG5qyC3ts5                     |
+------------------+-----------+------------------------------------------------------------------------+

After that you can grant some privileges to this role, as you usually do for users:

mysqld2-([email protected]) [(none)]> grant select on sakila.* to 'r_sakila_read';
Query OK, 0 rows affected (0.10 sec)
mysqld2-([email protected]) [(none)]> show grants for r_sakila_read;
+---------------------------------------------------+
| Grants for [email protected]%                        |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `r_sakila_read`@`%`         |
| GRANT SELECT ON `sakila`.* TO `r_sakila_read`@`%` |
+---------------------------------------------------+
2 rows in set (0.00 sec)

Now you can create your user:

mysqld2-([email protected]) [(none)]> create user 'u_sakila1'@localhost identified by 'qwepoi123098';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

And yes, check your password policy because of the new validate_password component (starting from version 8.0, it replaces the old validate_password plugin, but the concept remains the same):

mysqld2-([email protected]) [(none)]> show variables like 'validate_password_%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
mysqld2-([email protected]) [(none)]> create user 'u_sakila1'@localhost identified by 'QwePoi123098!';
Query OK, 0 rows affected (0.08 sec)

In my example I have by default a MEDIUM level for checking passwords which means “Length; numeric, lowercase/uppercase, and special characters” (I will better talk about validate_password component in an upcoming blog). Let’s go back to roles…

Grant the created role to your created user (as you usually grant a privilege):

mysqld2-([email protected]) [(none)]> grant 'r_sakila_read' to 'u_sakila1'@localhost;
Query OK, 0 rows affected (0.01 sec)
mysqld2-([email protected]) [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

At this point if you check privileges of your user through a USING clause, you will get information about the granted roles and also privileges associated with each role:

mysqld2-([email protected]) [(none)]> show grants for 'u_sakila1'@localhost using 'r_sakila_read';
+-------------------------------------------------------+
| Grants for [email protected]                        |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `u_sakila1`@`localhost`         |
| GRANT SELECT ON `sakila`.* TO `u_sakila1`@`localhost` |
| GRANT `r_sakila_read`@`%` TO `u_sakila1`@`localhost`  |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

Now if you try to connect with your user and do a select of data on the database on which you have a read privilege, you will discover that something is still missing:

mysqld2-([email protected]) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-([email protected]) [(none)]> use sakila;
ERROR 1044 (42000): Access denied for user 'u_sakila1'@'localhost' to database 'sakila'
mysqld2-([email protected]) [(none)]> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

Why?
Because you have to define which roles will be active when the user authenticates. You you can do that by adding the “DEFAULT ROLE role” during the user creation (starting from version 8.0.3), or even later through the following statement:

mysqld2-([email protected]) [(none)]> set default role r_sakila_read to 'u_sakila1'@localhost;
Query OK, 0 rows affected (0.08 sec)

Otherwise, starting from version 8.0.2, you can directly let the server activate by default all roles granted to each user, setting the activate_all_roles_on_login variable to ON:

mysqld2-([email protected]) [(none)]> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysqld2-([email protected]) [(none)]> set global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)
mysqld2-([email protected]) [(none)]> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

So if you check again, all works correctly:

mysqld2-([email protected]) [mysql]> select * from role_edges;
+-----------+----------------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER      | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
+-----------+----------------+-----------+-----------+-------------------+
| %         | r_sakila_read  | localhost | u_sakila1 | N                 |
+-----------+----------------+-----------+-----------+-------------------+
4 rows in set (0.00 sec)
mysqld2-([email protected]) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-([email protected]) [(none)]> use sakila
mysqld2-([email protected]) [sakila]> connect
Connection id:    29
Current database: sakila
mysqld2-([email protected]localhost) [sakila]> select CURRENT_ROLE();
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `r_sakila_read`@`%` |
+---------------------+
1 row in set (0.00 sec)

Enjoy your roles now! 😉

Leave a Reply

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

Elisa Usai
Elisa Usai

Delivery Manager & Consultant