Description:
https://dev.mysql.com/doc/refman/8.0/en/grant.html has the following description for the database name in grant statement:
"The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.*). This means, for example, that to use a _ character as part of a database name, specify it as \_ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON `foo\_bar`.* TO ...)."
When you start MySQL with default options the behavior is consistent with the documentation. However, when partial_revokes (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_partial_revoke...) is set to ON, the database name is treated literally instead of what the documentation suggests.
https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html mentions that "Partial revokes must name the schema literally. Schema names that contain the % or _ SQL wildcard characters (for example, myschema%) are not permitted." However, it does not mention that the same applies for the regular grants as well.
How to repeat:
Ensure you have the following in your MySQL server configuration under mysqld section:
partial-revokes=ON
Start MySQL server.
Connect with root or some other user with sufficient privileges and execute the following statements:
CREATE USER `testuser`@`%` IDENTIFIED WITH caching_sha2_password BY 'testpassword';
GRANT ALL PRIVILEGES ON `test_db_%`.* TO testuser;
CREATE DATABASE test_db_0;
FLUSH PRIVILEGES;
Try connecting to database test_db_0 as user 'testuser'. Observe it failing with error "Access denied for user 'testuser'@'%' to database 'test_db_0'".
Repeating the same steps with clean MySQL instance without having defined partial-revokes=ON results in successful connection to the database.
Suggested fix:
Either make wildcards for regular grants actually work as documented also when partial-revokes is enabled, or update the documentation at https://dev.mysql.com/doc/refman/8.0/en/grant.html to state that the database name is treated literally when partial-revokes is enabled.
