技术分享 | MySQL 修改可信 IP 白名单的方法

例如:

192.168.199.131/32 # 可信IP192.168.199.0/24   # 可信网段
复制

MySQL 没有配置文件可以直接配置可信 IP，MySQL 是通过用户授权来做这个事的。

MySQL 的某个用户在创建的时候，他的远程访问的可信网段访问就是要确认下来的。也就是 mysql 的账号体系中，一个具体的账号，不是 user， user@'host' 才是一个整体。

创建语句格式如下:

create user kexin_ip@'192.168.199.131' identified by 'Myp@ssword';  #可信IP: 192.168.199.131/32 具体IPcreate user kexin_subnet@'192.168.199.%' identified by 'Myp@ssword'; #可信IP: 192.168.199.0/24 整个网段create user unlimited@'%' identified by 'Myp@ssword';  #可信IP: 所有。无限制的账号，安全上不建议这么设置
复制

从安全上说，生产环境是禁止创建含'%'的用户的。

示例中，我建了两个账号，一个叫 kexin_ip，一个叫 kexin_subnet。

其中，kexin_ip 的可信 IP 是 '192.168.199.131' 。如果远程访问，通过用户 kexin_ip 访问数据库，其机器必须是 IP 地址为 '192.168.199.131'，不是的话，会报以下错误。

[root@192-168-199-132 ~]# mysql -ukexin_ip -p'Myp@ssword' -h192.168.199.198 -P3307mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'kexin_ip'@'192.168.199.132' (using password: YES)
复制

提醒，如果密码输错了，远程登录也是报同样的 1045 错误。和可信 IP 原因拒绝访问是同现象，注意鉴别。

而使用 192.168.199.131 这台机器，作为可信 IP ，登录成功。

[root@192-168-199-131 tmp]# mysql -ukexin_ip -p'Myp@ssword' -h192.168.199.198 -P3307 -e "select version()"mysql: [Warning] Using a password on the command line interface can be insecure.+------------+| version()  |+------------+| 5.7.32-log |+------------+
复制

[root@192-168-199-131 tmp]# mysql -ukexin_subnet -p'Myp@ssword' -h192.168.199.198 -P3307 -e "select version()"mysql: [Warning] Using a password on the command line interface can be insecure.+------------+| version()  |+------------+| 5.7.32-log |+------------+
复制

[root@192-168-199-132 ~]# mysql -ukexin_subnet -p'Myp@ssword' -h192.168.199.198 -P3307 -e "select version()"mysql: [Warning] Using a password on the command line interface can be insecure.+------------+| version()  |+------------+| 5.7.32-log |+------------+
复制

吐槽一下

在某些场景下，我们可能需要设置两个可信网段。对于 MySQL 来说，他的可信 IP 白名单的做法，其实是创建两个账号。

appuser@'192.168.199.%'appuser@'10.200.1.%'
复制

鉴于对 MySQL 来说， user@'host' 是一个整体，是一个账号。那么我对此类需求就必须建两个账号，维护两个账号，保证他们密码一致、权限一致，如果没有自动化的运维管理平台，DBA 就挺难受的。

这里体现出 MySQL 比 Oracle 的 IP 白名单要灵活，Oracle 的白名单，只能实现允许或拒绝哪些 IP 的用户请求。但做不到像 MySQL 这种精细化到用户级别的控制。

1. 要求 appuser@'192.168.199.%' ，appuser@'10.200.1.%' 不同的密码，或不同的权限，把这个账号给两个不同的人使用。(PS: 其实这种是不正常的需求，不同人使用，用户名不应该起一样的)

2. 数据库用户 backup
   和 monitor
   可信 IP 只有'127.0.0.1'，而数据库用户 repl 可信 IP 只有 '192.168.199.%' 。

appuser@'192.168.199.%, 10.200.1.%'
复制

其实修改很简单。假设，我们要把 '192.168.199.131' 修改为 '192.168.199.132'，最简单的方法就是，修改 mysql 元数据。

