Author: Yang Taotao
Senior database expert, specializing in MySQL for more than ten years. He is good at backup and recovery, SQL tuning, monitoring, operation and maintenance, high availability architecture design related to MySQL, PostgreSQL, MongoDB and other open source databases. At present, he works in aikesheng, providing MySQL related technical support and MySQL related course training for major operators and banking and financial enterprises.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
This article comes from the questions asked by customers.
The summary of the problem is as follows: in my memory, after authorizing users in MySQL, you need to execute flush privileges to take effect. Why didn't I see the statement of executing flush privileges in your articles related to user authorization?
To answer this question, we must first understand the function of the statement flush privileges? Flush privileges is a sub item in the flush statement set. Its function is to synchronize the authorization data in MySQL disk and memory to make them consistent. The point of detail is to read the disk data of the authorization table in full and completely cover the authorization data in memory, or reverse synchronize the authorization data on the disk.
For details, please refer to the manual link on the official website: https://dev.mysql.com/doc/ref...
Next, use two simple examples to illustrate when to execute the flush privileges statement.
First, when to use?
When the authorization data is inconsistent in disk and memory, there are two opportunities: one is that the memory data is relatively new; The other is that the disk data is newer. When either of these two points exists, the flush privileges statement needs to be executed.
For example, directly to the table mysql If the user executes the DML statement, the disk data is relatively new at this time. You need to manually execute the flush privileges statement to overwrite the authorization data in memory.
For example, user ytt_u1 has the following permission data:
mysql:(none)>show grants for ytt_u1; +-------------------------------------+ | Grants for ytt_u1@% | +-------------------------------------+ | GRANT SELECT ON *.* TO `ytt_u1`@`%` | +-------------------------------------+ 1 row in set (0.00 sec)
The administrator can directly update the underlying user table data: cancel the select permission.
mysql:(none)>update mysql.user set select_priv='N' where user ='ytt_u1'; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0
Review user YTT_ Permission data owned by U1: the result is not synchronized.
mysql:(none)>show grants for ytt_u1; +-------------------------------------+ | Grants for ytt_u1@% | +-------------------------------------+ | GRANT SELECT ON *.* TO `ytt_u1`@`%` | +-------------------------------------+ 1 row in set (0.00 sec)
Manually execute the flush privileges statement: view the user YTT again_ The permission data of U1 has been synchronized to the latest.
mysql:(none)>flush privileges; Query OK, 0 rows affected (0.12 sec) mysql:(none)>show grants for ytt_u1; +------------------------------------+ | Grants for ytt_u1@% | +------------------------------------+ | GRANT USAGE ON *.* TO `ytt_u1`@`%` | +------------------------------------+ 1 row in set (0.00 sec)
Second, when does it not need to be used?
MySQL internal commands automatically update or delete user authorization data.
For example, create user, grant, revoke and other statements will automatically synchronize the authorization data after execution. If there are no exceptions, there is no need to manually execute the flush privileges statement. For example: user ytt_u1, check the latest permission data, and the result shows that there is no permission.
mysql:(none)>show grants for ytt_u1; +------------------------------------+ | Grants for ytt_u1@% | +------------------------------------+ | GRANT USAGE ON *.* TO `ytt_u1`@`%` | +------------------------------------+ 1 row in set (0.00 sec)
Use grant statement to give user ytt_u1 gives the database ytt read-only permission:
mysql:(none)>grant select on ytt.* to ytt_u1; Query OK, 0 rows affected (0.20 sec)
View user YTT_ Permission data of U1: the data has been synchronized to the latest.
mysql:(none)>show grants for ytt_u1; +-----------------------------------------+ | Grants for ytt_u1@% | +-----------------------------------------+ | GRANT USAGE ON *.* TO `ytt_u1`@`%` | | GRANT SELECT ON `ytt`.* TO `ytt_u1`@`%` | +-----------------------------------------+ 2 rows in set (0.00 sec)
Let's see whether the cancellation of authorization will also synchronize the latest authorization data:
mysql:(none)>revoke select on ytt.* from ytt_u1; Query OK, 0 rows affected (0.09 sec)
View user YTT_ Permission data of U1: the data is also synchronized in time.
mysql:(none)>show grants for ytt_u1; +------------------------------------+ | Grants for ytt_u1@% | +------------------------------------+ | GRANT USAGE ON *.* TO `ytt_u1`@`%` | +------------------------------------+ 1 row in set (0.00 sec)