30

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.

3 Answers 3

58

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.

1
  • 1
    Works fine for MySQL version 8 Commented Jul 10, 2019 at 12:09
6

Just update the host field in your MySQL user table:

update mysql.user set Host = 'newIP' where Host = 'oldIP';
flush privileges;
3
  • 1
    Tried that: it doesn't work if there are grants.
    – cjc
    Commented Nov 10, 2011 at 21:41
  • OH...good call. My bad.
    – jdw
    Commented Nov 14, 2011 at 20:30
  • 1
    This should work just fine, you just need to execute the flush privileges statement after you manually manipulate any of the privileges tables.
    – Zoredache
    Commented 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.%'
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
    Commented Nov 10, 2011 at 21:42
  • You can update the existing users with the same syntax. Commented Nov 10, 2011 at 21:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .