MySQL: Alter user permissions in one go instead of manual revokes/grants

by

The question:

My team has been managing permissions (MySQL <=5.7) by creating “roles” (not MySQL 8 roles) and then programmatically calculating the target permissions. Then, using those we get the current permissions and create a “diff”. We use that diff to issue REVOKE, GRANT, CREATE USER, …, statements to alter permissions if they fall out of sync.

Would it be possible to simplify this process by just removing all permissions and then granting the desired ones in one go? And more importantly, will it disrupt running queries or connections if no permissions were modified? Something like this:

REVOKE <ALL> ON ...;
GRANT <NEW> ON ...;
FLUSH PRIVILEGES;

This is pretty similar to this question but I want to target individual permissions, and doesn’t clearly answer if it affects running queries.

Example:

A user gets created by a previous run of our tool, or manually by some drunk developer/DBA.

CREATE USER [email protected] IDENTIFIED BY '[email protected]#$';
GRANT SELECT, INSERT on *.* to [email protected];
FLUSH PRIVILEGES;

A week later we decide we want to replace SELECT, INSERT with SELECT, UPDATE. Currently we’re doing that like this:

  1. Get current (from MySQL) and target privileges (from our external source)
  2. Diff them to produce something like: “remove INSERT”, “add UPDATE”
  3. Apply:
REVOKE INSERT ON *.* FROM [email protected];
GRANT UPDATE ON *.* TO [email protected];
FLUSH PRIVILEGES;

This works fine. Permissions we want to keep stay there and are never revoked. MySQL can do whatever with permissions we’ve revoked (e.g. throw permission errors or whatever).

My question is: will the following work fine? Is there any difference? Could the initial revoke affect running queries of any kind?

REVOKE ALL PRIVILEGES ON *.* FROM [email protected];
GRANT SELECT, UPDATE ON *.* TO [email protected];
FLUSH PRIVILEGES;

How about this?

DROP USER [email protected];
CREATE USER [email protected] ...; 
GRANT SELECT, UPDATE ON *.* TO [email protected];
FLUSH PRIVILEGES;

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

GRANT and REVOKE and related commands take effect immediately. FLUSH PRIVILEGES is unnecessary and redundant in your examples.

Given that, dropping a user or revoking all privileges would definitely cause disruption. Privileges are checked on every SQL query, so if you revoke privileges, the user immediately loses access.

Demo:

I use the superuser to grant to a user ‘scott’:

mysql> grant all privileges on test2.* to 'scott'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Then in another window, scott queries a table, successfully:

mysql> select * from test2.mytable;
+-------------+
| temperature |
+-------------+
|        99.6 |
|       100.9 |
+-------------+

In my first window, I revoke those privileges, without using FLUSH PRIVILEGES:

mysql> revoke all privileges on test2.* from 'scott'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Scott’s next query fails.

mysql> select * from test2.mytable;
ERROR 1142 (42000): SELECT command denied to user 'scott'@'localhost' for table 'mytable'

Therefore I think your idea to “clean the slate” and then start granting privileges will be disruptive, at least briefly.

FLUSH PRIVILEGES is harmless, but unnecessary in most cases I see it used. Its use is the following:

  • Reload the cache of privileges after using INSERT/UPDATE/DELETE directly on data in the privilege tables in the mysql schema.

  • Purge memory used by the privilege cache, in case you had done a lot of temporary grants and expanded the memory use.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment