Re-assign host access permission to MySQL user

views:

105

answers:

Re-assign host access permission to MySQL user

Hello, 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

+1 A:

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

T.J. Crowder 2009-12-16 11:35:23

Thanks for your reply T.J.I tried modifying a user record for both mysql.user and mysql.db and although the changes seemed to be executed fine, I still cannot connect from any host on the internal network other than the original host which the user was allowed access to. I'm not sure how to modify .frm files (they are binary)... I assume you mean the ones in the mysql/ directory (and not the applicable DB in which the user has access to). I'm hesitant to go mucking about in files directly though. Is there really no supported way to do this in MySQL? It seems to be rather short-sighted.

Nick Jennings 2009-12-16 12:13:52

Having changed the tables, I expect you need to execute a `FLUSH PRIVILEGES` command (see http://dev.mysql.com/doc/refman/5.0/en/flush.html) or, of course, stop and start the service. The `frm` files I'm talking about would be for views in the database, and so would be in the database subdirectory. The ones I'm familiary with for the MyISAM storage engine are plain text; YMMV.

T.J. Crowder 2009-12-16 12:57:00

"familiary"? My typing skills have really deteriorated... ;)

T.J. Crowder 2009-12-16 12:57:43

for reference, the solution was: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 ;

Nick Jennings 2009-12-16 13:53:09

ansaurus

tags:

views:

answers:

Re-assign host access permission to MySQL user

related questions