MySQL Bugs: #98332: partial_revokes=1 disables wildcard database grants

Bug #98332	partial_revokes=1 disables wildcard database grants
Submitted:	23 Jan 2020 7:12	Modified:	24 Jan 2020 14:58
Reporter:	Nikolai Ikhalainen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

Grants are not provided for user with wildcard permissions for multiple databases if partial revokes are enabled.

How to repeat:
docker run --name m8019 -d -e MYSQL_ROOT_PASSWORD=secret mysql/mysql-server:8.0.19 --partial_revokes=1
docker exec -it m8019 mysql -uroot -psecret -e "create database test1;create database test2;create user 'test'@'localhost' identified by 'secret';GRANT SELECT ON \`test%\`.* TO test@localhost;"
$ docker exec -it m8019 mysql -utest -psecret -e "SHOW DATABASES;SHOW GRANTS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
+-------------------------------------------------+
| Grants for test@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost`        |
| GRANT SELECT ON `test%`.* TO `test`@`localhost` |
+-------------------------------------------------+

docker rm -f m8019

docker run --name m8019 -d -e MYSQL_ROOT_PASSWORD=secret mysql/mysql-server:8.0 --partial_revokes=0

$ docker exec -it m8019 mysql -uroot -psecret
create database test1;
create database test2;
create user 'test'@'localhost' identified by 'secret';
GRANT SELECT ON `test%`.* TO test@localhost;

$ docker exec -it m8019 mysql -utest -psecret
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> show grants;
+-------------------------------------------------+
| Grants for test@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost`        |
| GRANT SELECT ON `test%`.* TO `test`@`localhost` |
+-------------------------------------------------+

$ docker run --name m8019 -d -e MYSQL_ROOT_PASSWORD=secret mysql/mysql-server:8.0.19 --partial_revokes=0
$ docker exec -it m8019 mysql -uroot -psecret -e "create database test1;create database test2;create user 'test'@'localhost' identified by 'secret';GRANT SELECT ON \`test%\`.* TO test@localhost;"
$ docker exec -it m8019 mysql -utest -psecret -e "SHOW DATABASES;SHOW GRANTS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test1              |
| test2              |
+--------------------+
+-------------------------------------------------+
| Grants for test@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost`        |
| GRANT SELECT ON `test%`.* TO `test`@`localhost` |
+-------------------------------------------------+

Hello Nikolai,

Thank you for the report and feedback.
Verified as described with 8.0.19 build.

Thanks,
Umesh

As per manual -  https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html 

Partial revokes do not treat the wild card specially. That means user can create the partial revokes on database names that has wild cards in them. Partial revokes don't give special treatment to  wild cards.

Hi Rahul,

Just to be sure, there is no REVOKE statements in the test case, but partial_revokes = ON changes behavior for simple GRANT SELECT 'test%'.* handling.

Bug #99431 marked as duplicate of this one

Hello Nikolai,

> Just to be sure, there is no REVOKE statements in the test case, but partial_revokes = ON changes behavior for simple GRANT SELECT 'test%'.* handling.

Sorry, I might have missed to read your comment. I am responding 6 years later when I am no longer part of MySQL team at Oracle. 
If Partial revokes is ON then Grant statement also aggregate the privileges to be shown to the user. Therefore, REVOKES statements presence is not required. 

Anyways, as per the manual using wildcard is deprecated in the later releases.

> The use of the wildcard characters % and _ as described in the next few paragraphs is deprecated as of MySQL 8.0.35 and thus subject to removal in a future version of MySQL.
 
It is not a good idea from security reasons to grant privileges using wild card, it may lead to privilege escalation if not done carefully. Hope this  deprecated feature will be removed in the future MYSQL releases.