JIYIK CN >

This tutorial explains the refresh permissions operation and its implementation with examples.

Implementing refresh permissions in MySQL

MySQL implements user management through grant tables to ensure security and access control in the server. Usually, grant tables are modified root 用户directly through UPDATEstatements or indirectly through keywords.GRANT

However, modifying the grant tables directly requires a flush privileges operation or a restart/reload of the server to reflect the changes.

In MySQL, you can use three keywords to invoke the refresh privileges operation, which is more convenient and efficient for performing multiple changes to the server.

1. FLUSH PRIVILEGESOrder.
2. mysqladmin flush-privilegesOrder.
3. mysqladmin reloadOrder.

Grant tables in MySQL

Understanding the MySQL grant tables can provide a better context for refreshing privileges. As mentioned earlier, the grants table is a system table that stores information about various users and their privileges in a MySQL server connection.

To check permissions in the grant tables, use SHOW GRANTSthe 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 listed in the grant tables describe the restrictions or permissions available to a user. Let's create a test_useruser named and mysql.userview the privileges for this user in the grant 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;

In MySQL, use FLUSH PRIVILEGESthe keyword to commit direct permission changes to the server.

UPDATEStatements flushprivilegeare combined with commands to grant privileges to users and reflect changes at the same time.

test_userFor example, let's grant read-only permission ( ) to all databases in the server SELECT.

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 results show that the user now has SELECTpermissions on all databases. However, this action has not yet been reflected 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's perform the flush privileges operation.

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 method can be used for other refresh permission commands, namely mysqladmin flush-privilegesand mysqladmin reload.

The recommended way to modify user permissions is through GRANTthe command, as changes are automatically reflected without the need for a refresh permissions operation.

The difference between direct and indirect modification of the grant tables is explained in sufficient detail in the official documentation.

GRANTModify user privileges in MySQL using keywords

Let's give the test_userSYSCONFDIRECT privilege to all databases and tables on the server INSERT. This time, we use GRANT ONthe SYSCONFDIRECT command.

GRANT ONThe command syntax is as follows.

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

To specify all databases and tables, use the wildcard character *.*instead [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 changes reflect the absence of FLUSH PRIVILEGESthe command.