MySQL security for real users

Security features overview

One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:

1. MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work properly.
2. The "root" account now comes with a password defined during initialization. This is good news for security, but bad news for how the change was implemented.
3. There is a new way of setting an options file for connection credentials: the mysql_config_editor paired with option --login-path allows users to store encrypted credentials for secure use. Also here, while we should rejoice for the added security, we can't help feeling that the implementation is yet again far from meeting users needs.
4. There is an useful warning (introduced in MySQL 5.6) when using a password on the command line, telling users that it is a risk. Also in this case, we have a usability issue: while users care about their production deployments and use option files to avoid using passwords on the command line, there are, nonetheless, a lot of testing scripts, used in safe environment or with non-valuable data, where a password in the command line was not an issue, and the new warning simply screws up the result of those carefully crafted tests. This change, which can't be overcome without modifying the MySQL clients code, needs users to change their existing tests to adapt to the new behavior.
5. MySQL 8 introduces roles, which simplify the accounts management. There are some minor usability issues, although in general the feature meets expectations.

This is the scenario of the main enhancements in MySQL since 5.6. Each one of them has some usability problems, some minor, some really bad.
We will first have a look at the problems mentioned above, and then examine the root cause for why they have arisen.

Usability issues

I start by noticing that some developers in the MySQL team have been working there for many years, starting with the time when MySQL was a different database and was used really differently.
In those times, managing the database meant that a human (the DBA) would run operations manually, take a look at the result, and adjust when needed. And then, when things went wrong, the same human explored the database system to find out what happened, took action, and went back to sleep.

Human-centered management leads to human problems: lazy DBA left their databases without password, using the root account, and exposing the server to uninspired attacks; they used passwords on the command line, without caring for options files (or without knowing about them.) Careless DBAs did not deal with anonymous users, leaving a dangerous backdoor in their server.

Some of the new functionalities introduced in the latest MySQL versions are aimed at this type of users: when you install MySQL, you get a message saying: your root password is ************, and the lazy DBAs have no option but to take note and use it. When they use the password on the command line, the annoying warning forces them to start using an options file or the mysql_config_editor.

This is all good, but the main problem here is that the DBAs of 10 years ago are on the verge of extinction. They are replaced by a new breed of DBAs who are not lazy, because they can't afford to be, and need to use dozens, hundreds, thousands of databases at once, using configuration management tools that don't require manual intervention, and actually abhor it. In the land of automation, some of the MySQL security enhancements are not seen as a solution, but as new problems.

Let's see an interesting example: docker containers.

Using Docker, MySQL images are deployed using a password on the command line. This is done for compatibility with the first implementation of the image maintained by the Docker team, where you deploy with this syntax:

docker run -e MYSQL_ROOT_PASSWORD=secret -d mysql

The MYSQL_ROOT_PASSWORD is a directive that becomes an environment variable inside the container, and the server uses it during initialization. As you can imagine, this is not recommended for a secure environment. Then, what's the MySQL team recommendation? They suggest the same strategy used for manual installation: set a directive MYSQL_RANDOM_ROOT_PASSWORD that results in a random password being generated, then collected by the DBA and used. Alternatively, the directive MYSQL_ONETIME_PASSWORD will force the root user to change the password on first connection.

The above suggestions were designed with the ancient DBA still in mind, while container deployment is even more automated than VMs, and it is based on the principle of immutable objects, i.e. containers that spring up from the cloud ready to run, with no configuration needed, and especially no configuration that requires someone (or some tool) to extract a new password from a log. I proposed a different solution, that would never show passwords on the command line and while it was implemented, but it still feels like a hack to circumvent an inadequate design.

As a result, the implementation inside the MySQL recommended Docker image uses "--initialize-insecure" to start the server. This is an implicit recognition of the bad design of the initialization feature. What was designed to overcome DBA's laziness becomes an obstacle towards automation.

We have a similar problem with mysql_config_editor: the tool will create a safe configuration file with credentials for multiple instances, but the password must be inserted manually. Consequently, this potentially useful feature doesn't get adopted, because it would be too difficult or impossible to automate properly.

