Re-assign host access permission to MySQL user

Question

I have several thousand MySQL users all set to allow access from a specific host  The problem is that now I m going to have two machines  more in the future  which will need to use the same account to access each of their databases   I d like a quick and easy  as automated as possible  way to run through and modify the host portion of each user account to fit an internal network wildcard  For example       bugsy   internalfoo  has access to the  bugsy  DB    I want to now allow bugsy access from anywhere on the internal network      bugsy   10 0 0    has access to the  bugsy  DB

User · Accepted Answer

For reference  the solution is    UPDATE mysql user SET host    10 0 0    WHERE host    internalfoo  AND user     root   UPDATE mysql db SET host    10 0 0    WHERE host    internalfoo  AND user     root   FLUSH PRIVILEGES

User · Answer

The accepted answer only renamed the user but the privileges were left behind   I d recommend using   RENAME USER  foo   1 2 3 4  TO  foo   1 2 3 5     According to MySQL documentation      RENAME USER causes the privileges held by the old user to be those held by the new user

User · Answer

I received the same error with RENAME USER and GRANTS aren t covered by the currently accepted solution   The most reliable way seems to be to run SHOW GRANTS for the old user  find replace what you want to change regarding the user s name and or host and run them and then finally DROP USER the old user  Not forgetting to run FLUSH PRIVILEGES  best to run this after adding the new users  grants  test the new user  then drop the old user and flush again for good measure           SHOW GRANTS FOR  olduser   oldhost        -----------------------------------------------------------------------------------        Grants for olduser oldhost                                                               -----------------------------------------------------------------------------------        GRANT USAGE ON     TO  olduser   oldhost  IDENTIFIED BY PASSWORD   PASSHASH               GRANT SELECT ON  db    TO  olduser   oldhost                                             -----------------------------------------------------------------------------------      2 rows in set  0 000 sec         GRANT USAGE ON     TO  newuser   newhost  IDENTIFIED BY PASSWORD   SAME PASSHASH       Query OK  0 rows affected  0 006 sec         GRANT SELECT ON  db    TO  newuser   newhost       Query OK  0 rows affected  0 007 sec         DROP USER  olduser   oldhost       Query OK  0 rows affected  0 016 sec

User · Answer

Similar issue where I was getting permissions failed   On my setup  I SSH in only  So What I did to correct the issue was  sudo MySQL SELECT User  Host FROM mysql user WHERE Host  lt  gt       MariaDB   none   gt  SELECT User  Host FROM mysql user WHERE Host  lt  gt        ------- -------------    User    Host           ------- -------------    root    169 254 0       foo     192 168 0       bar     192 168 0      ------- -------------  4 rows in set  0 00 sec    I need these users moved to  localhost    So I issued the following   UPDATE mysql user SET host    localhost  WHERE user    foo   UPDATE mysql user SET host    localhost  WHERE user    bar     Run SELECT User  Host FROM mysql user WHERE Host  lt        again and we see   MariaDB   none   gt  SELECT User  Host FROM mysql user WHERE Host  lt  gt        ------- -------------    User    Host           ------- -------------    root    169 254 0       foo     localhost       bar     localhost      ------- -------------  4 rows in set  0 00 sec    And then I was able to work normally again   Hope that helps someone     mysql -u foo -p Enter password  Welcome to the MariaDB monitor   Commands end with   or  g  Your MariaDB connection id is 74 Server version  10 1 23-MariaDB-9 deb9u1 Raspbian 9 0  Copyright  c  2000  2017  Oracle  MariaDB Corporation Ab and others   Type  help   or   h  for help  Type   c  to clear the current input statement   MariaDB   none   gt

User · Answer

I haven t had to do this  so take this with a grain of salt and a big helping of  test  test  test    What happens if  in a safe controlled test environment  you directly modify the Host column in the mysql user and probably mysql db tables   E g   with an update statement   I don t think MySQL uses the user s host as part of the password encoding  the PASSWORD function doesn t suggest it does   but you ll have to try it to be sure  You may need to issue a FLUSH PRIVILEGES command  or stop and restart the server    For some storage engines  MyISAM  for instance   you may also need to check modify the  frm file any views that user has created  The  frm file stores the definer  including the definer s host   I have had to do this  when moving databases between hosts where there had been a misconfiguration causing the wrong host to be recorded

User · Answer

The more general answer is  UPDATE mysql user SET host    newhost  WHERE user    youruser

[mysql] Re-assign host access permission to MySQL user

Examples related to mysql

Examples related to permissions