What is the Use of FLUSH PRIVILEGES Statement in MySQL?

One of the key features of MySQL is the ability to manage users and their permissions.

To maintain the security of the database, different sets of permissions are given to different users so that no unwanted changes can be made to the database. These user-specific privileges are called user privileges. At the top of the chain is the root user which has all the privileges, it can make any changes on the database so any privileges that are given to or revoked for a particular user are done by the root user. Permissions for various important database operations such as UPDATE, DELETE, INSERT, SELECT, and CREATE can be given to a user by granting permission and removed by revoking the permission. Once the permissions for a user have been modified, there are 2 ways to make the changes effective. One is to restart the whole database server, this will apply the new set of privileges on restart. The other way is to use Flush Privileges.

The Flush Privileges statement is an important command in MySQL that is used to refresh the user privileges. In this article, we will explore the use of the Flush Privileges statement in MySQL.

So without wasting any more time, let us get started.

Working with MySQL Flush Privileges

Whenever a new user is created or their permissions are updated, MySQL stores this information in the database's memory, specifically a collection of system tables known as grant tables that stores data about user accounts, their associated privileges, and other relevant information. These tables are part of the MySQL data dictionary and play an important role in managing user authentication and access to the MySQL server.

MySQL has five main grant tables:

1. user: This table holds data about user accounts, such as authentication credentials (username, password), global privileges, and other account settings.

2. db: This table maintains database-level privileges for user accounts, specifying which users possess particular privileges on specific databases.

3. tables_priv: This table stores table-level privileges for user accounts, indicating which users have certain privileges on specific tables within a database.

4. columns_priv: This table handles column-level privileges for user accounts, determining which users have particular privileges on specific columns within a table.

5. procs_priv: This table keeps track of privileges associated with stored procedures and functions.

When a user attempts to connect to a MySQL database or a server, the server verifies the user's privileges using the information stored in the grant tables. Now if the user tries to make any changes to the system, it first checks if the user has the necessary permissions to perform the requested action. If the user is found to not have the required permissions, the system denies access to the requested resource. The user can request the admin or the root user to get access to the required permissions. Then, the admin can alter the grant tables using SQL commands such as GRANT, REVOKE, and ALTER USER to manage user permissions.

This is where the Flush Privileges statement comes in. It is used to refresh the user privileges so that any changes made to the user accounts are applied to the system.

The Flush Privileges statement reloads the grant tables' privileges, ensuring that any changes made to user permissions are immediately applied without requiring a restart of the MySQL server.

Effects of Using Flush Privileges

Let us look at some of the effects that it has on your MySQL database server.

Immediate Application of Permission Changes

One of the main effects of using the Flush Privileges statement is the immediate application of any permission changes. When a user's permissions are modified, the Flush Privileges statement forces the server to reload the grant tables, ensuring that the changes are applied immediately. This is particularly useful when creating new users, updating existing user permissions, or changing user passwords.

Clearing Cache

When MySQL loads the grant tables, it stores the information in the cache for faster access. When the MySQL Flush Privileges statement is executed, it clears the cache, forcing MySQL to reload the grant tables from the disk.

Maintaining Database Security

Using the Flush Privileges statement helps maintain the security and integrity of the database by making sure that users have the correct permissions. When user permissions are updated, it is crucial to apply these changes as soon as possible to prevent unauthorized access or data manipulation.

Enhancing Database Performance

Another effect of using the Flush Privileges statement is the potential enhancement of database performance. By ensuring that user permissions are up-to-date, the MySQL server can efficiently manage user access and data manipulation.

Simplifying Database Administration

The Flush Privileges statement simplifies database administration by allowing administrators to apply permission changes without restarting the MySQL server.

Syntax

The Syntax for the MySQL FLUSH PRIVILAGES command is quite simple and is given below. As discussed above, the grant table will be reloaded once you execute the below-given command.

This is a simple statement that does not require any parameters or arguments. It is used to refresh the user privileges and reload the privilege tables.

Uses of MySQL Flush Privileges

Now let us see how we can use the MySQL Flush Privileges commands with step-by-step explanations. We will create a new user to see the uses of these commands.

Connect with the MySQL Server

Firstly, we will open a new terminal or command prompt and use the following command to connect to the MySQL server. You can replace the 'username' with your MySQL username and 'localhost' with your server's address if it's not on the same machine.

You will need to enter your MySQL id or password if prompted.

Create a New User

First, we need to make sure that we connect to the MySQL server. After that, we can run the command given below to create a new user. Replace 'new_user', 'localhost', and 'new_password' with the desired username, host, and password, respectively.

We can create a sample used by the name of mailk by using the following code.

Whenever we create a new user in MySQL some default permissions are set for that user which are stored in the user table that we discussed above. This MySQL user table contains all the permissions-related data for a particular user. Let us look at how this table looks with the help of an example.

We can generate the user table by running the following command.

Output:

Only a tiny part of the output is shown here to give you the idea. As you can see in the table, no permissions are granted for our sample user.

Update the User’s Permission

In MySQL, permissions for specific users can be set using the UPDATE command or the GRANT command. The only difference between the two is that if the UPDATE command is used to change the permission, the Flush Privileges command is required to make the changes effective, on the other hand, if the GRANT statement is used to make the changes, Flush Privileges command is not required to make the changes effective.