mysql> select user,host from mysql.user;+---------------+-----------------+| user          | host            |+---------------+-----------------+| fander2       | 192.168.199.%   || kexin_subnet  | 192.168.199.%   || fander1       | 192.168.199.131 || kexin_ip      | 192.168.199.131 || mysql.session | localhost       || mysql.sys     | localhost       || root          | localhost       |+---------------+-----------------+7 rows in set (0.00 sec)mysql> update mysql.user set host='192.168.199.132' where host='192.168.199.131';Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select user,host from mysql.user;+---------------+-----------------+| user          | host            |+---------------+-----------------+| fander2       | 192.168.199.%   || kexin_subnet  | 192.168.199.%   || fander1       | 192.168.199.132 || kexin_ip      | 192.168.199.132 || mysql.session | localhost       || mysql.sys     | localhost       || root          | localhost       |+---------------+-----------------+7 rows in set (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
复制

DBA 们在一分钟内已经改好了，部分初级 DBA 有可能忘记 flush privileges
，可能要多花个 10分钟排查原因。

可能中级 DBA 发现不对劲，好像少了一些什么。

如果用户里有授权不是全局授权 *.*
这种类型，而是 db.*
这种类型，例如

grant select on fander.* to fander1@'192.168.199.131';
复制

这个授权，只允许用户 fander1@'192.168.199.131' 访问 fander 这个库的所有表。

这个授权的信息是存放在 db 表里的，所以我们还必须修改 db 这张表。

update mysql.db set host='192.168.199.132' where host='192.168.199.131';
复制

如果用户里有授权精确到表(table)级的情况，例如

grant select on fander.test to fander1@'192.168.199.131';
复制

这个授权，只允许用户 fander1@'192.168.199.131' 访问 fander.test 这张表。

这个授权的信息是存放在 tables_priv 表里的，所以我们还必须修改 tables_priv 这张表。

update mysql.tables_priv set host='192.168.199.132' where host='192.168.199.131';
复制

以为这就完了？还没有完。。。

如果授权精确到列(columns)级的，例如

mysql> grant select(id),update(mytime) on fander.test to fander1@'192.168.199.131';
复制

mysql> select * from test;ERROR 1142 (42000): SELECT command denied to user 'fander1'@'192.168.199.131' for table 'test'mysql> update test set id=2;ERROR 1143 (42000): UPDATE command denied to user 'fander1'@'192.168.199.131' for column 'id' in table 'test'mysql> select id from test limit 1;+------+| id   |+------+|    1 |+------+1 row in set (0.27 sec)mysql> update test set mytime=now();Query OK, 90264 rows affected (0.32 sec)Rows matched: 90264  Changed: 90264  Warnings: 0
复制

这个授权，只允许用户 fander1@'192.168.199.131' 查询 fander.test 这张表的 id 列，不能查询其他列，并且只能修改 mytime 列。

这个授权的信息是存放在 columns_priv 表里的，所以我们还必须修改 tables_priv 这张表。

update mysql.columns_priv set host='192.168.199.132' where host='192.168.199.131';
复制

上面的所有步骤连起来就是

select user,host from mysql.user;update mysql.user set host='192.168.199.132' where host='192.168.199.131';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';flush privileges;select user,host from mysql.user;
复制

一般来说，这样修改已经差不多了，如果有问题那就要 troubleshoot 问题了。解决的思路就是在 mysql 库里 desc 每张表
，表定义里含 user 和 host 字段的都检查和修改一下。你会发现除了我刚才说的那堆元数据表外还有这两张表涉及了 user、host 字段。

select user,host from mysql.procs_priv;select user,host from mysql.proxies_priv;
复制

在这里我就不深入研究了，供有精力的同学研究和 troubleshooting 备用。

直接修改 MySQL 元数据比较适合 DBA ，一般的运维同学还是尽量别这么搞，出问题跑路时请别贴出我的文章地址打我脸。😄

感谢爱可生开源社区某大佬"余振兴"提醒，其实 MySQL 原生支持修改账号可信 IP 的 DCL 语法，运维同学请采用这种方法，优雅的语法如下:

rename fander1@'192.168.199.132' to fander1@'192.168.199.131';rename peter@'192.168.199.132' to fander@'%';
复制

mysql> show triggers\G*************************** 1. row ***************************             Trigger: trig_test2_2_test3               Event: INSERT               Table: test2           Statement: INSERT INTO fander.test3 VALUES(3,null)              Timing: AFTER             Created: 2021-09-26 14:24:52.30            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION             Definer: fander1@192.168.199.131character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.00 sec)mysql> insert into test2 values(2,null);ERROR 1449 (HY000): The user specified as a definer ('fander1'@'192.168.199.131') does not exist
复制

检查事件（event、event_schedule）

