Description:
Hi,
I initially posted this odd behavior / issue via MySQL Forum posting: https://forums.mysql.com/read.php?20,709250,709250. I got a response there that said, "This is not an expected behavior. Enabling role(s) in a session should not cause such an issue" (https://forums.mysql.com/read.php?20,709250,709397#msg-709397) and to file a bug via https://bugs.mysql.com/. This is that bug report!
What was done:
We recently upgraded our DB from MySQL 5.7.x to MySQL 8.0.32. Post upgrade, roles were attached to those DB users. That's why this issue was found.
What was expected:
Since those roles had permissions that were completely unrelated to the permissions set at the DB user level, attaching those roles to the existing DB users and activating those roles on the user would not break existing permissions set at the DB user level. To be clear, the DB role attached to the DB user does not have any conflicting / related permissions to what is already set at the DB user level, so we expected attaching the DB role would have no ill effect.
What actually happened:
After roles were attached to DB user and activated on the DB user, our existing permissions to certain operational db users broke. What we found is that if any role, even if the role has no permissions (we tried that just for kicks), is attached/activated on those applicable db users, those DB users permissions directly assigned to the DB user no longer work as expected. Existing permissions that worked fine on MySQL 5.7.x broke on MySQL 8.0.32.
Some more notes: We have set activate_all_roles_on_login variable set to true on the server and want to keep this on for our DB. If all the roles are either removed from the DB user or not set/activated on the user, then the existing permissions on the DB user work on MySQL 8.x, just like they do when those same DB permissions on those DB users are set in MySQL 5.7.x. For whatever reason, adding and activating any role, even if unrelated or that role having no additional permissions, to the DB users causes the existing permissions set on the DB user to no longer work.
How to repeat:
You can also look at the above referenced forum posting for more details on how to replicate this problem. This was run in MySQL 8.0.32.
Here's short and sweet:
Ran this as root user:
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'@'%';
One logs in as test_jb and then runs:
create schema phone_api_prod;
CREATE TABLE phone_api_prod.test12 (id int);
drop schema phone_api_prod;
All works fine. I could do inserts, updates, deletes from that user in phone_api_prod.test12 and it would work fine too! Works fine in MySQL 8.0.32 just like Mysql 5.7.x before we upgraded to MySQL 8.
But if I add a role to that user via root user:
CREATE ROLE `TESTTESTTEST`@'%';
GRANT TESTTESTTEST TO 'test_jb'@'%';
Notice that role has no permissions added to it (just for simplicity doing that. obviously not real world):
Then logging back in to test_jb user, I activate that role on the user:
set role TESTTESTTEST;
And I try and run the same commands as before. Let's just do the first command for simplicity (see forum posting for details and attached files I'll later send for more examples):
create schema phone_api_prod;
ERROR 1044 (42000): Access denied for user 'test_jb'@'%' to database 'phone_api_prod'
It now fails!
Suggested fix:
When adding a role, it should not cause that unexpected behavior.
