MySQL users changed their IP address. What's the best way to deal with this?

Question

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.

score 55 · Accepted Answer · edited Jan 20 '23 at 20:52

55

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.

edited Jan 20 '23 at 20:52

Manuel Jordan

131

answered Aug 13 '12 at 19:10

cjc

25,114

Works fine for MySQL version 8 – Manuel Jordan Jul 10 '19 at 12:09

score 5 · Answer 2 · edited Apr 25 '21 at 12:10

5

Just update the host field in your MySQL user table:

update mysql.user set Host = 'newIP' where Host = 'oldIP';
flush privileges;

edited Apr 25 '21 at 12:10

Alok Rajasukumaran

105

answered Nov 10 '11 at 18:00

jdw

3,865

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 05 '12 at 01:41

score 2 · Answer 3 · edited Aug 13 '12 at 23:33

2

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

GRANT ALL ... user_name@'192.168.1.%'

edited Aug 13 '12 at 23:33

bahamat

6,313

answered Nov 10 '11 at 16:54

Tim Brigham

15,585

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

MySQL users changed their IP address. What's the best way to deal with this?

3 Answers3