MySQL: When is Flush Privileges in MySQL really needed?

Question

When creating new tables and a user to go along with it, I usually just invoke the following commands:

CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY "mypassword";

I have never ever needed to utilize the FLUSH PRIVILEGES command after issuing the previous two commands. Users can log in and use their database and run PHP scripts which connect to the database just fine. Yet I see this command used in almost every tutorial I look at.

When is the FLUSH PRIVILEGES command really needed and when is it unnecessary?

Demetri

It's a good thing to try when stuff doesn't work, but I haven't seen it as a necessary thing since at least MySQL 5.0. – Lewan

mytable.* uses mytable as a database name, not a table name. – Whilom

@Whilom Thankyou, I mistyped my code. It is fixed now. – Demetri

Answer 1

Privileges assigned through GRANT option do not need FLUSH PRIVILEGES to take effect - MySQL server will notice these changes and reload the grant tables immediately.

From MySQL documentation:

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Seay

Thanks, though I wonder why so many tutorials specifically say you need to use flush privileges after a GRANT command. – Demetri

Can you please advise on the command to use, when i write flush privileges nothing happens – Debase

@Demetri I don't have any hard facts backing this, but I believe that back in the 3.23:ish days of MySQL, it was in fact necessary. So that's probably why it's left in the documentation as a remnant of "how it used to be". – Chili

Answer 2

TL;DR

You should use FLUSH PRIVILEGES; only if you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE.

Ontiveros

Nice and concise. – Penetrance

Answer 3

Just to give some examples. Let's say you modify the password for an user called 'alex'. You can modify this password in several ways. For instance:

mysql> update* user set password=PASSWORD('test!23') where user='alex'; 
mysql> flush privileges;

Here you used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables.

Or you can modify the password like this:

mysql> set password for 'alex'@'localhost'= password('test!24');

Here it's not necesary to use "FLUSH PRIVILEGES;" If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Aulos

Thank you for a concrete example. – Demetri

Answer 4

2 points in addition to all other good answers:

1:

what are the Grant Tables?

from dev.mysql.com

The MySQL system database includes several grant tables that contain information about user accounts and the privileges held by them.

clarification: in MySQL, there are some inbuilt databases , one of them is "mysql" , all the tables on "mysql" database have been called as grant tables

2:

note that if you perform:

UPDATE a_grant_table SET password=PASSWORD('1234') WHERE test_col = 'test_val';

and refresh phpMyAdmin , you'll realize that your password has been changed on that table but even now if you perform:

mysql -u someuser -p

your access will be denied by your new password until you perform :

FLUSH PRIVILEGES;

Rummer

Answer 5

FLUSH PRIVILEGES can reflect the modifications of the grant tables in mysql database(schema) to your MySQL. *When you directly modify the grant tables, the modifications are not automatically reflected to your MySQL so you need to run FLUSH PRIVILEGES or restart your MySQL manually and by default, MySQL has mysql database(schema) which is a system database.

These are the grant tables in mysql database(schema) as shown below:

user
global_grants
db
tables_priv
columns_priv
procs_priv
proxies_priv
default_roles
role_edges
password_history

So for example, you need to run FLUSH PRIVILEGES after changing the username root to john in user table of mysql database(schema) as shown below:

UPDATE mysql.user SET User='john' WHERE User='root';
FLUSH PRIVILEGES;

But, if you forget to run FLUSH PRIVILEGES after changing the username root to john in user table of mysql database(schema) as shown below:

UPDATE mysql.user SET User='john' WHERE User='root';

Then, you still cannot log in to MySQL with the username john as shown below:

mysql -u john -p

So, you still need to log in to MySQL with the username root as shown below. *Even if you forgot to run FLUSH PRIVILEGES, you can still run FLUSH PRIVILEGES to reflect the modification to your MySQL by login with the username root:

mysql -u root -p

Actually, I recommend to use RENAME USER ... as shown below to change the username root to john because directly modifying the grant tables is sometimes problematic:

RENAME USER 'root'@'localhost' to 'john'@'localhost';

Bordy