Permission oddity/issue Mysql 8
Hi y'all,
We've recently upgraded our DB to the Mysql 8 from Mysql 5.7. Version of Mysql 8 we're on is 8.0.32
I'm going to use a test user below but this test user mimics an actual user in our DB.
This user was created this way with these permissions in mysql 5.7:
CREATE USER 'test_jb'@'%' IDENTIFIED BY '{enter_password}';
GRANT ALL PRIVILEGES ON `phone_%prod%`.* TO 'test_jb'@'%';
GRANT SELECT, CREATE TEMPORARY TABLES ON `%prod`.* TO 'test_jb'@'%';
All was working fine and the user had "ALL PRIVILEGES" to phone_%prod%`.*.
So we upgraded to Mysql 8 and there the user looked the same:
Logged in as that user, test_jb.
mysql> show grants for current_user
-> ;
+---------------------------------------------------------------------+
| Grants for test_jb@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_jb`@`%` |
| GRANT ALL PRIVILEGES ON `phone_%prod%`.* TO `test_jb`@`%` |
| GRANT SELECT, CREATE TEMPORARY TABLES ON `%prod`.* TO `test_jb`@`%` |
+---------------------------------------------------------------------+
3 rows in set (0.06 sec)
User was able to do things like so:
mysql> create schema phone_api_prod;
Query OK, 1 row affected (0.06 sec)
mysql> CREATE TABLE phone_api_prod.test12 (id int);
Query OK, 0 rows affected (0.08 sec)
in mysql 8, just as they were able in mysql 5.7
I'm to drop those schema now so I can show running them again a bit later:
mysql> drop schema phone_api_prod;
Query OK, 0 rows affected (0.06 sec)
However, we added a role to this user and now its permission is denied for running the same statements as before. For the sake of simplicity, I'll show you a role that is created but the role will have no additional permissions (in real life this wouldn't be the case but this is just to keep it simple. The role we added actually had some additional/unrelated priviliges/permissions and showed the same behavior). The role will be granted to the same user.
So this is what we did:
mysql> CREATE ROLE `TESTTESTTEST`@'%';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT TESTTESTTEST TO 'test_jb'@'%';
Query OK, 0 rows affected (0.06 sec)
Then logging back in to test_jb user:
mysql> set role TESTTESTTEST
-> ;
Query OK, 0 rows affected (0.06 sec)
mysql> select current_role()
-> ;
+--------------------+
| current_role() |
+--------------------+
| `TESTTESTTEST`@`%` |
+--------------------+
1 row in set (0.06 sec)
mysql> show grants for current_user
-> ;
+---------------------------------------------------------------------+
| Grants for test_jb@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_jb`@`%` |
| GRANT SELECT, CREATE TEMPORARY TABLES ON `%prod`.* TO `test_jb`@`%` |
| GRANT ALL PRIVILEGES ON `phone_%prod%`.* TO `test_jb`@`%` |
| GRANT `TESTTESTTEST`@`%` TO `test_jb`@`%` |
+---------------------------------------------------------------------+
4 rows in set (0.06 sec)
And as mentioned, now when running just that create schema statement that earlier ran fine, now fails:
mysql> create schema phone_api_prod;
ERROR 1044 (42000): Access denied for user 'test_jb'@'%' to database 'phone_api_prod'
Now if I set my role to NONE, it works again:
mysql> set role NONE;
Query OK, 0 rows affected (0.05 sec)
mysql> create schema phone_api_prod;
Query OK, 1 row affected (0.06 sec)
mysql> CREATE TABLE phone_api_prod.test12 (id int);
Query OK, 0 rows affected (0.09 sec)
set the role back again and it stops working again:
mysql> set role TESTTESTTEST;
Query OK, 0 rows affected (0.06 sec)
mysql> show grants for current_user;
+---------------------------------------------------------------------+
| Grants for test_jb@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_jb`@`%` |
| GRANT SELECT, CREATE TEMPORARY TABLES ON `%prod`.* TO `test_jb`@`%` |
| GRANT ALL PRIVILEGES ON `phone_%prod%`.* TO `test_jb`@`%` |
| GRANT `TESTTESTTEST`@`%` TO `test_jb`@`%` |
+---------------------------------------------------------------------+
4 rows in set (0.06 sec)
mysql> CREATE TABLE phone_api_prod.test14 (id int);
ERROR 1142 (42000): CREATE command denied to user 'test_jb'@'ec2-34-197-131-140.compute-1.amazonaws.com' for table 'test14'
read access still works:
mysql> select * from phone_api_prod.test12
-> ;
Empty set (0.06 sec)
and the role we assigned doesn't have any additional permissions. All the permissions come from the GRANTS assigned at the user level. So it's getting its read access from the GRANTS assigned at the user level. It's just when assigning a role which is a feature of mysql 8, those previous "modify" scripts fail to behave as before.
Can someone point me in the Mysql Docs where this behavior is documented? Seems kind of strange.
Only thing remotely related that I could find in the mysql 8 docs is this (https://dev.mysql.com/doc/refman/8.0/en/grant.html#:~:text=Issuing%20multiple%20GRANT): "Issuing multiple GRANT statements containing wildcards may not have the expected effect on DML statements; when resolving grants involving wildcards, MySQL takes only the first matching grant into consideration. In other words, if a user has two database-level grants using wildcards that match the same database, the grant which was created first is applied." To be clear, this is the same behavior documented in mysql 5.7, and like I said, it works fine in mysql 5.7 and mysql 8. But stops working as expected once a role is granted and activiated on a user.
Is there a way to solve this, keeping to the permission strategy we have in place, or does mysql 8 with roles just break permission setup like this? Either way, where is this behavior documented in the mysql 8 docs, so I can understand better?
We use this strategy of permissions to give broad "read only and temporary table creation access to all %prod schemas" and elevated/ALL privileges permissions to a subset of schemas depending on the data subject area /data domain. It was a great strategy for us, but since we want to use roles in mysql 8 for additional simplicity of permission management, we're now coming across the inability to continue BAU permission strategy with role based permissioning.
Could use some help here solving this mystery, thanks!