Using Partial Revokes in MySQL 8.0.16 to Revoke Granular Permissions

Background

MySQL permissions are granted at various levels (global, database, table, column). When a coarse‑grained permission is granted, finer checks are skipped, which can be inconvenient when you need to grant a permission to many databases except one.

Since MySQL 8.0.16, the Partial Revokes feature allows revoking a previously granted permission at a finer granularity.

Experiment

To use this feature the system variable partial_revokes must be set to ON; it is OFF by default, which leads to an error such as:

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

Enable the variable:

mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)

Grant the user scutech SELECT on all databases and then revoke the SELECT on the mysql system database:

mysql> grant select on *.* to scutech;
Query OK, 0 rows affected (0.04 sec)

mysql> revoke select on mysql.* from scutech;
Query OK, 0 rows affected (0.00 sec)

Verify the grants:

mysql> show grants for scutech;
+-----------------------------------------------+
| Grants for scutech@%                          |
+-----------------------------------------------+
| GRANT SELECT ON *.* TO `scutech`@`%`          |
| REVOKE SELECT ON `mysql`.* FROM `scutech`@`%` |
+-----------------------------------------------+

The mysql.user table now shows a Restrictions attribute for the user:

mysql> select User_attributes from mysql.user where user='scutech' and host='%';
+---------------------------------------------------------------------+
| User_attributes                                                     |
+---------------------------------------------------------------------+
| {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+---------------------------------------------------------------------+

Partial revokes can also be used to re‑grant a permission after it has been removed, for example:

mysql> grant SELECT ON `mysql`.* to scutech;
Query OK, 0 rows affected (0.01 sec)

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

Or you can revoke the coarse‑grained permission entirely, which makes the finer revocation unnecessary:

mysql> revoke SELECT ON *.* from scutech;
Query OK, 0 rows affected (0.01 sec)

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

Note that the USAGE privilege represents no privileges at all.