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



  • My team has been managing permissions (MySQL 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  ON ...;
    GRANT  ON ...;
    FLUSH PRIVILEGES;
    

    This is pretty similar to https://dba.stackexchange.com/questions/59273/how-to-revoke-and-re-grant-permissions-for-an-in-use-mysql-user 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 testuser@localhost IDENTIFIED BY '1234!@#$';
    GRANT SELECT, INSERT on *.* to testuser@localhost;
    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 testuser@localhost;
    GRANT UPDATE ON *.* TO testuser@localhost;
    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 testuser@localhost;
    GRANT SELECT, UPDATE ON *.* TO testuser@localhost;
    FLUSH PRIVILEGES;
    

    How about this?

    DROP USER testuser@localhost;
    CREATE USER testuser@localhost ...; 
    GRANT SELECT, UPDATE ON *.* TO testuser@localhost;
    FLUSH PRIVILEGES;
    


  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2