blog

How to Use the Roles That Have Changed in MySQL 8.0

Joshua Otwell

Published: January 31, 2019
Last Updated: May 4, 2022

Database Security is important to any MySQL setup. Users are the foundation of any system. In terms of database systems, I generally think of them in two distinct groups:

Application, service, or program users - basically customers or clients using a service.
Database developers, administrators, analyst, etc… - Those maintaining, working with or monitoring the database infrastructure.

While each user does need to access the database at some level, those permissions are not all created equal.

For instance, clients and customers need access to their ‘related user account’ data, but even that should be monitored with some level of control. However, some tables and data should be strictly off-limits (E.g., system tables).

Nevertheless:

Analyst need ‘read access‘, to garner information and insight via querying tables…
Developers require a slew of permissions and privileges to carry out their work…
DBA’s need ‘root’ or similar type privileges to run the show…
Buyers of a service need to see their order and payment history…

You can imagine (I know I do) just how difficult a task managing multiple users or groups of users within a database ecosystem is.

In older versions of MySQL, a multiple-user environment is established in a somewhat monotonous and repetitive manner.

Yet, version 8 implements an exceptional, and powerful, SQL standard feature - Roles - which alleviates one of the more redundant areas of the entire process: assigning privileges to a user.

So, what is a role in MySQL?

You can surely visit, MySQL in 2018: What’s in 8.0 and Other Observations, I wrote for the Severalnines blog here where I mention roles for a high-level overview. However, where I only summarized them there, this current post looks to go deeper and focus solely on roles.

Here is how the online MySQL documentation defines a role: “A MySQL role is a named collection of privileges”.

Doesn’t that definition alone seem helpful?

But how?

We will see in the examples that follow.

To Make Note of the Examples Provided

The examples included in this post are in a personal ‘single-user’ development and learning workstation/environment so be sure and implement those best practices that benefit you for your particular needs or requirements. The user names and passwords demonstrated are purely arbitrary and weak.

Users and Privileges in Previous Versions

In MySQL 5.7, roles do not exist. Assigning privileges to users is done individually. To better understand what roles do provide, let’s not use them. That doesn’t make any sense at all, I know. But, as we progress through the post, it will.

Below we create some users:

CREATE USER 'reader_1'@'localhost' IDENTIFIED BY 'some_password'; 
CREATE USER 'reader_writer'@'localhost' IDENTIFIED BY 'another_password'; 
CREATE USER 'changer_1'@'localhost' IDENTIFIED BY 'a_password';

Then those users are granted some privileges:

GRANT SELECT ON some_db.specific_table TO 'reader_1'@'localhost';
GRANT SELECT, INSERT ON some_db.specific_table TO 'reader_writer'@'localhost';
GRANT UPDATE, DELETE ON some_db.specific_table TO 'changer_1'@'localhost';

Whew, glad that is over. Now back to…

And just like that, you have a request to implement two more ‘read-only’ users…

Back to the drawing board:

CREATE USER 'reader_2'@'localhost' IDENTIFIED BY 'password_2'; 
CREATE USER 'reader_3'@'localhost' IDENTIFIED BY 'password_3';

Assigning them privileges as well:

GRANT SELECT ON some_db.specific_table TO 'reader_2'@'localhost';
GRANT ALL ON some_db.specific_table TO 'reader_3'@'localhost';

Can you see how this is less-than-productive, full of repetition, and error-prone? But, more importantly, did you catch the mistake?

Good for you!

While granting privileges for these two additional users, I accidentally granted ALL privileges to new user reader_3.

Oops.

A mistake that anyone could make.

Enter MySQL Roles

With roles, much of the above systematic privilege assignment and delegation can be somewhat streamlined.

User creation basically remains the same, but it’s assigning privileges through roles that differs:

