MySQL Bugs: #76769: FLUSH PRIVILEGES should return a warning when no change is done to discourage it

Bug #76769	FLUSH PRIVILEGES should return a warning when no change is done to discourage it
Submitted:	21 Apr 2015 10:43	Modified:	20 Oct 2020 11:08
Reporter:	Jaime Crespo (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is a followup feature request to my writing at http://dbahire.com/stop-using-flush-privileges/ after getting some feedback from other MySQL users.

Many people overuse FLUSH PRIVILEGES (as it is not needed for high-level grant-related statements) https://dev.mysql.com/doc/refman/5.7/en/privilege-changes.html . For example: http://www.cacti.net/downloads/docs/html/unix_configure_cacti.html and multiple uses over internet, tutorials, guides, manuals, even on the MySQL Reference Manual 3rd party comments (not the actual manual).

While this is not a huge issue performance-wise, if encourages non-critical thinking when dealing with MySQL problems, and encourages the usage of low-level DMLs for modifying grants.

How to repeat:
Search for FLUSH PRIVILEGES:

https://www.google.com/search?q=FLUSH+PRIVILEGES
http://dba.stackexchange.com/search?tab=relevance&q=FLUSH%20PRIVILEGES

Most of the results advice to use it after a GRANT or a CREATE USER.

Suggested fix:
In order to discourage its usage, let me propose FLUSH PRIVILEGES returning a note or a warning when the copy on cache of the grant tables is identical to the copy on disk. I do not know if that would be feasible or if that would decrease significantly the performance of that command in a server with lots of rows on the grant tables, but it would alert the users that that command is not needed.

FLUSH PRIVILEGES;
Warning: No changes were detected on the grant tables since the last reload or server startup.

The actual wording should be carefully considered by a usability expert to achieve the desired results and not confusing the user.

Thank you for the very reasonable feature request.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

Url of initial blog post referred on initial report is now at: https://jynus.com/dbahire/stop-using-flush-privileges/ , for archival reasons.