MySQL: When is Flush Privileges in MySQL really needed?

Just to give some examples. Let's say you modify the password for an user called 'alex'. You can modify this password in several ways. For instance:

mysql> update* user set password=PASSWORD('test!23') where user='alex'; 
mysql> flush privileges;

Here you used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables.

Or you can modify the password like this:

mysql> set password for 'alex'@'localhost'= password('test!24');

Here it's not necesary to use "FLUSH PRIVILEGES;" If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

2 points in addition to all other good answers:

1:

what are the Grant Tables?

from dev.mysql.com

The MySQL system database includes several grant tables that contain information about user accounts and the privileges held by them.

clarification: in MySQL, there are some inbuilt databases , one of them is "mysql" , all the tables on "mysql" database have been called as grant tables

2:

note that if you perform:

UPDATE a_grant_table SET password=PASSWORD('1234') WHERE test_col = 'test_val';

and refresh phpMyAdmin , you'll realize that your password has been changed on that table but even now if you perform:

mysql -u someuser -p

your access will be denied by your new password until you perform :

FLUSH PRIVILEGES;

TL;DR

You should use FLUSH PRIVILEGES; only if you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE.

Privileges assigned through GRANT option do not need FLUSH PRIVILEGES to take effect - MySQL server will notice these changes and reload the grant tables immediately.

From MySQL documentation:

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

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.