42 Do We Need to Flush Privileges After Grant

42 Do We Need to flush privileges After grant #

In MySQL, the grant statement is used to assign privileges to users. You may have seen in some operation documents that after executing the grant statement, you should immediately follow it with a flush privileges command to make the grant statement effective. When I first started using MySQL, I followed this order of operations according to the instructions in an operation document.

So, do I really need to execute flush privileges after grant? If I don’t execute this flush command, will the grant statement really not be effective?

Next, let me first introduce to you what the grant statement and flush privileges statement do separately, and then we can analyze this issue together.

To facilitate the explanation, let me first create a user:

create user 'ua'@'%' identified by 'pa';

This statement creates a user ‘ua’@’%’ with the password ‘pa’. Note that in MySQL, the username (user) plus the address (host) represents a user. Therefore, ua@ip1 and ua@ip2 represent two different users.

This command performs two actions:

  1. On the disk, it inserts a row into the mysql.user table. Since no privileges are specified, all the fields representing privileges in this row have the value N.
  2. In memory, it inserts an acl_user object into the array acl_users with the access field value set to 0.

Figure 1 shows the state of the user ua in the user table at this moment.

img

Figure 1 mysql.user data row

In MySQL, user privileges have different scopes. Next, I will explain it to you in order from larger scope to smaller scope.

Global Privileges #

Global privileges apply to the entire MySQL instance and this information is stored in the user table in the mysql database. If I want to assign the highest level of privilege to the user ua, the statement would be written as follows:

grant all privileges on *.* to 'ua'@'%' with grant option;

This grant command performs two actions:

  1. On the disk, it modifies all the fields representing privileges in the row of the user ‘ua’@’%’ in the mysql.user table to have the value ‘Y’.
  2. In memory, it finds the acl_user object corresponding to this user in the acl_users array and modifies the access value (privilege bits) to binary “1’s”.

After this grant command is executed, if a new client logs in successfully using the username ua, MySQL will maintain a thread object for the new connection, then look up the permissions of this user from the acl_users array and copy the permissions value into this thread object. Subsequently, when statements are executed in this connection, all checks related to global privileges will directly use the permissions stored in the thread object.

Based on the above analysis, we can conclude that:

  1. The grant command updates both the disk and memory for global privileges. The command takes effect immediately, and new connections will use the new privileges.
  2. For an existing connection, its global privileges are not affected by the grant command.

It should be noted that in a production environment, it is generally advisable to control the scope of user privileges properly. The grant statement we used above is a typical incorrect example. If a user has all privileges, they should not be allowed to access from any IP address.

To revoke the privileges granted by the above grant statement, you can use the following command:

revoke all privileges on *.* from 'ua'@'%';

This revoke command is used in a similar way to the grant command and performs the following two actions:

  1. On the disk, it modifies all the fields representing privileges in the row of the user ‘ua’@’%’ in the mysql.user table to have the value “N”.
  2. In memory, it finds the acl_user object corresponding to this user in the acl_users array and modifies the access value to 0.

Database-specific (db) Privileges #

In addition to global privileges, MySQL also supports defining privileges at the database level. If you want to grant user ua all privileges on the database db1, you can execute the following command:

grant all privileges on db1.* to 'ua'@'%' with grant option;

Privileges based on databases are recorded in the mysql.db table on disk, and in memory, they are stored in the acl_dbs array. This grant command performs the following two actions:

  1. On the disk, it inserts a row into the mysql.db table with all privilege bit fields set to “Y”.
  2. In memory, it adds an object to the acl_dbs array with all privilege bits set to “1”.

Figure 2 shows the state of the user ua in the db table at this moment.

img

Figure 2 mysql.db data row

Every time the permissions of a user on a database need to be checked, the acl_dbs array is traversed to find a matching object based on user, host, and db, and the permissions value of the object is used to determine the access.

In other words, when granting or revoking permissions at the db level, it takes effect on both the disk and memory simultaneously.

The impact of the grant operation on existing connections is different for global privileges and db-specific privileges. Next, let’s do a comparative experiment to see this difference.

img

Figure 3 Effect of permission operations

It should be noted that the operation set global sync_binlog in the figure requires super privileges.

As you can see, although the super privilege of the user ua has been revoked at time T3, when executing set global at time T4, the permission check still passed. This is because the super privilege is a global privilege, and this privilege information is in the thread object, which is not affected by the revoke operation.

In conclusion, for global privileges, the grant operation updates both disk and memory, and the effect is immediate. For db-specific privileges, the grant operation also updates disk and memory simultaneously. And at T5, when the ua is revoked all privileges on db1 database, at T6, when session B attempts to operate on tables in db1, it will return an error “insufficient privileges”. This is because acl_dbs is a global array, and all threads use this array to determine database privileges. As a result, the revoke operation immediately affects session B.

There is a special logic in the code implementation. If the current session is already in a certain database, the privileges obtained when using this database will be saved in the session variable.