shell> tailf err.log2021-09-26T14:33:28.200944+08:00 828 [ERROR] Event Scheduler: [fander1@192.168.199.131].[fander.test] execution failed, failed to authenticate the user.2021-09-26T14:33:28.200963+08:00 828 [ERROR] Event Scheduler: [fander1@192.168.199.131][fander.test] The user specified as a definer ('fander1'@'192.168.199.131') does not exist2021-09-26T14:33:28.200970+08:00 828 [Note] Event Scheduler: [fander1@192.168.199.131].[fander.test] event execution failed.2021-09-26T14:33:29.201936+08:00 829 [ERROR] Event Scheduler: [fander1@192.168.199.131].[fander.test] execution failed, failed to authenticate the user.2021-09-26T14:33:29.201979+08:00 829 [ERROR] Event Scheduler: [fander1@192.168.199.131][fander.test] The user specified as a definer ('fander1'@'192.168.199.131') does not exist2021-09-26T14:33:29.202002+08:00 829 [Note] Event Scheduler: [fander1@192.168.199.131].[fander.test] event execution failed.
复制

检查存储过程（procedure）

mysql> call count_example_5_8;ERROR 1449 (HY000): The user specified as a definer ('fander1'@'192.168.199.131') does not exist
复制

检查自定义函数（function）

mysql> select genPerson('student');ERROR 1449 (HY000): The user specified as a definer ('fander1'@'192.168.199.131') does not exist
复制

检查视图（views）

mysql> select * from v;ERROR 1449 (HY000): The user specified as a definer ('fander1'@'192.168.199.131') does not exist
复制

• 触发器(trigger)

• 事件(event)

• 存储过程(procedure)

• 自定义函数(function)

• 视图(VIEWS)

不得补充提一下，为了防止“孤立存储对象” 的产生，按官方文档的说法，mysql8.0.22 版本做了改进，如果 drop user、rename user 会产生孤立对象，如果 create user 有可能利用到数据库遗留的孤立对象，这些行为，都会被禁止，会 failed 。以下是官方的原话:

As of MySQL 8.0.22, the server imposes additional account-management security checks designed to prevent operations that (perhaps inadvertently) cause stored objects to become orphaned or that cause adoption of stored objects that are currently orphaned:

• DROP USER
  fails with an error if any account to be dropped is named as the DEFINER
  attribute for any stored object. (That is, the statement fails if dropping an account would cause a stored object to become orphaned.)
• RENAME USER
  fails with an error if any account to be renamed is named as the DEFINER
  attribute for any stored object. (That is, the statement fails if renaming an account would cause a stored object to become orphaned.)
• CREATE USER
  fails with an error if any account to be created is named as the DEFINER
  attribute for any stored object. (That is, the statement fails if creating an account would cause the account to adopt a currently orphaned stored object.)

mysql> rename user user_a to user_b;Query OK, 0 rows affected, 1 warning (0.0060 sec)Warning (code 4005): User 'user_a'@'%' is referenced as a definer account in a stored routine
复制

我基于 mysql8.0.22 和 最新版本 8.0.26 测试，都可以执行成功，只是给出个 warnings ，而没有拒绝执行。

1. 检查

利用 information_schema 库，我们可以快速找到这些需要修改的对象。这里我提供了一些检查语句供大家参考。

#检查存储过程、自定义函数select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,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';
复制

检查发现，我一共需要修改 5 处。

1. 修改

To redefine an object with a different definer, you can use ALTER EVENT or ALTER VIEW to directly modify the DEFINER account of events and views. For stored procedures and functions and for triggers, you must drop the object and re-create it to assign a different DEFINER account

按照官方文档的说法，视图和事件可以通过 ALTER EVENT 或 ALTER VIEW 语法来修改。而存储过程、自定义函数、触发器对象都得删了重新创建。

在我们这次的修改可信 IP 的例子中，我测试发现 (仅供参考)

存储过程、自定义函数、事件可以通过 mysql 库对应的元数据表，直接修改。

# 修改存储过程、自定义函数# 修改后，对新连接的线程生效，已连接的线程请重连update mysql.proc set DEFINER='fander1@192.168.199.132' where DEFINER='fander1@192.168.199.131';# 修改事件# 事件下一次调度时生效 (马上生效)update mysql.event set DEFINER='fander1@192.168.199.132' where DEFINER='fander1@192.168.199.131';
复制

视图需要用 DDL 语法修改。

