19

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.

35

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.

3

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

update mysql.users set Host = newIP where Host = oldIP;
flush privileges;
  • 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
2

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

GRANT ALL ... user_name@'192.168.1.%'
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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