Home / server / Questions / 329795
Accepted
cjc
Asked: 2011-11-11 08:43:51 +0800 CST

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

  • 772

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.

mysql

3 Answers

  1. 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.

    • 53

  2. Just update the host field in your MySQL user table:

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

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

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