MySQL Bugs: #98876: All privileges on a database for role doesn't allow user to create

Bug #98876	All privileges on a database for role doesn't allow user to create
Submitted:	9 Mar 2020 12:02	Modified:	25 Aug 2020 13:53
Reporter:	Michael Bausano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Roles	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	authorization

Description:
I use roles to give grants to my MySQL users. I noticed a strange behavior which I couldn't find documented anywhere when granting roles to users. Therefore I attribute that behavior to a bug.

When a user is granted all privileges upon a database, they can create the database. However when that same user is granted a role which has also all privileges upon a database, the user has no longer authority to create the database. Revoking the role or granting global privileges make allow the user to be able to create the database again (although neither are viable workarounds). I use a MySQL 8.0.18 Docker image.

How to repeat:
First we create an actual user joe@localhost and grant it all privileges on joe_db. While I omit the second step in my code, I use it here to illustrate how unexpected this behavior is. ($ mysql logs me in as root).

$ mysql
mysql> CREATE USER 'joe'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'joe'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON `joe_db`.* TO 'joe'@'localhost';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> exit
Bye

Now for the sake of eliminating other issues I log in as joe@localhost and assert that my grants indeed work.

$ mysql -ujoe -p1234 -hlocalhost
mysql> CREATE DATABASE `joe_db`;
Query OK, 1 row affected (0.01 sec)
mysql> DROP DATABASE `joe_db`;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

Now let's login as root again and create a new role joe_role. We grant the very same privileges to this role as we do on joe@localhost. Then we grant joe_role to joe.

$ mysql
mysql> CREATE ROLE 'joe_role';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON `joe_db`.* TO `joe_role`;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT `joe_role` TO `joe`@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

Let's login as joe again and activate the joe_role. Doing this will prevent us from creating a database. This behavior is what I question. Since I couldn't find it in the manref, I suspect it's a bug.

$ mysql -ujoe -p1234 -hlocalhost
mysql> SET ROLE `joe_role`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE `joe_db`;
ERROR 1044 (42000): Access denied for user 'joe'@'localhost' to database 'joe_db'

For the sake of eliminating other issues, let's see what happens when we set joe privileges to global.

mysql> exit
Bye
$ mysql
mysql> GRANT ALL ON *.* to `joe_role`;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
$ mysql -ujoe -p1234 -hlocalhost
mysql> CREATE DATABASE `joe_db`;
Query OK, 1 row affected (0.01 sec)

Hi Mr. Bausano,

Thank you for your bug report.

However, this is not a bug.

For the restrictions of the SET ROLE command, please read paragraph 6.2.5 of our manual.

Thank you for your response.

I have read the article again more closely and I really cannot find anything which would hit this behavior. Could you please point me to a specific paragraph which says it should work like that?

My reference when I work with roles are following articles:
- https://dev.mysql.com/doc/refman/8.0/en/role-names.html
- https://dev.mysql.com/doc/refman/8.0/en/roles.html
- https://dev.mysql.com/doc/refman/8.0/en/set-role.html

> Privileges that the user has been granted directly (rather than through roles) remain unaffected by changes to the active roles.

This sentence is not true as shown in my example. Also there's no mention about that granting all privileges on a database works differently for roles and for users. Rather it is being noted that grants for users and roles are interchangeable.

Hi Mr. Bausano,

Roles are working just in the way that they are designed.

If you think that this is not explained sufficiently, I can verify this report as a documentation bug.

Thank you for your quick response.

It would be great if the documentation mentioned that granting ALL privileges for a role doesn't actually grant ALL privileges, but just some. It sounds like a bug to me but if that's by design, my personal opinion is that the refman should have an enumeration of privileges which are assigned to a role on granting ALL privileges.

Just to be clear, I let me give you a brief example on what am I complaining about and what doesn't make sense to me:

mysql> show grants for 'joe_role';
+------------------------------------------------------+
| Grants for joe_role@%                                |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `joe_role`@`%`                 |
| GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe_role`@`%` |
+------------------------------------------------------+

mysql> show grants for `joe`;
+-------------------------------------------------+
| Grants for joe@%                                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `joe`@`%`                 |
| GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` |
| GRANT `joe_role`@`%` TO `joe`@`%`               |
+-------------------------------------------------+

---
and as `joe` user
---

mysql> set role `joe_role`;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for joe@%                                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `joe`@`%`                 |
| GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` |
| GRANT `joe_role`@`%` TO `joe`@`%`               |
+-------------------------------------------------+

mysql> create database `joe_db`;
ERROR 1044 (42000): Access denied for user 'joe'@'%' to database 'joe_db'

mysql> set role all except 'joe_role';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for joe@%                                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `joe`@`%`                 |
| GRANT ALL PRIVILEGES ON `joe_db`.* TO `joe`@`%` |
| GRANT `joe_role`@`%` TO `joe`@`%`               |
+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql> create database `joe_db`;
Query OK, 1 row affected (0.02 sec)

`joe_role` with the **very same** grants as the user, when assigned to the user, prevents them from creating database. This doesn't happen with global grants on the user or the role, neither with SELECT privileges on database. Only CREATE privileges exhibit this behavior. If this is by design, the documentation would benefit from list of all privileges which exhibit the same behavior as the CREATE privilege to unexpected errors.

Thank you for your time

Hi Mr. Bausano,

Thank you for your thorough description of the lack of the documentation on this issue.

Verified as a documentation bug.

Posted by developer:
 
Reclassifying as server issue. Kristofer wants to discuss with his group whether this should be happening.

Thank you , Paul.

It is, indeed, very difficult to decide whether this is in accordance with both SQL standard and with our own design.

Thank you for your care in investigating whether this is an expected behavior.

Thank you Mr. Bausano,

But this is, indeed, our job.

Posted by developer:
 
Fixed in 8.0.22.

Database-level privileges inherited through a role were not handled
properly for database names that contained wildcard characters.

Thank you, Paul.