Infrastructure at your Service

Elisa Usai

MySQL 8.0 – Roles are finally there

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-(root@localhost) [(none)]> CREATE ROLE 'r_sakila_read';
Query OK, 0 rows affected (0.03 sec)
mysqld2-(root@localhost) [(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-(root@localhost) [(none)]> grant select on sakila.* to 'r_sakila_read';
Query OK, 0 rows affected (0.10 sec)
mysqld2-(root@localhost) [(none)]> show grants for r_sakila_read;
+---------------------------------------------------+
| Grants for r_sakila_read@%                        |
+---------------------------------------------------+
| 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-(root@localhost) [(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, starting from version 8.0, the new validate_password component replaces the old validate_password plugin and is now enabled by default and you don’t have to install it anymore.

mysqld2-(root@localhost) [(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-(root@localhost) [(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-(root@localhost) [(none)]> grant 'r_sakila_read' to 'u_sakila1'@localhost;
Query OK, 0 rows affected (0.01 sec)
mysqld2-(root@localhost) [(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-(root@localhost) [(none)]> show grants for 'u_sakila1'@localhost using 'r_sakila_read';
+-------------------------------------------------------+
| Grants for u_sakila1@localhost                        |
+-------------------------------------------------------+
| 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-(root@localhost) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-(u_sakila1@localhost) [(none)]> use sakila;
ERROR 1044 (42000): Access denied for user 'u_sakila1'@'localhost' to database 'sakila'
mysqld2-(u_sakila1@localhost) [(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-(root@localhost) [(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-(root@localhost) [(none)]> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysqld2-(root@localhost) [(none)]> set global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)
mysqld2-(root@localhost) [(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-(root@localhost) [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-(root@localhost) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-(u_sakila1@localhost) [(none)]> use sakila
mysqld2-(u_sakila1@localhost) [sakila]> connect
Connection id:    29
Current database: sakila
mysqld2-(u_sakila1@localhost) [sakila]> select CURRENT_ROLE();
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `r_sakila_read`@`%` |
+---------------------+
1 row in set (0.00 sec)

Enjoy your roles now! ;)

 

Leave a Reply

Elisa Usai
Elisa Usai