We have seen that, of the security features that were introduced lately, only a few can be used safely in an automated environment, and all of them have at least one small usability quirk. I have talked about a confusing issue related to the removal of anonymous users where in their eagerness of removing the vulnerability the MySQL team removed also the "test" database, which was a consequence, not the cause of the problem. And I have recently talked about roles usability where there are still open problems, like the ability of telling roles from users which are apparently not considered a bug by the MySQL team.

All the above considerations led me to ask: how did we get to this point? There is an active community, and feedback is offered often with plenty of detail. How come we have such an abundance of usability issues? Don't the developers spend time with users at conferences to learn what they expect? Don't they read articles and blog posts about how a new feature meets expectations? Don't they talk to customers who have adopted new features? They certainly do. Then, why the usability problems persist?

What follows is my observation and speculation on this matter.

Disconnection between MySQL developers and users community

My experience working with system providers has put me in contact with many users. I have seen that in most cases users are very much protective of their current deployment, because it took them long time to get it right, and they don't upgrade unless they don't have another choice. I've seen users test the newer versions, realize that they would break some of their procedures, and defer the upgrade to better times that never come. I remember last year a user with a not so large set of servers was considering an upgrade to MySQL 5.6, while 5.7 had been GA for two years. The reason was a set of incompatibilities that made the upgrade too difficult.

For companies that deal with thousands of servers, the problem is similar, but exacerbated by the quantity of servers to upgrade and the need to do it without stopping operations. This latest requirement has made some users decide not to use GTID, because it required offline time for a master, and they hadn't had time enough to test the upgrade to MySQL 5.7 that would solve that problem.

For one reason or the other, many companies upgrade only two or three years after a given version became GA. And this is the main problem: until they use it in production, or at least test the version for a projected upgrade, users can't give valuable feedback, the one that is related to usage in production, and when they do, the version for which they provide feedback has been GA for long time, and can't be changed, while the next one is already close to GA, and as such will be untouchable.

The MySQL team gets feedback on a release from a handful of curious users who don't delay testing until the new version is GA, but don't provide the kind of important feedback that get the development team attention, such as deployment in production by large customers. In many cases, large customers are the ones that upgrade several years after GA, and by then their input is difficult to act upon.

We have then a curious situation, where the warnings given by the early software explorers are confirmed years later by the users to which the MySQL team listens more closely, but by then the next version of the server is already locked in a release schedule that nobody wants to alter to fix usability issues.

How can we solve this problem? Simple: listen to early software explorers and try to fix problems before GA.

Revisiting roles in MySQL 8.0

In my previous article about roles I said that one of the problems with role usage is that roles need to be activated before they kick in. Let's recap briefly what the problem is:

## new session, as user `root`

mysql [localhost] {root} ((none)) > create role viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant select on *.* to viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > create user see_it_all identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant viewer to see_it_all;
Query OK, 0 rows affected (0.01 sec)

## NEW session, as user `see_it_all`

mysql [localhost] {see_it_all} ((none)) > use test
ERROR 1044 (42000): Access denied for user 'see_it_all'@'%' to database 'test'

mysql [localhost] {see_it_all} ((none)) > show grants\G
*************************** 1. row ***************************
Grants for see_it_all@%: GRANT USAGE ON *.* TO `see_it_all`@`%`
*************************** 2. row ***************************
Grants for see_it_all@%: GRANT `viewer`@`%` TO `see_it_all`@`%`
2 rows in set (0.00 sec)

mysql [localhost] {see_it_all} (test) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

We can create a simple role that gives read-only access to most database objects, and assign it to a user. However, when the new user tries accessing one database, it is rejected. The problem is that the role must be activated, either permanently, or for the current session.

mysql [localhost] {see_it_all} ((none)) > set role viewer;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {see_it_all} (test) > select current_role();
+----------------+
| current_role() |
+----------------+
| `viewer`@`%`   |
+----------------+
1 row in set (0.00 sec)

mysql [localhost] {see_it_all} ((none)) > use test
Database changed

mysql [localhost] {see_it_all} (test) > show grants\G
*************************** 1. row ***************************
Grants for see_it_all@%: GRANT SELECT ON *.* TO `see_it_all`@`%`
*************************** 2. row ***************************
Grants for see_it_all@%: GRANT `viewer`@`%` TO `see_it_all`@`%`
2 rows in set (0.00 sec)