select concat("alter DEFINER=`fander1`@`192.168.199.132` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where TABLE_SCHEMA !='sys';
复制

先拼接 SQL ，然后执行。

触发器没有很好的方法，只能采用官方的方法删除和重建。

如果能确认，数据库没有使用以上五种对象的话，我给出一种比较稳妥的修改方式。

# 查出要修改为可信IP的账账号，及其加密后的密码mysql> select user,host,authentication_string from mysql.user where host='192.168.199.131';+------------+-----------------+-------------------------------------------+| user       | host            | authentication_string                     |+------------+-----------------+-------------------------------------------+| fander1    | 192.168.199.131 | *7FAD9BF7D3CDD964A197764D9683CAC478CED556 || kexin_ip   | 192.168.199.131 | *7FAD9BF7D3CDD964A197764D9683CAC478CED556 |+------------+-----------------+-------------------------------------------+2 rows in set (0.00 sec)# 创建相同密码的可信IP账号。mysql> create user fander1@'192.168.199.132' identified with mysql_native_password as '*7FAD9BF7D3CDD964A197764D9683CAC478CED556';Query OK, 0 rows affected (0.00 sec)mysql> create user kexin_ip@'192.168.199.132' identified with mysql_native_password as '*7FAD9BF7D3CDD964A197764D9683CAC478CED556';Query OK, 0 rows affected (0.01 sec)# 查看权限，给可信IP账号复制授权mysql> show grants for fander1@'192.168.199.131';+------------------------------------------------------------+| Grants for fander1@192.168.199.131                         |+------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'fander1'@'192.168.199.131' |+------------------------------------------------------------+1 row in set (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'fander1'@'192.168.199.132';Query OK, 0 rows affected (0.01 sec)mysql> show grants for kexin_ip@'192.168.199.131';+------------------------------------------------------+| Grants for kexin_ip@192.168.199.131                  |+------------------------------------------------------+| GRANT USAGE ON *.* TO 'kexin_ip'@'192.168.199.131'   |+------------------------------------------------------+1 row in set (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'kexin_ip'@'192.168.199.132';Query OK, 0 rows affected (0.00 sec)
复制

这种方法是思路是，不修改原账号，先克隆出一个新的可信 IP 账号出来。这个可以提前做。

等需要切换可信 IP 当天，执行锁账号操作，把旧 IP 的账号锁住。

mysql> alter user fander1@'192.168.199.131' account lock;Query OK, 0 rows affected (0.00 sec)mysql> alter user kexin_ip@'192.168.199.131' account lock;Query OK, 0 rows affected (0.00 sec)
复制

观察几天甚至一个月，没有人保障，没有人找你麻烦后，你可以去删除账号了。

mysql> drop user fander1@'192.168.199.131';Query OK, 0 rows affected (0.00 sec)mysql> drop user kexin_ip@'192.168.199.131';Query OK, 0 rows affected (0.00 sec)
复制

这种方法的优点是如果修改有问题，恢复还原较为简单，只需要 account unlock
对应账号即可。

至于为什么我前面提到，要先确认数据库没有以上五个对象的情况下，才用这个方法。因为 account lock 方法只是锁住账号不能登录，里面的账号还是真实存在的，采用了这个账号作为 definer 的存储过程、自定义函数、事件等等依然可以执行成功。这种情况较为复杂，读者可以根据自身情况做决定。

可能会有小伙伴会说，"哎呀! 你这个方法好麻烦啊，既然都没有那五类对象了，还不如直接 update mysql 元数据表来得快啊。"

答: "不好意思，我提出的不是快，也不是优雅，而是一种稳妥的方法。

我认为官方可以考虑这两种做法来解决我提出的问题:

1. 通过 DCL 来做，新增一种 DCL 语法，可以把这些"孤立对象"带走。我做这个操作的时候我甚至能接受暂时性的全实例级别的锁用于保证数据一致性。

rename user user_a to user_b with stored obj;
复制

灵感来源于 grant xxx to user with grant option

为了兼容性，默认执行 rename user user_a to user_b
保持原样，不带走"孤立对象"，并且有 warnings。

1. 通过 sql_mode 来做，新增一种 sql_mode = rename_user_with_stored_obj 来控制此 DCL 语句的行为。默认未添加上，需要时手动添加，可以 session 级别。不新增 SQL 语法的优点是可以保持对别家数据库 SQL 语法的兼容。

一般来说，可以带走所有对象儿子，但我想到一种情况，如果创建对象后，权限被 revoke 过，可能有一些对象儿子就带不走了，得考虑这种错误情况处理。

Enjoy MySQL!

参考文章：