How to Implement Flush Privileges in MySQL

1. Implement Flush Privileges in MySQL
2. The Grants Table in MySQL
3. Use the FLUSH PRIVILEGES Keyword to Commit Direct Permission Changes to the Server in MySQL
4. Use the GRANT Keyword to Modify User Permissions in MySQL

This tutorial explains the flush privileges action and its implementation via examples.

Implement Flush Privileges in MySQL

MySQL implements user management via the grants table for ensuring security and access control in the server. Typically, a root user modifies the grants table directly via an UPDATE statement or indirectly via the GRANT keyword.

However, directly modifying the grants table requires a flush privilege action or a restart/reload of the server to reflect the changes.

The flush privileges action, being more convenient and efficient for performing multiple changes to the server, can be invoked with three keywords in MySQL.

1. The FLUSH PRIVILEGES command.
2. The mysqladmin flush-privileges command.
3. The mysqladmin reload command.

The Grants Table in MySQL

Understanding the MySQL grants table allows for a better context for flush privileges. As previously described, the grants table is a system table that stores information about the various users and their permissions in the MySQL server connection.

To check the privileges in the grants table, use the SHOW GRANTS keyword.

-- Showing Grant privileges for the current user
SHOW GRANTS FOR CURRENT_USER();

/* Showing Grant privileges for specific user
SHOW GRANTS FOR [USERNAME]
*/

The privileges outlined in the grants table describe the restrictions or permissions available to users. Let us create a user called test_user and view this user’s privileges in the mysql.user table.

-- Creating a sample user
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '20202010';
-- checking assigned privileges
SELECT * FROM mysql.user where user='test_user' \G;

Use the FLUSH PRIVILEGES Keyword to Commit Direct Permission Changes to the Server in MySQL

The UPDATE statement combined with a flush privileges command grants a user privilege and reflects the changes simultaneously.

For example, let us grant the test_user read-only privilege (SELECT) for all databases in the server.

UPDATE mysql.user    		-- Directly modifying the user table
SET Select_priv = 'Y'		-- Granting SELECT privilege for test_user
WHERE user = 'test_user';

SELECT * FROM mysql.user where user='test_user' \G;    -- Viewing changes

Output:

*************************** 1. row ***************************
                    Host: localhost
                    User: test_user
            	    Select_priv: Y     <- This is the updated permission
             		Insert_priv: N
             		Update_priv: N
             		Delete_priv: N                 (OUTPUT HAS BEEN TRUNCATED)

The query result shows that the user now has SELECT permission on all databases. However, this action does not yet reflect in the server.

SHOW GRANTS FOR test_user@localhost;    -- Viewing the grants for the user

Output:

+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`localhost` |
+-----------------------------------------------+
1 row in set (0.00 sec)

Now, let us perform a flush privileges action.

FLUSH PRIVILEGES;    					-- This affects the changes made
SHOW GRANTS FOR test_user@localhost;    -- Viewing the grants for the user, again

Output:

+------------------------------------------------+
| Grants for test_user@localhost                 |
+------------------------------------------------+
| GRANT SELECT ON *.* TO `test_user`@`localhost` |
+------------------------------------------------+
1 row in set (0.00 sec)

The same approach can be taken for the other flush privilege commands, i.e., mysqladmin flush-privileges and mysqladmin reload.

The recommended way to modify permissions for a user is via the GRANT command, as changes automatically reflect without the need for a flush privileges action.

This distinction between the direct and indirect modification of the grants table is sufficiently detailed in this official documentation.

Use the GRANT Keyword to Modify User Permissions in MySQL

Let us give the test_user the INSERT privilege on all databases and tables on the server. This time, we use the GRANT ON command.

The GRANT ON command syntax is below.

GRANT [privilege(s)] ON [Db_name . table_name] TO user

To specify all databases and tables, use the wildcard *.* in place of [Db_name . table_name].

GRANT INSERT ON *.* TO test_user@localhost;   -- Giving test_user Insert privileges
SHOW GRANTS FOR test_user@localhost;   -- Checking for reflected changes

Output:

+--------------------------------------------------------+
| Grants for test_user@localhost                         |
+--------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `test_user`@`localhost` |
+--------------------------------------------------------+
1 row in set (0.00 sec)

As expected, the change reflects without using the FLUSH PRIVILEGES command.