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

I have a number of users who are connecting to MySQL over a VPN, so we have grants along the lines of "grant select on foo.* to user@ipaddress1" and so on.

This week, the IP used on the VPN changed to address2, so user@ipaddress1 grants no longer work.

What's the best way to handle updating the user and grant information in MySQL to reflect this change?

Note that the grants are a serious mess, because some users are excluded from particular columns in particular tables, so we've had to do grants around the excluded objects.

share|improve this question

3 Answers 3

up vote 11 down vote accepted

Apparently, the right way to do this is:

RENAME USER user@ipaddress1 TO user@ipaddress2;

http://dev.mysql.com/doc/refman/5.0/en/rename-user.html

This takes care of all the grants.

share|improve this answer

If you have a dedicated subnet for your VPN users the following syntax works well.

GRANT ALL ... user_name@'192.168.1.%'
share|improve this answer
    
That doesn't help with the existing users at a specific IP. We would still have to redo all the grants, even if we're using a range the next time through. –  cjc Nov 10 '11 at 21:42
    
You can update the existing users with the same syntax. –  Tim Brigham Nov 10 '11 at 21:45

Just update the host field in your user's table:

update mysql.users set Host = newIP where Host = oldIP;
flush privileges;
share|improve this answer
    
Tried that: it doesn't work if there are grants. –  cjc Nov 10 '11 at 21:41
    
OH...good call. My bad. –  jdw Nov 14 '11 at 20:30
    
This should work just fine, you just need to execute the flush privileges statement after you manually manipulate any of the privileges tables. –  Zoredache May 5 '12 at 1:41

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.