Bug #88694 MySQL accepts wildcard for database name for table level grant but won't use it
Submitted: 29 Nov 2017 10:51 Modified: 30 Nov 2017 12:13
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[29 Nov 2017 10:51] Daniël van Eeden
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
[30 Nov 2017 12:13] Umesh Shastry
Hello Daniël,

Thank you for the report.

Thanks,
Umesh