MySQL Bugs: #103220: revoking partial privileges executes successfully while partial

Bug #103220	revoking partial privileges executes successfully while partial_revokes is OFF
Submitted:	6 Apr 2021 9:09	Modified:	6 Apr 2021 11:20
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Red Hat (rhel-7.4)
Assigned to:		CPU Architecture:	x86 (x86-64)

Description:
Hello,
  After setting partial_revokes as ON and executing a partial revocation, then I restart the server and I can see that config partial_revokes shows as OFF, but I can also execute partial revocation commands.
  Please reference to `How to repeat` for detail.

How to repeat:
[yxxdb_8022@localhost ~]$ mysql -uroot -S bin/mysql1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.22 Source distribution

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>
mysql>
mysql>
mysql> show variables like '%partial_revokes%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.04 sec)

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

mysql> GRANT SELECT, INSERT ON *.* TO u1;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql>
mysql> set @@global.partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE INSERT ON world.* FROM u1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> ^DBye
[yxxdb_8022@localhost ~]$ pkill mysqld
pkill: killing pid 18802 failed: Operation not permitted
pkill: killing pid 191816 failed: Operation not permitted
[yxxdb_8022@localhost ~]$ mysqld --defaults-file=~/etc/my.cnf &
[2] 19395
[1]   Done                    mysqld --defaults-file=~/etc/my.cnf
[yxxdb_8022@localhost ~]$
[yxxdb_8022@localhost ~]$ mysql -uroot -S bin/mysql1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 Source distribution

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 variales like '%partial_revokes%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'variales like '%partial_revokes%'' at line 1
mysql> show variables like '%partial_revokes%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.02 sec)

mysql> REVOKE SELECT ON world.* FROM u1;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> show grants for u1;
+--------------------------------------------------+
| Grants for u1@%                                  |
+--------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`          |
| REVOKE SELECT, INSERT ON `world`.* FROM `u1`@`%` |
+--------------------------------------------------+
2 rows in set (0.01 sec)

Hello Brian Yue,

Thank you for the report and feedback.
I quickly attempted to reproduce using the provided steps but confirmed that after restart partial_revokes is set to ON. 

-
rm -rf 103220/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/103220 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/103220 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/103220/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &
.
 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/103220 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/103220/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &
[1] 4976

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 variables like '%partial_revokes%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

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

mysql> GRANT SELECT, INSERT ON *.* TO u1;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql> show grants for u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> set @@global.partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE INSERT ON world.* FROM u1;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)

mysql> \q
Bye
kill -9 4976 

[1]+  Killed                  bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/103220 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/103220/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1

- started again

 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/103220 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/103220/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &

- confirmed that partial revoke is ON

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 variables like '%partial_revokes%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

In your test results I see your attempt to kill mysqld was failed:

pkill mysqld
pkill: killing pid 18802 failed: Operation not permitted
pkill: killing pid 191816 failed: Operation not permitted

Is there anything I'm missing here? Also, could you try to confirm this on 8.0.23? Thank you!

regards,
Umesh

Hello Umesh,
  Thanks for your work, I tried to repeat my operations on MySQL8.0.23, and find that this problem cannot be reproduced again, so I think this problem should have been fixed now.
  Now this bug is closed.

Thank you, Brian.