The main issue here is that the role is not active immediately. If we grant a given privilege to a user, the user will be able to operate under that privilege straight away. If we grant a role, instead, the user can't use it immediately. Roles need to be activated, either by the giver or by the receiver.

Auto activating roles

In MySQL 8.0.2 there are two new features related to roles, and one of them addresses the main problem we have just seen. When we use activate_all_roles_on_login, all roles become active when the user starts a session, regardless of any role activation that may pre-exist. Let's try. In the previous example, as root, we issue this command:

mysql [localhost] {root} ((none)) > set global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)

Then, we connect as user see_it_all

mysql [localhost] {see_it_all} ((none)) > select current_role();
+----------------+
| current_role() |
+----------------+
| `viewer`@`%`   |
+----------------+
1 row in set (0.00 sec)

mysql [localhost] {see_it_all} ((none)) > use test
Database changed

The role is active. The current role can be overridden temporarily using SET ROLE:

mysql [localhost] {see_it_all} ((none)) > use test
Database changed

mysql [localhost] {see_it_all} (test) > set role none;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {see_it_all} (test) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql [localhost] {see_it_all} (test) > show tables;
ERROR 1044 (42000): Access denied for user 'see_it_all'@'%' to database 'test'

This is a good option, which can further simplify DBAs work. There are, as usual, a few caveats:

• This option has effect only on login, i.e. when the user starts a new session. Users that are already logged in when the option is changed will not be affected until they re-connect.
• Use of this option can have adverse effects when using combinations of roles. If the DBA intent is to give users several roles that should be used separately, using activate_all_roles_on_login will make the paradigm more difficult to use. Let's see an example:

CREATE ROLE payroll_viewer ;
GRANT SELECT ON payroll.* TO payroll_viewer;

CREATE ROLE payroll_updater;
GRANT CREATE, INSERT, UPDATE, DELETE ON payroll.* TO payroll_updater;

CREATE ROLE personnel_viewer ;
GRANT SELECT ON personnel.* TO personnel_viewer;

CREATE ROLE personnel_updater;
GRANT CREATE, INSERT, UPDATE, DELETE ON personnel.* TO personnel_updater;

CREATE ROLE payroll;
GRANT payroll_updater, payroll_viewer, personnel_viewer to payroll;

CREATE ROLE personnel;
GRANT personnel_updater, personnel_viewer to personnel;

CREATE USER pers_user identified by 'msandbox';
CREATE USER pers_manager identified by 'msandbox';
CREATE USER pay_user identified by 'msandbox';

GRANT personnel to pers_user;
GRANT personnel, payroll_viewer to pers_manager;
GRANT payroll to pay_user;

SET DEFAULT ROLE personnel TO pers_user;
SET DEFAULT ROLE personnel TO pers_manager;
SET DEFAULT ROLE payroll TO pay_user;

In the above situation, we want the user pers_manager to see the personnel records by default, but she needs to manually activate payroll_viewer to see the payroll.
If we set activate_all_roles_on_login, pers_manager would be able to see payroll info without further action.

Mandatory roles

Another option introduced in 8.0.2 is mandatory_roles. This variable can be set with a list of roles. When set, the roles in the list will be added to the privileges of all users, including future ones.

Here's an example of how this feature could be useful. We want a schema containing data that should be accessible to all users, regardless of their privileges.

CREATE SCHEMA IF NOT EXISTS company;

DROP TABLE IF EXISTS company.news;
CREATE TABLE company.news(
    id int not null auto_increment primary key,
    news_type ENUM('INFO', 'WARNING', 'ALERT'),
    contents MEDIUMTEXT);

DROP ROLE IF EXISTS news_reader;
CREATE ROLE news_reader;
GRANT SELECT ON company.* TO news_reader;
SET PERSIST mandatory_roles = news_reader;

In this example, every user that starts a session after mandatory_roles was set will be able to access the "company" schema and read the news from there.

There are at least two side effects of this feature:

• When a role is included in the list of mandatory roles, it can't be dropped.

mysql [localhost] {root} (mysql) > drop role news_reader;
ERROR 4527 (HY000): The role `news_reader`@`%` is a mandatory role and can't be revoked or dropped. 
The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

