MySQL Flush Privileges

To keep the database secure, the specific permissions are set for specific users, called user’s privilege. The root user has all privileges, and any permission can be granted to or revoked from the user by the root user. The permission for different types of database operations, such as insert, update, delete, select, and create, can be set for the user by granting the permission and removed by revoking the permission. After granting particular permissions for the user, there are two ways to make the changes effective. One way is to restart the database server, and another way is to use the flush privileges command. The uses of the MySQL flush privileges command have been shown in this tutorial.

Effects of using Flush Privileges:

• The newly granted permission for the user will be activated after executing the FLUSH PRIVILEGES command, and the user will be able to access the new privileges.
• If the current database is changed to the new one, then all newly granted permissions will be affected for the FLUSH PRIVILEGES command.
• The database changes related to the global settings and password of the user are not affected by the FLUSH PRIVILEGES command.
• The effect of the database changes will not be visible after executing the FLUSH PRIVILEGES command if the database is cached. You have to set the current database with another database and again change to the previous database.

Syntax:

The syntax of the flush privileges command is given below. After executing the following command, the grant table will be reloaded in the memory.

Uses of MySQL Flush Privileges:

You have to create a new MySQL user to know the uses of the FLUSH PRIVILEGES command.

Connect with the MySQL server:

Open the terminal and connect with the MySQL server by executing the following command.

Create a new user:

Run the following command to create a new user with the username, ‘fahmida’ and the password ‘secret.’

When a new user is created in MySQL, the default permissions are set for the new users. The user table of the MySQL database contains the permission-related data of all users. Run the following command to check the permission for the newly created user, ‘fahmida.’

The above command will generate a long list of information. Some portion of the output has shown in the following image. The output shows that no permissions are granted for the user, ‘fahmida’ by default.

Update the user’s permission:

The specific permission for any user can be granted by using the UPDATE query or by using the GRANT command. If the permission is changed by using the UPDATE query, then the FLUSH PRIVILEGE command is required to make the change effective. If the permission is changed by using the GRANT command, then the FLUSH PRIVILEGE command is not required to use to make the change effective.

Run the following UPDATE query to set the SELECT permission for the user, ‘fahmida’ for all tables of all databases.

To make the update operation effective, you have to run the FLUSH PRIVILEGE command.

Rerun the following command after setting the SELECT permission for the user, ‘fahmida.’

Output:

The following output shows that SELECT privilege has been granted to the user.

Run the following GRANT command to set the INSERT and DELETE permissions for the user, ‘fahmida.’ You don’t need to use the FLUSH PRIVILEGES command for this command.

Run the following SELECT command again after setting the INSERT and DELETE permissions for the user, ‘fahmida.’

Output:

The following output shows that INSERT and DELETE privileges have been granted to the user.

Change the user’s password:

Many commands exist in MySQL to change the user’s password. SET, ALTER, and UPDATE statements. The FLUSH PRIVILEGE command will be required to use if the password is changed by using the UPDATE query, and the FLUSH PRIVILEGE command will not be required if the password is changed by SET or ALTER commands. The uses of ALTER and UPDATE statements have been shown here.

Run the following command to check the value of the authentication_string value before changing the password.

Output:

The following output shows the authentication_string value for the password, ‘secret.’

Run the following ALTER command to change the password for the user, ‘fahmida.’

You don’t need to use the FLUSH PRIVILEGES command to activate the password changed by the ALTER statement. Run the following command to check the authentication_string value after changing the password.

Output:

The following output shows the new authentication_string value after changing the password.

Run the following UPDATE statement to set the NULL password by using authentication_string for the user, ‘fahmida.’

The FLUSH PRIVILEGES command is required to use here to make the change effective for the UPDATE statement.

Run the following command to check the authentication_string value after changing the password.

Output:

The following output shows the new authentication_string value after changing the password.

Conclusion:

The uses of the FLUSH PRIVILEGES command after granting privileges and changing the password for the user have been shown in this tutorial by creating a new user account. Using the FLUSH Privilege command will be cleared after reading this tutorial.