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

MySQL

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.

Best Answer

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.

Related Topic