If you Google ten random MySQL/MariaDB tutorials that involve setting up a database, 9 out of 10 will go something like this:
CREATE DATABASE somedb;
CREATE USER 'someone'@'localhost' identified by 'secret';
GRANT ALL on somedb.* to 'someone'@'localhost';
FLUSH PRIVILEGES;
Guess what? That last command is completely unnecessary.
Legions of MySQL users (from professional DBAs to devs who just need to store crap in a table) have learned that after granting, you must flush privileges. But that has never been true.
Let’s go waaay back to MySQL 3.26. That’s about 2001. The Earth’s crust was just cooling. In the manual we read:
when the grant tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.
In MySQL 4, a section entitled “When Privilege Changes Take Effect” clarifies matters:
If you modify the grant tables indirectly using statements such as GRANT, REVOKE, or SET PASSWORD, the server notices these changes and loads the grant tables into memory again immediately.
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. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.
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 do not seem to make any difference!
This languages continues (without the final quip, alas) in the current manual.
So the rule is quite simple:
- If you GRANT privileges with the GRANT command, then GRANT will take care of any flushing necessary. You do not need to flush privileges. This is the norm.
- If you use INSERT, UPDATE, or DELETE on the grant tables – which the current manual says “is not recommended” – then you would have to manually FLUSH TABLES.
In other words, if you do something very unusual – which the manual says not to do! – then you have to manually flush. Otherwise, you don’t have to worry about it.
I can’t really think of a reason why you would need to directly modify the tables, though I’m sure there is some corner case out there and someone will let us know in the comments.
Related Posts:
- Crunchbits Discontinuing Popular Annual Plans – The Community Mourns! - November 20, 2024
- RackNerd’s Black Friday 2024: Bigger, Better, and Now in Dublin! - November 19, 2024
- It’s the Season of Giving and CharityHost Has Deals for You! - November 18, 2024
Nice to know!
I’ve been noticing over the years that adding flush or not adding it seemed to always work, good to have confirmation! :)