How to Efficiently Update MySQL User Grants When VPN IP Addresses Change

When working with MySQL in VPN environments, IP-based authentication can become problematic when network configurations change. The core issue arises when existing grants like:

GRANT SELECT ON foo.* TO 'user'@'ipaddress1';

become invalid because the VPN now assigns clients a new IP range (ipaddress2). This creates authentication failures despite unchanged credentials.

For clean migration, consider these approaches:

1. Wildcard IP Range Update

If the new IPs follow a predictable pattern:

UPDATE mysql.user SET host = '192.168.1.%' WHERE host = '10.0.0.%';
FLUSH PRIVILEGES;

2. Dual-IP Grant Approach

During transition periods, maintain both grants:

GRANT SELECT ON foo.* TO 'user'@'ipaddress1';
GRANT SELECT ON foo.* TO 'user'@'ipaddress2';

3. Hostname-Based Authentication

For more stable identification:

-- First verify reverse DNS works
GRANT SELECT ON foo.* TO 'user'@'vpn-client-hostname.domain';

For environments with column-level restrictions:

-- Export current grants
SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') 
FROM mysql.user WHERE user LIKE 'vpn_user%' INTO OUTFILE '/tmp/grants.sql';

-- Modify the exported file with new IPs
-- Then re-import
source /tmp/grants_updated.sql;

For bulk updates across multiple users:

#!/bin/bash
OLD_IP="10.0.0.%"
NEW_IP="192.168.1.%"

MYSQL_USER="admin"
MYSQL_PW="securepassword"

mysql -u$MYSQL_USER -p$MYSQL_PW <


To prevent future issues:

Consider using VPN-assigned hostnames instead of IPs
Implement MySQL roles for easier permission management
Set up VPN DHCP reservations for critical users


When working with MySQL in VPN environments, IP-based authentication can become problematic when network configurations change. Recently, I encountered this exact scenario where all our VPN-assigned IPs were modified, breaking existing GRANT statements that specified the old IP addresses.
First, examine your current privileges with:
SELECT user, host FROM mysql.user WHERE host LIKE 'ipaddress1%';
SHOW GRANTS FOR 'user'@'ipaddress1';
For complex column-level restrictions, this becomes critical. You might see output like:
GRANT SELECT (col1, col3), 
UPDATE (col2) ON foo.* 
TO 'user'@'ipaddress1';
Here's the safest approach I've found for migrating users while preserving complex privileges:
-- 1. Create identical user with new IP
CREATE USER 'user'@'ipaddress2' IDENTIFIED BY 'password';

-- 2. Clone permissions (MySQL 8.0+ alternative shown later)
SET @grants = (SELECT CONCAT('GRANT ', 
    REPLACE(REPLACE(REPLACE(
    SHOW GRANTS FOR 'user'@'ipaddress1',
    'GRANT ',''), 'ON', 'ON'), 
    'TO \'user\'@\'ipaddress1\'', 
    'TO \'user\'@\'ipaddress2\''))
FROM mysql.user WHERE user='user' AND host='ipaddress1');

PREPARE stmt FROM @grants;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Verify identical privileges
SHOW GRANTS FOR 'user'@'ipaddress1';
SHOW GRANTS FOR 'user'@'ipaddress2';

-- 4. Only drop old user after confirming everything works
DROP USER 'user'@'ipaddress1';
For modern MySQL versions, use the SHOW CREATE USER and privilege cloning:
-- Clone user with all attributes
SET @create_user = (SELECT SHOW CREATE USER 'user'@'ipaddress1');
SET @create_user = REPLACE(@create_user, '@ipaddress1', '@ipaddress2');
PREPARE stmt FROM @create_user;
EXECUTE stmt;

-- Clone all privileges
SET @clone = CONCAT('GRANT ', 
    (SELECT REPLACE(REPLACE(
        GROUP_CONCAT(DISTINCT privilege_type), 
        'USAGE,', ''), 'USAGE', '')
    FROM information_schema.user_privileges
    WHERE grantee LIKE "'user'@'ipaddress1'"),
    ' ON *.* TO "user"@"ipaddress2"');
PREPARE stmt FROM @clone;
EXECUTE stmt;
For tables with column-specific grants, use this pattern:
-- Generate dynamic SQL for each restricted table
SELECT CONCAT('GRANT ',
    REPLACE(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(show_grants, ' TO', 1),
            'GRANT ', -1),
        '\'user\'@\'ipaddress1\'',
        '\'user\'@\'ipaddress2\'')
    ) AS grant_statement
FROM (
    SELECT SHOW GRANTS FOR 'user'@'ipaddress1' AS show_grants
) AS g
WHERE show_grants LIKE '%(%';
For large-scale migrations, consider scripting this with your preferred language. Here's a Python example:
import pymysql
import re

def migrate_grants(conn, old_ip, new_ip):
    with conn.cursor() as cursor:
        cursor.execute(f"SELECT user FROM mysql.user WHERE host='{old_ip}'")
        users = [row[0] for row in cursor.fetchall()]
        
        for user in users:
            # Create new user with same auth
            cursor.execute(f"SHOW CREATE USER '{user}'@'{old_ip}'")
            create_user = cursor.fetchone()[0].replace(
                f"'{user}'@'{old_ip}'", 
                f"'{user}'@'{new_ip}'")
            cursor.execute(create_user)
            
            # Clone privileges
            cursor.execute(f"SHOW GRANTS FOR '{user}'@'{old_ip}'")
            for grant in cursor.fetchall():
                new_grant = grant[0].replace(
                    f"'{user}'@'{old_ip}'", 
                    f"'{user}'@'{new_ip}'")
                cursor.execute(new_grant)

Consider using hostnames instead of IPs where possible
Document all special grants with comments
Implement a configuration management system
Test IP changes in staging first