Description:
https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-quoting says:
"The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level."
So this means:
GRANT SELECT ON `t%`.* TO 'foobar'@'%'; -- Works
GRANT SELECT ON `test`.`foo%` TO 'foobar'@'%'; -- Does not work because it is a table level grant.
GRANT SELECT ON `t%`.`foobar` TO 'foobar'@'%'; -- Does not work because it is a table level grant.
So if there is a wildcard in the database name the table must be specified as '*'. However MySQL does accept grant statements for which this isn't the case.
How to repeat:
mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------------------+
| Grants for foobar@% |
+--------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foobar'@'%' |
| GRANT SELECT ON `test`.* TO 'foobar'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `t%`.`FooBar` TO 'foobar'@'%' |
+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE FooBar (id SERIAL PRIMARY KEY);
ERROR 1142 (42000): CREATE command denied to user 'foobar'@'localhost' for table 'FooBar'
Suggested fix:
Multiple possible solutions:
1. Don't accept a grant statement with a wildcard in the database where the table part is not '*'.
2. Accept statements which have a wildcard in the database name and a fixed table name.
3. Accept wildcards for table names and database names in any combination.
Related:
- Bug #81621 GRANT privileges on db vs db table - Wildcard characters in db name - Escaping
- Bug #78713 Allow wildcards in table names of GRANT statement
