27

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.

Improve this question

3 Answers 3

Reset to default
53

Apparently, the right way to do this is:

RENAME USER user@ipaddress1 TO user@ipaddress2;

For more details see the RENAME USER Statement section

This takes care of all the grants.

Improve this answer
1
  • Works fine for MySQL version 8
    – Manuel Jordan
    Jul 10, 2019 at 12:09
5

Just update the host field in your MySQL user table:

update mysql.user set Host = 'newIP' where Host = 'oldIP';
flush privileges;
Improve this answer
3
  • Tried that: it doesn't work if there are grants.
    – cjc
    Nov 10, 2011 at 21:41
  • OH...good call. My bad.
    – jdw
    Nov 14, 2011 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, 2012 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.%'
Improve this answer
2
  • 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, 2011 at 21:42
  • You can update the existing users with the same syntax.
    – Tim Brigham
    Nov 10, 2011 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.