MySQL Bugs: #90134: Inconsistent behavior on granting privilege on wildcard database

Bug #90134	Inconsistent behavior on granting privilege on wildcard database
Submitted:	20 Mar 2018 2:26	Modified:	29 Jun 2018 18:57
Reporter:	Jaime Sicam	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.7.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Based on tests, GRANT using _ without escaping it on database and specifying a particular object on the table works as if _ was escaped. But https://dev.mysql.com/doc/refman/5.7/en/grant.html states 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"

Eg. 
GRANT SELECT ON `test_1`.`t1` TO u1;

mysql --port=5721 -uu1 -pu1 -e 'SELECT * FROM test_1.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --port=5721 -uu1 -pu1 -e 'SELECT * FROM test11.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1142 (42000) at line 1: SELECT command denied to user 'u1'@'localhost' for table 't1'

However, if you specify all objects(*) in GRANT, _ works as a wildcard as specified in the documentation.
Eg.
GRANT SELECT ON `test_1`.* TO u2;
mysql --port=5721 -uu2 -pu2 -e 'SELECT * FROM test_1.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --port=5721 -uu2 -pu2 -e 'SELECT * FROM test11.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

How to repeat:
To test, run this in MySQL console:

CREATE DATABASE test_1;
USE test_1;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);

CREATE DATABASE test11;
USE test11;
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);

CREATE USER u1 IDENTIFIED by 'u1';
GRANT SELECT ON `test_1`.`t1` TO u1;
FLUSH PRIVILEGES;

CREATE USER u2 IDENTIFIED by 'u2';
GRANT SELECT ON `test_1`.* TO u2;
FLUSH PRIVILEGES;

Run from shell:

mysql --port=5721 -uu1 -pu1 -e 'SELECT * FROM test_1.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --port=5721 -uu1 -pu1 -e 'SELECT * FROM test11.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1142 (42000) at line 1: SELECT command denied to user 'u1'@'localhost' for table 't1'

mysql --port=5721 -uu2 -pu2 -e 'SELECT * FROM test_1.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --port=5721 -uu2 -pu2 -e 'SELECT * FROM test11.t1';
mysql: [Warning] Using a password on the command line interface can be insecure.

Hi,

This behaviour is as expected, but our documentation requires additional explanations and elucidations.

Verified as the documentation bug.

Hi,

This turned out to be a real bug, a bug in the code, and not a documentation bug.

Actual behavior is as documented, but the description in the bug report
omitted part of the documentation:

Based on tests, GRANT using _ without escaping it on database and specifying
a particular object on the table works as if _ was escaped. But
https://dev.mysql.com/doc/refman/5.7/en/grant.html states 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"

The documentation states:

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

Note that first sentence, which states that wildcards are permitted only at
the database level (ON db_name.*). If the database name is used as a
qualifier for other objects such as tables or routines, wildcard characters
are treated as normal characters.