• users who have already a broad access that include the privileges in the mandatory role will nonetheless have the global role show up in the user list of grants. For example, here is how 'root'@'localhost' grants look like:

mysql [localhost] {root} ((none)) > show grants \G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,
 SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
 CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, 
 CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, 
 CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT 
 OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,
 ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN, 
 REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,
 SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` 
 WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT <b>SELECT ON `company`.*</b> TO `root`@`localhost`
*************************** 4. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
*************************** 5. row ***************************
Grants for root@localhost: GRANT <b>`news_reader`@`%`</b> TO `root`@`localhost`
5 rows in set (0.00 sec)

More gotchas

There are several commands for setting roles. One of them uses ALTER USER, while the rest uses a SET command.

• First gotcha: SET ROLE and SET DEFAULT ROLE don't need an equals sign (=). The syntax is similar to SET CHARACTER SET, not to SET variable. This is a bit confusing, because another security related command (SET PASSWORD) requires the '=' in the assignment.

• Now, for the really entertaining part, here's a list of commands that can give any DBA an headache.

Command	meaning
SET ROLE role_name	Activates the role role_name for the current session.
SET DEFAULT ROLE role_name	Sets the role role_name as default permanently.
SET ROLE DEFAULT	Activates the default role for the current session.

State of bugs

Community members have reported several bugs about roles. While I am happy of the MySQL team response concerning the usability of roles (the automatic activation came after I gave feedback) I am less thrilled by seeing that none of the public bugs reported on this matter have been addressed.Bug#85561 is particularly vexing. I reported that users can be assigned non-existing roles as default. I was answered with a sophism about the inner being of a default role, and the bug report was closed with a "Won't fix" state. I disagree with this characterisation. The behaviour that I reported is a bug because it allows users to write a wrong statement without a warning or an error. I hope the team will reconsider and take action to improve the usability of roles.

MySQL 8.0 roles

One of the most interesting features introduced in MySQL 8.0 is roles or the ability of defining a set of privileges as a named role and then granting that set to one or more users. The main benefits are more clarity of privileges and ease of administration. Using roles we can assign the same set of privileges to several users, and eventually modify or revoke all privileges at once.

Roles in a nutshell

Looking at the manual, we see that using roles is a matter of several steps.

(1) Create a role. The statement is similar to CREATE USER though the effects are slightly different (we will see it in more detail later on.)

mysql [localhost] {root} ((none)) > CREATE ROLE r_lotr_dev;
Query OK, 0 rows affected (0.02 sec)

(2) Grant privileges to the role. Again, this looks like granting privileges to a user.

mysql [localhost] {root} ((none)) > GRANT ALL on lotr.* TO r_lotr_dev;
Query OK, 0 rows affected (0.01 sec)

(3) Create a user. This is the same that we've been doing until version 5.7.

mysql [localhost] {root} (mysql) > create user aragorn identified by 'lotrpwd';
Query OK, 0 rows affected (0.01 sec)

Notice that the role is in the mysql.user table, and looks a lot like a user.

mysql [localhost] {root} ((none)) > select host, user, authentication_string from mysql.user where user not like '%sandbox%;
+-----------+-------------+-------------------------------------------+
| host      | user        | authentication_string                     |
+-----------+-------------+-------------------------------------------+
| %         | r_lotr_dev  |                                           | 
| %         | aragorn     | *3A376D0203958F6EB9E6166DC048EC04F84C00B9 |
| localhost | mysql.sys   | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
+-----------+-------------+-------------------------------------------+

(4) Grant the role to the user. Instead of granting single privileges, we grant the role. Note that when we use this syntax we can't specify the ON xxx clause, because it is already implicit in the role definition.

mysql [localhost] {root} (mysql) > grant r_lotr_dev to aragorn;
Query OK, 0 rows affected (0.03 sec)

The relationship between user and role is recorded in a new table in the mysql database:

mysql [localhost] {root} (mysql) > select * from mysql.role_edges;
+-----------+------------+---------+---------+-------------------+
| FROM_HOST | FROM_USER  | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+------------+---------+---------+-------------------+
| %         | r_lotr_dev | %       | aragorn | N                 |
+-----------+------------+---------+---------+-------------------+

(5) Finally we set the default role. Until this point, the role is assigned to the user, but not active. We either set the default role permanently (as done below) or we let the user activate the role.

mysql [localhost] {root} (mysql) > alter user aragorn default role r_lotr_dba;
Query OK, 0 rows affected (0.01 sec)

If a default role has been set, it is recorded in another new table, mysql.default_roles.

mysql [localhost] {root} (mysql) > select * from mysql.default_roles;
+------+---------+-------------------+-------------------+
| HOST | USER    | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+---------+-------------------+-------------------+
| %    | aragorn | %                 | r_lotr_dba        |
+------+---------+-------------------+-------------------+

Common gotchas

If we follow all the steps described above, using roles would not feel any different than using old style grants. But it is easy to go astray if we skip something. Let's see an example. First, we create an user and grant the same role as the one given to user aragorn:

mysql [localhost] {root} (mysql) > create user legolas identified by 'lotrpwd';
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {root} (mysql) > grant r_lotr_dev to legolas;
Query OK, 0 rows affected (0.01 sec)

Then we connect using user legolas:

mysql [localhost] {legolas} ((none)) > use lotr;
ERROR 1044 (42000): Access denied for user 'legolas'@'%' to database 'lotr'
mysql [localhost] {legolas} ((none)) > show grants;
+-----------------------------------------+
| Grants for legolas@%                    |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `legolas`@`%`     |
| GRANT `r_lotr_dev`@`%` TO `legolas`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {legolas} ((none)) > show grants for legolas using r_lotr_dev;
+---------------------------------------------------+
| Grants for legolas@%                              |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `legolas`@`%`               |
| GRANT ALL PRIVILEGES ON `lotr`.* TO `legolas`@`%` |
| GRANT `r_lotr_dev`@`%` TO `legolas`@`%`           |
+---------------------------------------------------+
3 rows in set (0.00 sec)

The role is assigned to the user, but it is not active, as no default role was defined. To use a role, the user must activate one:

mysql [localhost] {legolas} ((none)) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.01 sec)

There is no default role for legolas. We need to assign one.

mysql [localhost] {legolas} ((none)) > set role r_lotr_dev;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role()   |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.00 sec)

Now the role is active, and all its privileges kick in:

mysql [localhost] {legolas} ((none)) > use lotr
Database changed
mysql [localhost] {legolas} (lotr) > show tables;
Empty set (0.00 sec)

mysql [localhost] {legolas} (lotr) > create table t1 (i int not null primary key);
Query OK, 0 rows affected (0.15 sec)

Note that the role activation is volatile. If the user reconnects, the role activation goes away:

mysql [localhost] {legolas} ((none)) > connect;
Connection id:    33
Current database: *** NONE ***

mysql [localhost] {legolas} ((none)) > select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.01 sec)

For a permanent assignment, the user can use the SET DEFAULT ROLE statement:

mysql [localhost] {legolas} ((none)) > set default role r_lotr_dev to legolas;
Query OK, 0 rows affected (0.01 sec)

The above statement corresponds to ALTER USER ... DEFAULT ROLE .... Every user can set its own role with this statement, without needing additional privileges.

mysql [localhost] {legolas} ((none)) > alter user legolas default role r_lotr_dev;
Query OK, 0 rows affected (0.01 sec)

Now if the user reconnects, the role persists:

mysql [localhost] {legolas} ((none)) > connect
Connection id:    34
Current database: *** NONE ***

mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role()   |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.01 sec)

However, if an user sets its own default role using ALTER USER, the change will be available only in the next session, or after calling SET ROLE.

Let's try:

mysql [localhost] {legolas} ((none)) > set default role none to legolas;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {legolas} ((none)) > select current_role();
+------------------+
| current_role()   |
+------------------+
| `r_lotr_dev`@`%` |
+------------------+
1 row in set (0.00 sec)

The role stays what it was before. This is similar to what happens when using SET GLOBAL var_name vs SET SESSION var_name. In the first case the effect persists, but it is not activated immediately, while a session set will be immediately usable, but will not persist after a new connection.

mysql [localhost] {legolas} ((none)) > connect
Connection id:    35
Current database: *** NONE ***

select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

It's worth mentioning that SET DEFAULT ROLE implies an hidden update of a mysql table (default_roles), similar to what happens with SET PASSWORD. In both cases, a user without explicit access to the mysql database will be unable to check the effects of the operation.

Advanced role management.

Dealing with one or two roles is no big deal. Using the statements seen above, we can easily see what privileges were granted to a role or an user. When we have many roles and many users, the overview become more difficult to achieve.

Before we see the complex scenario, let's have a deeper look at what constitutes a role.

mysql [localhost] {root} (mysql) > create role role1;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {root} (mysql) > create user user1 identified by 'somepass';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} (mysql) > select host, user, authentication_string , password_expired , account_locked from user where user in ('role1', 'user1');
+------+-------+-------------------------------------------+------------------+----------------+
| host | user  | authentication_string                     | password_expired | account_locked |
+------+-------+-------------------------------------------+------------------+----------------+
| %    | role1 |                                           | Y                | Y              |
| %    | user1 | *13883BDDBE566ECECC0501CDE9B293303116521A | N                | N              |
+------+-------+-------------------------------------------+------------------+----------------+
2 rows in set (0.00 sec)

The main difference between user and role is that a role is created with password_expired and account_locked. Apart from that, an user could be used as a role and vice versa.

mysql [localhost] {root} (mysql) > alter user role1 identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > alter user role1 account unlock;
Query OK, 0 rows affected (0.02 sec)

Now role1 can access the database as any other user.

mysql [localhost] {root} ((none)) > grant root@'localhost' to user1;
Query OK, 0 rows affected (0.03 sec)

And user1 inherits all privileges from root, but it can access the server from any host.

Now let's see some complex usage of roles. In a typical organisation, we would define several roles to use the lotr database:

CREATE ROLE r_lotr_observer;
CREATE ROLE r_lotr_tester;
CREATE ROLE r_lotr_dev;
CREATE ROLE r_lotr_dba;

GRANT SELECT on lotr.* TO r_lotr_observer;
GRANT SELECT, INSERT, UPDATE, DELETE on lotr.* TO r_lotr_tester;
GRANT ALL on lotr.* TO r_lotr_dev;
GRANT ALL on *.* TO r_lotr_dba;

And then assign those roles to several users:

CREATE USER bilbo     IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER frodo     IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER sam       IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER pippin    IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER merry     IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER boromir   IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gimli     IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER aragorn   IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER legolas   IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gollum    IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER galadriel IDENTIFIED BY 'msandbox' PASSWORD EXPIRE;
CREATE USER gandalf   IDENTIFIED BY 'msandbox';

GRANT r_lotr_observer TO pippin, merry, boromir, gollum;
SET DEFAULT ROLE r_lotr_observer to pippin, merry, boromir, gollum;

GRANT r_lotr_tester TO sam, bilbo, gimli;
SET DEFAULT ROLE r_lotr_tester to sam, bilbo, gimli;

GRANT r_lotr_dev to frodo, aragorn, legolas;
SET DEFAULT ROLE r_lotr_dev to frodo, aragorn, legolas;

GRANT r_lotr_dba TO gandalf, galadriel;
SET DEFAULT ROLE r_lotr_dba to gandalf, galadriel;

Now we have 12 users with 4 different roles. Looking at the user table, we don't get a good overview:

mysql [localhost] {root} (mysql) > select host, user, authentication_string from mysql.user where user not like '%sandbox%';
+-----------+-----------------+-------------------------------------------+
| host      | user            | authentication_string                     |
+-----------+-----------------+-------------------------------------------+
| %         | aragorn         | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | bilbo           | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | boromir         | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | frodo           | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | galadriel       | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | gandalf         | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | gimli           | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | gollum          | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | legolas         | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | merry           | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | pippin          | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| %         | r_lotr_dba      |                                           |
| %         | r_lotr_dev      |                                           |
| %         | r_lotr_observer |                                           |
| %         | r_lotr_tester   |                                           |
| %         | sam             | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | mysql.sys       | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | root            | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
+-----------+-----------------+-------------------------------------------+

And even the roles_edges table does show a clear picture:

mysql [localhost] {root} (mysql) > select * from role_edges;
+-----------+-----------------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER       | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------------+---------+-----------+-------------------+
| %         | r_lotr_dba      | %       | galadriel | N                 |
| %         | r_lotr_dba      | %       | gandalf   | N                 |
| %         | r_lotr_dev      | %       | aragorn   | N                 |
| %         | r_lotr_dev      | %       | frodo     | N                 |
| %         | r_lotr_dev      | %       | legolas   | N                 |
| %         | r_lotr_observer | %       | boromir   | N                 |
| %         | r_lotr_observer | %       | gollum    | N                 |
| %         | r_lotr_observer | %       | merry     | N                 |
| %         | r_lotr_observer | %       | pippin    | N                 |
| %         | r_lotr_tester   | %       | bilbo     | N                 |
| %         | r_lotr_tester   | %       | gimli     | N                 |
| %         | r_lotr_tester   | %       | sam       | N                 |
+-----------+-----------------+---------+-----------+-------------------+

Here's a better use of that table's data. Which users are using the dev role?

select to_user as users from role_edges where from_user = 'r_lotr_dev';
+---------+
| users   |
+---------+
| aragorn |
| frodo   |
| legolas |
+---------+
3 rows in set (0.00 sec)

And the testers?

select to_user as users from role_edges where from_user = 'r_lotr_tester';
+-------+
| users |
+-------+
| bilbo |
| gimli |
| sam   |
+-------+
3 rows in set (0.00 sec)

Or, even better, we could see all the roles at once:

select from_user as role, count(*) as how_many_users, group_concat(to_user) as users from role_edges group by role;
+-----------------+----------------+-----------------------------+
| role            | how_many_users | users                       |
+-----------------+----------------+-----------------------------+
| r_lotr_dba      |              2 | galadriel,gandalf           |
| r_lotr_dev      |              3 | aragorn,frodo,legolas       |
| r_lotr_observer |              4 | boromir,gollum,merry,pippin |
| r_lotr_tester   |              3 | bilbo,gimli,sam             |
+-----------------+----------------+-----------------------------+
4 rows in set (0.01 sec)

Similarly, we could list the default role for several users:

select default_role_user as default_role, group_concat(user) as users from default_roles group by default_role;
+-----------------+-----------------------------+
| default_role    | users                       |
+-----------------+-----------------------------+
| r_lotr_dba      | galadriel,gandalf           |
| r_lotr_dev      | aragorn,frodo,legolas       |
| r_lotr_observer | boromir,gollum,merry,pippin |
| r_lotr_tester   | bilbo,gimli,sam             |
+-----------------+-----------------------------+

The latest two queries should be good candidates for information_schema views.

Another candidate for an information_schema view is the list of roles, for which there is no satisfactory workaround now. The best we could think of is a list of users with password_expired and account_locked:

select host, user from mysql.user where password_expired='y' and account_locked='y';
+------+-----------------+
| host | user            |
+------+-----------------+
| %    | r_lotr_dba      |
| %    | r_lotr_dev      |
| %    | r_lotr_observer |
| %    | r_lotr_tester   |
+------+-----------------+
4 rows in set (0.00 sec)

Until a feature to differentiate users and roles is developed, it is advisable to use a name format that helps identify roles without help from the server. In this article I am using r_ as a prefix, which makes role listing easier.

mysql [localhost] {root} (mysql) > select host, user from mysql.user where user like 'r_%';
+------+-----------------+
| host | user            |
+------+-----------------+
| %    | r_lotr_dba      |
| %    | r_lotr_dev      |
| %    | r_lotr_observer |
| %    | r_lotr_tester   |
+------+-----------------+
4 rows in set (0.00 sec)

Known bugs

• bug#85562 Dropping a role does not remove privileges from active users.
• bug#85561 Users can be assigned non-existing roles as default.
• bug#85559 Dropping a role does not remove the associated default roles.
• bug#84244 Distinguish roles and plain users in mysql.user.
• bug#82987 SHOW CREATE USER doesn't show default role.

Summing up

Roles are a great addition to the MySQL feature set. The usability could be improved with some views in information_schema (usersforrole, defaultroleforuser) and functions in sys schema (defaultroleforuser, is_role.)

Perhaps some commands to activate or deactivate roles could make administration easier. The current bugs don't affect the basic functionality but decrease usability. I think that another run of bug fixing and user feedback would significantly improve this feature.

More about roles in my talk at PerconaLive 2017.