Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
This is exactly what I needed, however I added a second line doing the same thing to the mysql.db table. Was this necessary? (it seems to work regardless, just curious). –  Nick Jennings Dec 16 '09 at 13:51
    
Not sure actually. Just realized I wasn't as familiar with MySQL as I thought I was. Will have to look into into it and get back to you. (Curious myself now.) –  andol Dec 16 '09 at 13:55

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.