Skip to main content

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

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.

Question Credit: cjc
Question Reference
Asked July 9, 2019
Tags: mysql
Posted Under: Network
3 Answers

Apparently, the right way to do this is:

RENAME USER user@ipaddress1 TO user@ipaddress2;

This takes care of all the grants.

credit: cjc
Answered July 9, 2019

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

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

credit: bahamat
Answered July 9, 2019

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

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

credit: bahamat
Answered July 9, 2019
Your Answer