Bug #90133 GRANT on particular table where database has an escape character is not allowed
Submitted: 20 Mar 2018 1:37 Modified: 22 Mar 2018 22:15
Reporter: Jaime Sicam Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.21 OS:Any
Assigned to: CPU Architecture:Any

[20 Mar 2018 1:37] Jaime Sicam
Description:
Based on the documentation, to not use _ as a wildcard, you'll need to prefix it with \:

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level. 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 ....

But if you do use backslash, you cannot specify a particular table from GRANT and you'll need to use * to specify all objects in the database.

Is this an expected behavior?

How to repeat:
mysql> USE test_1;
Database changed

mysql> CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER u1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> USE test2;
Database changed

mysql> CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON `test2`.`t1` TO u1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `test\_1`.`t1` TO u1;
ERROR 1146 (42S02): Table 'test\_1.t1' doesn't exist

mysql> GRANT SELECT ON `test\_1`.* TO u1;
Query OK, 0 rows affected (0.00 sec)
[22 Mar 2018 22:15] Bogdan Kecman
Hi,
can't be 100% what's "expected" but I can say that your observation is accurate and that wildcards _ and % are not working in the database name for GRANT while our documentation here: https://dev.mysql.com/doc/refman/5.7/en/grant.html
directly states that

[quote]
The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level. 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 ...
[/quote]

This is a duplicate of bug #88694 and bug #87420

all best
Bogdan