Home / server / Questions / 94903
Accepted
Nick Jennings
Asked: 2009-12-17 04:24:24 +0800 CST

Re-assign host access permission to MySQL user

  • 772

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 to 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 'bugy' DB.

I want to now allow bugsy access from anywhere on the internal network

'bugsy'@'10.0.0.%' has access to the 'bugsy' DB.

I tried to cheat and use phpmyadmin for one, copy the syntax, rinse repeat for the other several thousand users, but the phpmyadmin way of doing it seems overly complicated. It creates a new user with the same permissions (different host access) and then deletes the old one. This would require me to know what every users password is, which isn't a realistic option for me at this time. I'd simply like to modify the existing users host access.

If anyone has any tips, I'd be very appreciative! :) -Nick

mysql

2 Answers

  1. Best Answer

    Perhaps something like the following?

    mysql> UPDATE mysql.user SET host='10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
mysql> UPDATE mysql.db  SET host='10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
mysql> FLUSH PRIVILEGES;

    Of course, you will probably want to test this in some kind of test environment first.

    • 1

  2. (Copying my answer from stackoverflow; Nick, probably best to close the one there...)

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

    • 0