You can see that at T6, session C and session B have the same logic when operating on table t. However, session B returns an error, while session C can execute successfully. This is because session C executed “use db1” at T2 and obtained the privileges for this database. Session C has been granted access to this database before switching out of db1.

Table Privileges and Column Privileges #

In addition to database-level privileges, MySQL also supports more granular table privileges and column privileges. Table privileges are defined in the mysql.tables_priv table, and column privileges are defined in the mysql.columns_priv table. Both types of privileges are stored in the column_priv_hash hash structure in memory.

The command for granting these privileges is as follows:

create table db1.t1(id int, a int);
 
grant all privileges on db1.t1 to 'ua'@'%' with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

Similar to database privileges, granting these two privileges will modify the data tables and synchronously modify the hash structure in memory. Therefore, operations on these two types of privileges will immediately affect existing connections.

At this point, you may wonder that it seems that grant statements take effect immediately, so there is no need to execute the flush privileges statement.

Indeed, that is the case.

The flush privileges command clears the acl_users array and then reads the data from the mysql.user table to reload and reconstruct the acl_users array. In other words, based on the data in the data table, the global privilege memory array is reloaded.

Similarly, MySQL also handles database, table, and column privileges in this way.

In other words, if the memory permissions data and the disk data table are the same, there is no need to execute flush privileges. And if we use grant/revoke statements to execute, the memory and data table will already be synchronized.

Therefore, under normal circumstances, there is no need to execute flush privileges after a grant statement.

Use Cases for flush privileges #

So when do we use flush privileges? Obviously, when the permissions data in the data table is inconsistent with the permissions data in memory, the flush privileges statement can be used to rebuild the memory data and achieve consistency.

This inconsistency is often caused by non-standard operations, such as directly manipulating system permission tables using DML statements. Let’s take a look at the following scenario:

img

Figure 4: Using flush privileges

As you can see, even though the ua user has been deleted using a delete statement at T3, the user can still successfully connect at T4. The reason is that the acl_users array in memory still contains this user, so the system considers the user to still exist.

After executing the flush command at T5, the memory is updated. If we attempt to log in with ua at T6, it will return an error of “access denied”.

Directly manipulating system tables is not a standard operation, and this inconsistent state can also lead to some more “strange” phenomena. For example, in the example of deleting a user using a delete statement, the following situation may occur:

img

Figure 5: Abnormalities caused by non-standard permission operations We can see that because the records of the data table were directly deleted at time T3, the data in memory still exists. This leads to the following consequences:

  1. At time T4, the permission assignment to user ua fails because the record cannot be found in the mysql.user table.
  2. And at time T5, recreating this user is also not possible because during the memory check, it will be considered that this user still exists.

Summary #

In this article, I introduced to you the existence of MySQL user permissions in both the data table and memory, as well as the execution logic of the grant and revoke commands.

The grant statement will modify both the data table and memory, and the memory data is used to determine permissions. Therefore, it is not necessary to include a flush privileges statement after using grant and revoke statements.

The flush privileges statement rebuilds a copy of the memory permission data using the data from the data table, so it can be used when there may be inconsistencies in permission data. However, these inconsistencies are often caused by directly manipulating the system permission table with DML statements, so it is best to avoid using such statements.

Furthermore, when using the grant statement to grant privileges, you may also come across this syntax:

grant super on *.* to 'ua'@'%' identified by 'pa';

This command includes identified by ‘password’ and in addition to granting privileges, it also includes:

  1. If the user ‘ua’@’%’ doesn’t exist, create this user with the password ‘pa’.
  2. If the user ua already exists, change the password to ‘pa’.

This is also not a recommended approach because it is easy to accidentally change the password.

“I used to always add flush privileges after grant” is something I did for two or three years until I realized it wasn’t necessary when reading the code, and that was back in 2011.

Last year, when I pointed out this issue to a colleague who was still doing it, he also found it quite surprising. Because, like me, he also started with the documentation saying to do it this way and had been doing it like that all along.

So, today’s after-class question is, have you ever encountered a similar scenario in using databases or writing code: that you used it incorrectly for a long time and then suddenly realized “Oh, I’ve been wrong for so long”?

Feel free to share your experience in the comments, and I will select interesting comments to share with you at the end of the next article. Thank you for listening and feel free to share this article with more friends to read together.

Previous Question Recap #

The previous question was, when MySQL parses a statement-format binlog, why does it use “load data local” instead of just “load data”?

One reason for doing this is to ensure that the binlog is applied correctly on the standby database. Because the standby database may have the secure_file_priv=null configuration, without using “local,” the import may fail and cause replication delay between the primary and standby databases.

Another use case is when using the mysqlbinlog tool to parse binlog files and apply them to the target database. You can use the following command:

mysqlbinlog $binlog_file | mysql -h$host -P$port -u$user -p$pwd

This command parses the log and sends it directly to the target database for execution. Adding “local” allows this method to support a non-local $host.