mysql> CREATE USER 'reader_1'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE USER 'reader_writer'@'localhost' IDENTIFIED BY 'another_password';
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE USER 'changer_1'@'localhost' IDENTIFIED BY 'a_password';
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE USER 'reader_2'@'localhost' IDENTIFIED BY 'password_2';
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE USER 'reader_3'@'localhost' IDENTIFIED BY 'password_3';
Query OK, 0 rows affected (0.12 sec)

Querying the mysql.user system table, you can see those newly created users exist:

(Note: I have several user accounts in this learning/development environment and have suppressed much of the output for better on-screen clarity.)

mysql> SELECT User FROM mysql.user;
+------------------+
| User             |
+------------------+
| changer_1        |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| reader_1         |
| reader_2         |
| reader_3         |
| reader_writer    |
| root             |
|                  | --multiple rows remaining here...
+------------------+
23 rows in set (0.00 sec)

I have this arbitrary table and sample data:

mysql> SELECT * FROM name;
+--------+------------+
| f_name | l_name     |
+--------+------------+
| Jim    | Dandy      |
| Johhny | Applesauce |
| Ashley | Zerro      |
| Ashton | Zerra      |
| Ashmon | Zerro      |
+--------+------------+
5 rows in set (0.00 sec)

Let’s now use roles to establish and assign, privileges for the new users to use the name table.

First, create the roles:

mysql> CREATE ROLE main_read_only;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE ROLE main_read_write;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE ROLE main_changer;
Query OK, 0 rows affected (0.14 sec)

Notice the mysql.user table again:

mysql> SELECT User FROM mysql.user;
+------------------+
| User             |
+------------------+
| main_changer     |
| main_read_only   |
| main_read_write  |
| changer_1        |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| reader_1         |
| reader_2         |
| reader_3         |
| reader_writer    |
| root             |
|                  |
+------------------+
26 rows in set (0.00 sec)

Based on this output, we can surmise; that in all essence, roles are in fact, users themselves.

Next, privilege assignment:

mysql> GRANT SELECT ON practice.name TO 'main_read_only';
Query OK, 0 rows affected (0.14 sec)

mysql> GRANT SELECT, INSERT ON practice.name TO 'main_read_write';
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT UPDATE, DELETE ON practice.name TO 'main_changer';
Query OK, 0 rows affected (0.16 sec)

A Brief Interlude

Wait a minute. Can I just log in and carry out any tasks with the role accounts themselves? After all, they are users and they have the required privileges.

Let’s attempt to log in to the practice database with role main_changer:

