MySQL8.0用户创建、授权以及撤销授权报错ERROR 1141 (42000): There is no such grant defined for user

我这里mysql版本是8.0.23

[root@localhost ~]# mysqld -V
/usr/sbin/mysqld  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

创建一个远程用户并全局授权：

mysql> create user 'jjb'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.03 sec)

mysql> grant all privileges on *.* to 'jjb'@'%' with grant option;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

我想撤销该用户的部分数据库权限，出现报错：

mysql> revoke all on mysql.* from jjb@'%';
ERROR 1141 (42000): There is no such grant defined for user 'jjb' on host '%'

查阅资料需配置开启partial_revokes=ON：

mysql> show variables like '%revoke%'
    -> ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

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

mysql> 
mysql> show variables like '%revoke%'
    -> ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | ON    |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> revoke all on mysql.* from jjb@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 

注意，开启后如果使用了全局部分权限撤销功能，该参数不能设置为OFF，如需改回OFF可以先恢复全局权限：

mysql> set global partial_revokes=OFF;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.
mysql> 
mysql> grant all on mysql.* to jjb@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)