Sunday, September 24, 2017

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.

10 comments:

Entry 23 Eco of Kristofer said...

A sophism is loosely translated as "a clever but false argument, especially one used deliberately to deceive". I naturally disagree that I used a false argument and I also disagree that I deliberately tried to deceive you. I do respect your very strong opinion on this matter and I'm willing to reconsider if you could better help me to realize the practice complications which makes this bug critical for you. Please note that relaxing constrains sometimes aids the overall goal of simplicity. You seem concerned that you're likely to make a severe mistake because this constraint isn't in place. Can you elaborate more on this please?

Giuseppe Maxia said...

@Kristofer,
Thanks for your comments.
With the current code, when I assign a default role that does not exist, I don't get any error or warning. If my purpose was to enable a user to start working with a given set of privileges, I will consider the lack of errors as correct assignment.
The problem will be felt only when the user starts operations, and by then we will have errors that need to be addressed and operations that can't continue. We may think of "users" as humans who will realize that they lack the proper privileges and take action to activate the proper role. But more often in modern operations a "user" is just an account for an automated process in an application server or in a nighttime cron job.
Here, the lack of error during the assignment will result in lost minutes or hours during operations.
When I try to assign a non-existing role to a user, i get a rightful error. I would like to have the same loud error when I try to assign a non-existing default role to an user.

Entry 23 Eco of Kristofer said...

@giuseppe Ok, I see that you write that this is a significant inconvenience that can cost time and cause confusion. I'll reopen the bug and see if I can address it somehow.

Simon J Mudd said...

A couple of related comments to roles:
* Someone said to me the other day "MySQL will implement SET ROLE". I think that misses the point for those of us who have a large number of application users, especially if they use a pooled connection to the backend database. They will NEVER use that command (unless the pool connector knows what this means and which connected has which role). All the app cares about is that it has the privileges it needs to do its job. If that can be more easily defined in terms of "role access" (several apps may share similar permissions) then using roles is cleaner than providing individual grants to each user. I think this will simplify grant management and is welcome. While I do also manage "user" accounts where I could see SET ROLE being potentially useful this is really nothing more than unix's sudo command, and having to explain to the user to use it requires more effort. They might wonder why I didn't just give them the rights directly in the first place.
* Changing grants is a problem these days. You can run the GRANT statement but unless I'm mistaken active connections will not notice. The solution has traditionally to do FLUSH PRIVILEGES but that happens globally and so may have more impact so I'd love to see something like FLUSH PRIVILEGES FOR 'user'@'domain' or FLUSH PRIVILEGES FOR 'user'@'domain' NOW to just update the specific grants for the specific MySQL user. Change management would probably prefer that as any changes can be suffixed with such a command and you'd know that all users now are applying the grants whether new connections or not.
* Looking at https://dev.mysql.com/doc/refman/8.0/en/grant.html I don't see any more fine grained privileges than was present in 5.7. I'd like to see privileges for STOP SLAVE/START SLAVE (but perhaps a different one for CHANGE MASTER TO) as some replication related activities require REPLICATION CLIENT, REPLICATION SLAVE or SUPER and these grants include more than a single command and some commands I might give to a user and others I might prefer not to. Finer level grants are still not present yet and would be good to add.

Giuseppe Maxia said...

@Simon,

* The remedy for SET ROLE problem is in the auto activation of roles (see above in the article);

* Changing grants is an issue that has been reported already (See Bug#85562, but I guess that it will require some global refactoring of the privilege system to be properly addressed with either traditional privileges or roles. In my tests, FLUSH PRIVILEGES has no effect on running sessions. Even a global change will only affect the user after a reconnect.

* I haven't seen any increase in granularity for replication related privileges. Is there a feature request for that?

Entry 23 Eco of Kristofer said...

@simon, @giuseppe Please note that you now also can set the DEFAULT ROLE when you create the user. When a user has a default role, he won't need to concern himself with SET ROLE - default roles are activated by default if the user also have the appropriate GRANT.

As for privilege caching, we're trying to minimize the performance overhead of security and in doing so we must make some compromises. I think it should be possible to create an enforced sync point for a particular user, but the privilege object maps are stored in global containers and must be locked for writing. Changing the privilege object maps will of course always invalidate the role aggregates so they need to be recalculated too. We make no effort to calculate the optimal recalculation plan for roles instead we just recreate all of them when a GRANT is issued.

It would be great if you could write down the use case that concerns you as a feature request on bugs.mysql.com.

We've introduced quite a few new privileges primarily with the intent to offer a more fine grained alternative to the somewhat peculiar privilege SUPER (which isn't really a super privilege). Example: 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

If there are some missing here you should definitely toss in a feature request as it's now super easy to add new privilege objects.

Giuseppe Maxia said...

@Kristofer,
When I gave my feedback to the MySQL team back in April I explained that SET ROLE (including SET DEFAULT ROLE) is a break of users expectations. Until now, when a new privilege was needed, the DBAs had only to run a GRANT for that privilege, and the job was done. The necessity of having a SET ROLE and SET DEFAULT ROLE means that we're taking the workload backwards.

The ideal workload is be that a "GRANT rolename to username" would work like "GRANT SELECT, INSERT ON someobject TO username" (with no SET ROLE needed.) Having a disabled role should be the exception, not the rule.

We should have something like "GRANT rolename DISABLED TO username" for the cases when DBAs need to give roles that are not active immediately.

Entry 23 Eco of Kristofer said...

@giuseppe Yes, and I see your point very well. It has been discussed to some length but out conclusion after consolidating multiple views is that the current pattern, although it has it's downsides, is a flexible all around solution.

What we can do though is to make a "GRANT r1 TO giuseppe@localhost" statement automatically set r1 the default role for giuseppe@localhost unless and exception clause is added to GRANT. Example: "GRANT r1 TO giuseppe@localhost NOT DEFAULT".

I believe this will simplify the workflow and sufficiently mimic what you are asking for. How does that sound to you?

Giuseppe Maxia said...

@Kristofer,
Yes, thanks. If the result is that a role assignment becomes effective immediately, I don't care much about the syntax.

Simon J Mudd said...

Giuseppe:

* Thanks for the link to Bug#85562. Subscribed.
* I've talked to people about this (FR for splitting replication related privileges) but not made a FR. I already make too many and some are still open after several years. I can add such a FR if others think this would be useful.

Kristofer:

* "I think it should be possible to create an enforced sync point for a particular user": this would be useful and perhaps to be honest it wouldn't be used frequently but given the bug referenced above for some cases this would be useful.
* I'll also have to come up with a use case but just consider applications that run for a long time and need after connecting to have access to objects that weren't configured previously. Having a way to allow this would be good. right now there's no way to monitor if the user that's connected actually has the same grants as indicated by SHOW GRANTS FOR 'user'@'host' and I'm not sure if there's an indication of when the grants were last changed which might at least give you a reference point. Right now the typical solution is to kill connections and let them come back. That works but of course will generate client errors.
* SUPER isn't a privilege? Well I think it is. With it you can do all sorts of exciting things, especially with replication. So I think that it would be good to decompose all of the individual things that you can do with super into a role called super and contemplate the migration to use the super role rather than GRANT SUPER on *.* TO xxxx. This would allow me to enable devs or applications to stop or start replication but not to change the master or wipe out the bin logs or relay logs which I'd prefer to be left to those that manage the mysql servers. So a stop_start_slave type privilege, a change_master type privilege, and clearly reset_slave and reset_master type privileges fall into those I'd like to see.