:~$ mysql -u main_changer -p practice
Enter password: 
ERROR 1045 (28000): Access denied for user 'main_changer'@'localhost' (using password: YES

The simple fact that we are presented with a password prompt is a good indication that we cannot (at this time at least). As you recall, I did not set a password for any of the roles during their creation.

What does the mysql.user system tables’ authentication_string column have to say?

mysql> SELECT User, authentication_string, password_expired
    -> FROM mysql.user
    -> WHERE User IN ('main_read_only', 'root', 'main_read_write', 'main_changer')G
*************************** 1. row ***************************
                 User: main_changer
authentication_string: 
     password_expired: Y
*************************** 2. row ***************************
                 User: main_read_only
authentication_string: 
     password_expired: Y
*************************** 3. row ***************************
                 User: main_read_write
authentication_string: 
     password_expired: Y
*************************** 4. row ***************************
                 User: root
authentication_string: ***various_jumbled_mess_here*&&*&*&*##
     password_expired: N
4 rows in set (0.00 sec)

I included the root user among the role names for the IN() predicate check to simply demonstrate it has an authentication_string, where the roles do not.

This passage in the CREATE ROLE documentation clarifies it nicely: “A role when created is locked, has no password, and is assigned the default authentication plugin. (These role attributes can be changed later with the ALTER USER statement, by users who have the global CREATE USER privilege.)”

Back to the task at hand, we can now assign the roles to users based on their needed level of privileges.

Notice no ON clause is present in the command:

mysql> GRANT 'main_read_only' TO 'reader_1'@'localhost', 'reader_2'@'localhost', 'reader_3'@'localhost';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT 'main_read_write' TO 'reader_writer'@'localhost';
Query OK, 0 rows affected (0.16 sec)

mysql> GRANT 'main_changer', 'main_read_only' TO 'changer_1'@'localhost';
Query OK, 0 rows affected (0.13 sec)

It may be less confusing if you use some sort of ‘naming convention‘ when establishing role names, (I am unaware if MySQL provides one at this time… Community?) if for no other reason than to differentiate between them and regular ‘non-role’ users visually.

There is Still Some Work Left To Do

That was super-easy wasn’t it?

Less redundant than the old way of privilege assignment.

Let’s put those users to work now.

We can see the granted privileges for a user with SHOW GRANTS syntax. Here is what is currently assigned to the reader_1 user account:

mysql> SHOW GRANTS FOR 'reader_1'@'localhost';
+------------------------------------------------------+
| Grants for reader_1@localhost                        |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `reader_1`@`localhost`         |
| GRANT `main_read_only`@`%` TO `reader_1`@`localhost` |
+------------------------------------------------------+
2 rows in set (0.02 sec)

Although that does provide an informative output, you can ‘tune‘ the statement for even more granular information on any exact privileges an assigned role provides by including a USING clause in the SHOW GRANTS statement and naming the assigned roles name:

mysql> SHOW GRANTS FOR 'reader_1'@'localhost' USING 'main_read_only';
+-------------------------------------------------------------+
| Grants for reader_1@localhost                               |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `reader_1`@`localhost`                |
| GRANT SELECT ON `practice`.`name` TO `reader_1`@`localhost` |
| GRANT `main_read_only`@`%` TO `reader_1`@`localhost`        |
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

After logging in with reader_1:

mysql> SELECT * FROM practice.name;
ERROR 1142 (42000): SELECT command denied to user 'reader_1'@'localhost' for table 'name'

What on earth? That user was granted SELECT privileges through role main_read_only.

To investigate, let’s visit 2 new tables in version 8, specifically for roles.

The mysql.role_edges table shows what roles have been granted to any users:

mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------------+-----------+---------------+-------------------+
| FROM_HOST | FROM_USER       | TO_HOST   | TO_USER       | WITH_ADMIN_OPTION |
+-----------+-----------------+-----------+---------------+-------------------+
| %         | main_changer    | localhost | changer_1     | N                 |
| %         | main_read_only  | localhost | changer_1     | N                 |
| %         | main_read_only  | localhost | reader_1      | N                 |
| %         | main_read_only  | localhost | reader_2      | N                 |
| %         | main_read_only  | localhost | reader_3      | N                 |
| %         | main_read_write | localhost | reader_writer | N                 |
+-----------+-----------------+-----------+---------------+-------------------+
6 rows in set (0.00 sec)

But, I feel the other additional table, mysql.default_roles, will better help us solve the SELECT problems for user reader_1:

mysql> DESC mysql.default_roles;
+-------------------+----------+------+-----+---------+-------+
| Field             | Type     | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------+-------+
| HOST              | char(60) | NO   | PRI |         |       |
| USER              | char(32) | NO   | PRI |         |       |
| DEFAULT_ROLE_HOST | char(60) | NO   | PRI | %       |       |
| DEFAULT_ROLE_USER | char(32) | NO   | PRI |         |       |
+-------------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.default_roles;
Empty set (0.00 sec)

Empty results set.

Turns out, in order for a user to be able to use a role - and ultimately the privileges – the user must be assigned a default role.

mysql> SET DEFAULT ROLE main_read_only TO 'reader_1'@'localhost', 'reader_2'@'localhost', 'reader_3'@'localhost';
Query OK, 0 rows affected (0.11 sec)

(A default role can be assigned to multiple users in one command as above…)

mysql> SET DEFAULT ROLE main_read_only, main_changer TO 'changer_1'@'localhost';
Query OK, 0 rows affected (0.10 sec)

(A user can have multiple default roles specified as in the case for user changer_1…)

User reader_1 is now logged in…

mysql> SELECT CURRENT_USER();
+--------------------+
| CURRENT_USER()     |
+--------------------+
| reader_1@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+----------------------+
| CURRENT_ROLE()       |
+----------------------+
| `main_read_only`@`%` |
+----------------------+
1 row in set (0.03 sec)

We can see the currently active role and also, that reader_1 can issue SELECT commands now:

mysql> SELECT * FROM practice.name;
+--------+------------+
| f_name | l_name     |
+--------+------------+
| Jim    | Dandy      |
| Johhny | Applesauce |
| Ashley | Zerro      |
| Ashton | Zerra      |
| Ashmon | Zerro      |
+--------+------------+
5 rows in set (0.00 sec)

Other Hidden Nuances

There is another important part of the puzzle we need to understand.

There are potentially 3 different ‘levels’ or ‘variants’ of role assignment:

SET ROLE …;
SET DEFAULT ROLE …;
SET ROLE DEFAULT …;

I’ll GRANT an additional role to user reader_1 and then login with that user (not shown):

mysql> GRANT 'main_read_write' TO 'reader_1'@'localhost';
Query OK, 0 rows affected (0.17 sec)

Since role main_read_write does have the INSERT privilege, user reader_1 can now run that command right?

mysql> INSERT INTO name(f_name, l_name)
    -> VALUES('Josh', 'Otwell');
ERROR 1142 (42000): INSERT command denied to user 'reader_1'@'localhost' for table 'name'

What is going on here?

This may help…

mysql> SELECT CURRENT_ROLE();
+----------------------+
| CURRENT_ROLE()       |
+----------------------+
| `main_read_only`@`%` |
+----------------------+
1 row in set (0.00 sec)

Recall, we initially set user reader_1 a default role of main_read_only. This is where we need to use one of those distinct ‘levels’ of what I loosely term ‘role setting’:

mysql> SET ROLE main_read_write;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-----------------------+
| CURRENT_ROLE()        |
+-----------------------+
| `main_read_write`@`%` |
+-----------------------+
1 row in set (0.00 sec)

Now attempt that INSERT again:

mysql> INSERT INTO name(f_name, l_name)
    -> VALUES('Josh', 'Otwell');
Query OK, 1 row affected (0.12 sec)

However, once user reader_1 logs back out, role main_read_write will no longer be active when reader_1 logs back in. Although user reader_1 does have the main_read_write role granted to it, it is not the default.

Let’s now come to know the 3rd ‘level’ of ‘role setting’, SET ROLE DEFAULT.

Suppose user reader_1 has no roles assigned yet:

mysql> SHOW GRANTS FOR 'reader_1'@'localhost';
+----------------------------------------------+
| Grants for reader_1@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `reader_1`@`localhost` |
+----------------------------------------------+
1 row in set (0.00 sec)

Let’s GRANT this user 2 roles:

mysql> GRANT 'main_changer', 'main_read_write' TO 'reader_1'@'localhost';
Query OK, 0 rows affected (0.07 sec)

Assign a default role:

mysql> SET DEFAULT ROLE ‘main_changer’ TO 'reader_1'@'localhost';
Query OK, 0 rows affected (0.17 sec)

Then with user reader_1 logged in, that default role is active:

mysql> SELECT CURRENT_ROLE();
+--------------------+
| CURRENT_ROLE()     |
+--------------------+
| `main_changer`@`%` |
+--------------------+
1 row in set (0.00 sec)

Now switch to role main_read_write:

mysql> SET ROLE 'main_read_write';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT CURRENT_ROLE();
+-----------------------+
| CURRENT_ROLE()        |
+-----------------------+
| `main_read_write`@`%` |
+-----------------------+
1 row in set (0.00 sec)

But, to return back to the assigned default role, use SET ROLE DEFAULT as shown below:

mysql> SET ROLE DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+--------------------+
| CURRENT_ROLE()     |
+--------------------+
| `main_changer`@`%` |
+--------------------+
1 row in set (0.00 sec)

Roles Not Granted

Even though user changer_1 has 2 roles available during a session:

mysql> SELECT CURRENT_ROLE();
+-----------------------------------------+
| CURRENT_ROLE()                          |
+-----------------------------------------+
| `main_changer`@`%`,`main_read_only`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

What happens if you attempt and set a user to a role they have not been granted?

mysql> SET ROLE main_read_write;
ERROR 3530 (HY000): `main_read_write`@`%` is not granted to `changer_1`@`localhost`

Denied.

Taketh Away

No user management system would be complete without the ability to constrain or even remove access to certain operations should the need arise.

We have the SQL REVOKE command at our disposal to remove privileges from users and roles.

Recall that role main_changer has this set of privileges, essentially, all of those users granted this role do as well:

mysql> SHOW GRANTS FOR main_changer;
+-----------------------------------------------------------------+
| Grants for main_changer@%                                       |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `main_changer`@`%`                        |
| GRANT UPDATE, DELETE ON `practice`.`name` TO `main_changer`@`%` |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> REVOKE DELETE ON practice.name FROM 'main_changer';
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW GRANTS FOR main_changer;
+---------------------------------------------------------+
| Grants for main_changer@%                               |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `main_changer`@`%`                |
| GRANT UPDATE ON `practice`.`name` TO `main_changer`@`%` |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

To know what users this change affected, we can visit the mysql.role_edges table again:

mysql> SELECT * FROM mysql.role_edges WHERE FROM_USER = 'main_changer';
+-----------+--------------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER    | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
+-----------+--------------+-----------+-----------+-------------------+
| %         | main_changer | localhost | changer_1 | N                 |
+-----------+--------------+-----------+-----------+-------------------+
1 row in set (0.00 sec)

And we can see that user changer_1 no longer has the DELETE privilege:

mysql> SHOW GRANTS FOR 'changer_1'@'localhost' USING 'main_changer';
+--------------------------------------------------------------------------+
| Grants for changer_1@localhost                                           |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `changer_1`@`localhost`                            |
| GRANT UPDATE ON `practice`.`name` TO `changer_1`@`localhost`             |
| GRANT `main_changer`@`%`,`main_read_only`@`%` TO `changer_1`@`localhost` |
+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Finally, if we need to get rid of a role completely, we have the DROP ROLE command for that:

mysql> DROP ROLE main_read_only;
Query OK, 0 rows affected (0.17 sec)

And querying the mysql.role_edges table, role main_read_only has been removed:

mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------------+-----------+---------------+-------------------+
| FROM_HOST | FROM_USER       | TO_HOST   | TO_USER       | WITH_ADMIN_OPTION |
+-----------+-----------------+-----------+---------------+-------------------+
| %         | main_changer    | localhost | changer_1     | N                 |
| %         | main_read_write | localhost | reader_1      | N                 |
| %         | main_read_write | localhost | reader_writer | N                 |
+-----------+-----------------+-----------+---------------+-------------------+
3 rows in set (0.00 sec)

(Bonus: This fantastic YouTube video was a great learning resource for me on Roles.)

This example of user creation, role assignment, and setup is rudimentary at best. Yet, roles have their own set of rules that make them far from trivial. My hope is that through this blog post, I have shed light on those areas that are less intuitive than others, enabling readers to better understand potential role uses within their systems.

Thank you for reading.

Implementing the golden backup rule for your databases

Best practices for Elasticsearch security

DevOpsDays Houston 2024: Our conference highlights

What’s new in PG15