Modifying Trusted IP Whitelist for MySQL Users

In many production environments MySQL accounts are accessed through a bastion host, and each account is limited to a trusted IP or IP subnet. When the bastion host network changes, the trusted IPs must be updated in bulk.

MySQL does not have a configuration file for trusted IPs; it enforces them through the user@'host' part of the account definition. Creating a user with a specific trusted IP looks like:

create user kexin_ip@'192.168.199.131' identified by 'Myp@ssword';
create user kexin_subnet@'192.168.199.%' identified by 'Myp@ssword';
create user unlimited@'%' identified by 'Myp@ssword';

Only exact‑match IPs (e.g., 192.168.199.131/32 ) or subnet patterns (e.g., 192.168.199.% ) are allowed. Using a non‑trusted IP results in ERROR 1045 (28000): Access denied for user ... .

To change a trusted IP, the simplest method is to update the MySQL system tables directly:

select user,host from mysql.user;
update mysql.user set host='192.168.199.132' where host='192.168.199.131';
flush privileges;

If the account has database‑level, table‑level, or column‑level grants, the corresponding metadata tables ( mysql.db , mysql.tables_priv , mysql.columns_priv ) must also be updated:

update mysql.db set host='192.168.199.132' where host='192.168.199.131';
update mysql.tables_priv set host='192.168.199.132' where host='192.168.199.131';
update mysql.columns_priv set host='192.168.199.132' where host='192.168.199.131';

After these changes, the account can log in from the new IP, but any stored objects (triggers, events, procedures, functions, views) that reference the old account as a DEFINER become orphaned and will fail with errors such as “definer does not exist”.

MySQL 8.0.22+ adds security checks that prevent dropping or renaming users that are definers of stored objects, unless the operation would orphan those objects. However, in practice the checks may only emit warnings.

To locate orphaned objects, query the information_schema :

select ROUTINE_SCHEMA,ROUTINE_NAME,DEFINER from information_schema.ROUTINES where ROUTINE_SCHEMA!='sys';
select TABLE_SCHEMA,TABLE_NAME,DEFINER from information_schema.VIEWS where TABLE_SCHEMA!='sys';
select EVENT_SCHEMA,EVENT_NAME,DEFINER from information_schema.EVENTS;
select TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER from information_schema.TRIGGERS where TRIGGER_SCHEMA!='sys';

Views and events can be altered with ALTER VIEW or ALTER EVENT to change the DEFINER. Stored procedures, functions, and triggers must be dropped and recreated, or their metadata in mysql.proc and mysql.event can be updated directly.

A safer approach is to clone the existing account to a new trusted‑IP account, copy its privileges, and lock the old account until the migration is verified:

create user fander1@'192.168.199.132' identified with mysql_native_password as '*7FAD9BF7D3CDD964A197764D9683CAC478CED556';
grant all privileges on *.* to fander1@'192.168.199.132';
alter user fander1@'192.168.199.131' account lock;

When the new account works, the old one can be dropped.

The article also proposes that MySQL provide a dedicated DCL command to modify trusted IPs without affecting the underlying user object, or a SQL mode that enables renaming users while automatically updating dependent stored objects.