MySQL 8 Couldn't execute FLUSH TABLES Access denied

Question

I have MySQL 8 database with a user to whom I grant ALL privileges as follows:

GRANT ALL ON test_db.* TO 'test_user'@'%';

When I login to mysql as this user and run the following command I get error saying ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation

FLUSH TABLES;

I thought the GRANT ALL permission would have already given this user the RELOAD or FLUSH_TABLES privilege(s)?

How do I fix?

Answer 1

Global Privileges

Global privileges are administrative or apply to all databases on a given server. To assign global privileges, use ON . syntax.

The CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER static privileges are administrative and can only be granted globally.

RELOAD

The RELOAD enables the following operations:

• Use of the FLUSH statement.
• etc

Conclusion , you need the reload privilege which can be given :

GRANT RELOAD ON *.* TO 'test_user'@'%';

• adam78: so what you are saying is I'd have to give it as a global privilege as opposed to being able to restrict to a specific database?
• Ergest Basha: @adam78 yes, global privileges apply to all databases, you can't restrict to only one specific db.