Let us look at a sample query where we are changing the Select_priv for your sample user.

After this, we need to make sure that the changes are effective and since we are using the UPDATE command to make the changes, we will need to use the Flush Privileges command.

Now let us see, what the new permissions for our sample user look like.

Output:

As you can see, the Select_priv section has been changed to Y, which stands for yes and all the other fields are N, which stands for no permission.

Now, we will use the GRANT command to set the INSERT and DELETE permissions for our sample user, malik and as discussed above, you don’t need to use the Flush Privileges command for the changes to take effect.

We will again run the previous select command to see if the insert and delete privileges have been assigned to our user.

Output:

As you can see, our sample user has been granted the Insert and Delete privileges by using the GRANT command without using the Flush Privileges command.

Change the User’s Password

SET, ALTER, and UPDATE statements are some of the many commands that exist in MySQL to change the user’s password. As we saw in the previous section as well, The FLUSH PRIVILEGE command is required use if you change the password by using the UPDATE query, and the FLUSH PRIVILEGE command is not required if the password is changed by SET or ALTER commands. We have shown below how you can change the password using the UPDATE command as well as the ALTER command.

First, we will run the below-provided command to get the authentivation_string for our sample user.

Output:

As you can see, we get the authentication_string for the user with the password "secret".

Now, we will use the following ALTER command to change the password for our sample user "malik".

We do not need to use the Flush Privileges command to make the changes effective if we are using the ALTER command to make the changes. We will run the previous SELECT command to see whether the auth string has been changed or not.

Output:

As you can see, we have successfully changed the password using the ALTER command.

Now, we will use the UPDATE command to make the changes. We will set the auth string for our sample user malik. The code for the same is given below.

As we are using the UPDATE command to make the changes, we are required to use the Flush Privileges command to make the changes effective immediately.

We can now run the SELECT query to check the new auth string for our user.

Output:

As you can see, the auth string for our user has been changed to null by using the UPDATE command followed by the Flush Privileges command.

Examples of MySQL Flush Privileges

Now we will look at some examples that will allow us to grasp the concept in depth. So without wasting any time, let us get started.

EXAMPLE 1: Granting privileges to a new user

Let us assume that we have created a new MySQL user and granted some privileges to them. However, we must note that these privileges are not reflected immediately, and we will have to reload the privileges from the grant tables. we can do this using the following code. You can replace the keyword 'newuser' with the user name and the keyword 'password' with the password for that user.

If we view the user privilege table now, it will look something like this. In this example, we are using a sample user with the username 'malik'.

Output:

As you can see, the user has been granted the select, insert and update privileges signified by the Y in front of the corresponding fields. where Y stands for yes and N stands for no.

EXAMPLE 2: Revoking privileges from a user

Until now we have only granted some privileges to a MySQL user, but what if we wanted to revoke some of these privileges? The code given below shows how we can revoke some privileges from a specific user. And again, the changes may not be reflected immediately, so we need to use the Flush Privileges command.

In the above code, we use the REVOKE statement which revokes the SELECT privilege on the mydatabase database from the 'malik' user. The Flush Privileges command is then used to reload the privileges from the grant tables.

Let us see the privilege table for our users and see if there are any changes.

Output:

As you can see, the select privilege has been revoked for the user.

EXAMPLE 3: Updating privileges for a user

Let us suppose that we have already granted some privileges to a MySQL user, but now we want to update these privileges. we can use the following code to update the privileges and reload them using the Flush Privileges command

The only difference between this and the code in example 1 is that when we are changing privileges for an already existing user, we do not need to add the "IDENTIFIED BY" statement in our query. Otherwise, this query also works in the same way.

EXAMPLE 4: Changing the password of an existing user

So now if we want to change the password of an existing MySQL user in our database, we can do that by using the following code to change the password and reload the privileges using the flush privileges command.

Here, 'user1' is the name of the user, and 'newpassword' signifies the new password that you want to set for the 'user1'. The SET PASSWORD statement is used to change the password of the user1 user. The PASSWORD function works by encrypting the new password before setting it in the database. The Flush Privileges command is then used to reload the privileges from the grant tables.

EXAMPLE 5: Removing a MySQL user

Suppose that we want to remove a MySQL user and revoke all their privileges. we can do that by using the following code and removing the user and reloading the privileges using the flush privileges command

In this code, we use the DROP USER statement which removes the malik user from the MySQL database. The Flush Privileges command is then used to reload the privileges from the grant tables.

EXAMPLE 6: Granting privileges to a user on a specific table

Now let us suppose that we have a scenario where we want to grant some privileges to a MySQL user on a specific table within a database and not on all database tables. we can use the following code to grant the privileges and reload the privileges using the flush privileges command.

Here, mytable is the table to which we want to assign privileges to the user. We are using the GRANT statement which grants the malik user SELECT, INSERT, and UPDATE privileges on the mytable table in the mydatabase database. The Flush Privileges command is then used to reload the privileges from the grant tables.

EXAMPLE 7: Revoking all privileges from a user

Now if we want to revoke all the privileges granted to a MySQL user we can do that by using the following code and revoke all privileges and reload the privileges using the flush privileges command

In the above code, we are using the REVOKE statement which revokes all the privileges and grant options from the malik user. The Flush Privileges command is then used to reload the privileges from the grant tables.