Technology sharing | When do you need to manually refresh the authorization form

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

This article comes from a customer inquiries.

The summary of the problem is: in my memory, after authorizing a user in MySQL, it is necessary to execute flush privileges to take effect. Why did I not see the execution of the flush privileges statement in the articles you wrote about user authorization?

To answer this question, we must first understand what is the function of the statement flush privileges? Flush privileges is a sub-item in the flush statement set. The function of executing it is to synchronize the authorization data in MySQL disk and memory to keep them consistent. To be more specific, it is to fully read the disk data of the authorization table and completely cover the authorization data in the memory, or reversely synchronize the authorization data on the disk.
For details, please refer to the official website manual link: https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html

Next, two simple examples illustrate when to execute the flush privileges statement.

First, when to use it?

When the authorization data is inconsistent between disk and memory, there are two opportunities: one is that the memory data is newer; the other is that the disk data is newer. The flush privileges statement is required when either of these two points exists.

For example, if the DML statement is directly executed on the table mysql.user, the disk data is relatively new at this time, and the flush privileges statement needs to be manually executed to cover the authorization data in the memory.

For example, the permissions data owned by user ytt_u1 are as follows:

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

Reviewed permissions data owned by user ytt_u1: Results are not synced.

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: Check the privilege data of user ytt_u1 again, the data 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, after the statements such as create user, grant, and revoke are executed, the authorization data will be automatically synchronized. If there is no abnormality, there is no need to manually execute the flush privileges statement. For example: still user ytt_u1, check the latest permission data, 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 the grant statement to give the user ytt_u1 read-only access to the database ytt:

mysql:(none)>grant select on ytt.* to ytt_u1;
Query OK, 0 rows affected (0.20 sec)

View the permission data of user ytt_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 if the cancel authorization operation 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 the permission data of user ytt_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)

Therefore, as long as the user authorization information is operated according to the built-in user management statement of MySQL, it is not necessary to execute the flush privileges statement manually, and vice versa.