When is FLUSH PRIVILEGES in MySQL really needed?

[xluffy]

MySQL docs

If you modify the grant tables indirectly using an account-management statement, the server notices these changes and loads the grant tables into memory again immediately. Account-management statements are described in Section 13.7.1, “Account Management Statements”. Examples include GRANT, REVOKE, SET PASSWORD, and RENAME USER.

Và

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

Ví dụ:

mysql> USE mysql
mysql> UPDATE user SET authentication_string =PASSWORD('dbl2021') WHERE user = 'xluffy';
mysql> FLUSH PRIVILEGES

Nhưng xài SET STATEMENT thì impact ngay lập tức

mysql> SET PASSWORD FOR 'xluffy'@'localhost' =  PASSWORD('dbl2021');

Nhưng docs bảo là ưu tiên ALTER hơn là SET

ALTER USER 'xluffy'@'localhost' IDENTIFIED BY 'dbl2021';

Và theo thứ tự ưu tiên thì ngược từ dưới lên, ALTER, SET và không nên xài UPDATE.