Introduction

MySQL has support for the RELOAD privilege. Now, imagine a database user who has been granted the RELOAD privilege, which allows this user to execute the FLUSH PRIVILEGES statement on the system. Suppose this user accidentally executes another powerful statement like RESET or FLUSH TABLES. The consequences could lead to some problems, right?

Consider a database administrator who creates a user with the intention of allowing the user to execute only the FLUSH PRIVILEGES statement. The administrator then grants the RELOAD privilege for this purpose, only to realize that it also grants the user the ability to execute many powerful operations such as:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> RESET BINARY LOGS AND GTIDS;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.01 sec)

The problem with the RELOAD privilege in MySQL versions up to and including MySQL 8.0 is that it permits users to execute a wide range of powerful statements on the server, including any type of FLUSH or RESET command. This prevents administrators from having a more granular control on who executes specific statements.

To tackle this problem, we have introduced a new privilege: FLUSH_PRIVILEGES in MySQL 8.4 Community and Enterprise Editions, and HeatWave. When this privilege is granted, it allows the user to execute the FLUSH PRIVILEGES statement without needing the RELOAD privilege, thus providing a more granular level of control over the execution of the FLUSH PRIVILEGES statement, which is a crucial operation for refreshing in-memory privileges after making changes to the MySQL privilege tables.

Usage

Let us take a look at how this new privilege can be used in MySQL 8.4:

Connect as root, create a test user, and assign this user the FLUSH_PRIVILEGES privilege instead of RELOAD, like this:

mysql> CREATE USER test@localhost;
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT FLUSH_PRIVILEGES ON *.* TO test@localhost;
Query OK, 0 rows affected (0.01 sec)

The test user can execute the FLUSH PRIVILEGES statement now without any problems, but is not allowed to execute the other FLUSH or RESET statements. You can see this here:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> RESET BINARY LOGS AND GTIDS;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation

mysql> FLUSH TABLES;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation

Additional Information

FLUSH_PRIVILEGES can be used along with roles.

To see this in action, create a role named privs_flush and assign the FLUSH_PRIVILEGES privilege to this role. Then assign this role to another test user 'test2'; this allows user test2 to execute the FLUSH PRIVILEGES statement. You can do this by executing the statements shown here:

mysql> CREATE ROLE privs_flush;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT FLUSH_PRIVILEGES ON *.* TO privs_flush;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER test2@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT privs_flush TO test2@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR test2@localhost;
+------------------------------------------------+
| Grants for test2@localhost                     |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`localhost`      |
| GRANT `privs_flush`@`%` TO `test2`@`localhost` |
+------------------------------------------------+
2 rows in set (0.00 sec)

After connecting as the test2 user, you can see how this role affects the user's privileges:

mysql> FLUSH PRIVILEGES;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_PRIVILEGES privilege(s) for this operation

mysql> SET ROLE privs_flush;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation

Note that the root account has the FLUSH_PRIVILEGES privilege by default.

Upgrade/Downgrade Considerations

To help ensure a smooth transition during upgrade, users having the RELOAD privilege are granted the new privilege automatically upon upgrade.

If the MySQL server is downgraded, then we have two possible scenarios:

1. The downgraded server version does not support the FLUSH_PRIVILEGES privilege:
   In this case the privilege has no effect, that is, the FLUSH PRIVILEGES statement cannot be executed unless the user has the RELOAD privilege. Following the downgrade, if there are FLUSH_PRIVILEGES grants present, FLUSH_PRIVILEGES can still be granted or revoked but does not have any effect. Once the last grant is revoked, and the server is restarted, granting FLUSH_PRIVILEGES is no longer possible.
2. The downgraded server version does support the FLUSH_PRIVILEGES privilege:
   Users with the FLUSH_PRIVILEGES privilege continue to have the ability to execute the FLUSH PRIVILEGES statement. It is still possible to grant or revoke FLUSH_PRIVILEGES using GRANT or REVOKE.

Conclusion

The new privilege provides administrators greater control over user permissions and so addresses the need for a more granular access control over the FLUSH PRIVILEGES statement. Look for FLUSH_PRIVILEGES in MySQL 8.4 Community and Enterprise Editions, and HeatWave.

As always, a big thank you for using MySQL!