Aurora 3 MySQL / The value of the `partial_revokes` parameter in the parameter group is different from the value of the same variable in MySQL

4

We are on the way to migration from Aurora 2 (2.10.1 - MySQL 5.7) to Aurora 3 (3.02.0 - MySQL 8.0). We started by testing migration in the dev environment and settled on the "restore from snapshot" option. A custom parameter group was created for the new cluster.

Our MySQL instance has a bunch of databases and users. After switching the application's connections to the new Aurora 3 cluster we discovered that several MySQL users are unable to connect to some databases, although there were no such issues with Aurora 2.

An investigation showed that the problem is in the way how Aurora 3 (MySQL 8) handles user access to the databases when it is granted using the wildcard character %. Our MySQL users are granted the database permissions as follows:

GRANT SELECT, INSERT, UPDATE, DELETE ON 'testdb%'.* TO 'testuser'@'%';

This is what the users are able to see in the new cluster:

SHOW DATABASES;
>>>
|Database                |
+------------------------+
|information_schema      |

and no one from the testdb_1 ... testdb_20.

Then we addressed the partial_revokes variable and got the following:

SHOW GLOBAL variables LIKE '%partial_revokes%';
>>>
|Variable_name  |Value |
+---------------+------+
|partial_revokes|ON    |

while the same parameter in the parameter group has a hardcoded value 0: Enter image description here

Even rebooting the instance didn't help MySQL's variable to get the value from the parameter group. So there is no other way for us than granting permissions to each database explicitly for each user. But this does not approach us because of the architectural specifics.


Finally, the situation with the parameter partial_revokes looks like a bug. Can we expect it to be resolved, and will it be allowed to be changed for Aurora users?

colden
asked 2 years ago677 views
3 Answers
2

Our upgrades by snapshot from aurora v2 to v3 are also on hold because of this. It seems the variable is stuck to enabled after the upgrade:

partial_revokes=ON

And I suspect this is (at least one of the causes) why we cannot properly grant and revoke permissions to our users.

On an aurora v3 cluster created from scratch, the variable is correctly disabled, as specified in the parameter group:

partial_revokes=OFF

dsech
answered 2 years ago
1

Update.

Granting with the escape character also does not work:

GRANT SELECT, INSERT, UPDATE, DELETE ON 'testdb\%'.* TO 'testuser'@'%';
colden
answered 2 years ago
0

I'm the reporter of this issue. Unfortunately, couldn't sign in with my old account anymore thus writing from the new one. Finally this issue seems to be fixed. Tested on both 'restore from snapshot' and 'inplace upgrade'. This question can be closed.

colden
answered a year ago

This post is closed: Adding new answers, comments, and votes is disabled.