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

于由陈俊聪发布

作者：陈俊聪

数据库管理员，就职于中移信息技术有限公司，从事 MySQL、TiDB、Redis、clickhouse 等开源数据库的维护工作。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景

我们的生产环境访问数据库是通过堡垒机访问的，所以每个 mysql 账号都设置了可信 IP，这个 IP 就是堡垒机器集群所在的网段。现在因为机房搬迁，堡垒机集群需要迁移到新的网段。于是我们需要批量修改 MySQL 上这些账号的网段地址。下面我们研究一下应该怎么修改。

MySQL 是怎么做可信 IP 的访问控制的？

我们知道，Oracle 和 Postgres 上都可以直接配置数据库访问的白名单，一般我们会配置某个具体的可信 IP 或者是一整个可信网段。

例如:

192.168.199.131/32 # 可信IP
192.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 具体IP
create 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 -P3307
mysql: [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)

图中，我使用了 192.168.199.132 这台机器，使用用户 ‘kexin_ip’ 登录数据库我的远程 mysql server 192.168.199.198:3307，因不是可信 IP，登录被拒绝。

提醒，如果密码输错了，远程登录也是报同样的 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 |
+------------+

kexin_subnet 的可信 IP 采用的是网段写法，我们称之为可信网段，是’192.168.199.%’，所以，无论使用 192.168.199.131 还是 192.168.199.132 都能登录数据库服务器。

[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 这种精细化到用户级别的控制。

例如以下两个需求，MySQL 可以实现，但 Oracle 不可以：

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

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

我建议官方修改源代码，支持 host 以逗号隔开写多个 IP。

appuser@'192.168.199.%, 10.200.1.%'

如何修改可信IP？

知道 MySQL 可信 IP 是怎么一回事后，下面我们讨论一下如何修改可信 IP 。

其实修改很简单。假设，我们要把 ‘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: 0

mysql> 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 user fander1@'192.168.199.132' to fander1@'192.168.199.131';
rename user peter@'192.168.199.132' to fander@'%';

真的改好了吗？

我带大家看看我的环境。

检查触发器（trigger）

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.131
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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.log
2021-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 exist
2021-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 exist
2021-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

报错均显示，’fander1’@’192.168.199.131′ 这个账号现在是不存在的，因为他被修改为 ‘fander1’@’192.168.199.132’ 了。按官方的术语来说，我们产生了五类“孤立存储对象”。这些对象共同点是都需要定义 definer ，就是对象调用时使用哪个用户，及其对应权限来运行。

  • 触发器(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.)

遗憾的是，官方说谎了，这里可能有 bug，看看有没有热心读者上官网提个 bug 单。

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 ，而没有拒绝执行。

实践

无论采用修改上述修改元数据方法，或者 MySQL 的原生 rename user 语法修改 mysql 用户可信 IP，均无法把他们身上的五类对象带走。我们除了要修改 mysql.user 元数据表外，还得修改 触发器、事件、存储过程、自定义函数、视图中的 definer 部分。那么如何修改呢？

  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 ，然后执行。

修改后立刻生效。

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

一种比较稳妥的做法

MMySQL 修改可信 IP 白名单比想象中要复杂，尤其是，官方给出的存储过程、自定义函数、触发器对象修改 definer 的方法是需要删除重建，这太麻烦了，这个得停机维护修改啊。
好在我们单位的开发规范要求不使用存储过程、自定义函数、触发器，这样我能省心一些。但是该检查还是得检查，万一开发还是跑去使用了存储过程、自定义函数、触发器呢，对吧。

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

# 查出要修改为可信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 元数据表来得快啊。”

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

感想和给官方的建议

我们并没有打算修改用户、权限、或者密码，但 MySQL 上修改可信 IP 实际上等同于修改了 mysql 用户，导致了情况变复杂，变麻烦。希望日后，官方能提供一种方法，支持单纯修改可信 IP ，操作简单而对实例没有任何影响。

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

  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!

参考文章：

https://dev.mysql.com/doc/refman/5.7/en/stored-objects-security.html

分类： 技术文章
标签：
avatar
100
  Subscribe  
提醒

相关文章

技术文章

故障分析 | MySQL 无法修改主键？原来是因为这个参数

同事咨询了一个问题，TDSQL（for MySQL）中的某张表主键需要 阅读更多…

技术分享

行业趋势 | 2023 年 的 DBA 有哪些变化？

作者：Craig S. Mullins 数据库专家，IBM 优化冠军， 阅读更多…

技术文章

技术译文 | MySQL 8 和 MySQL 5.7 在自增计数上的区别

MySQL 8 和 MySQL 5.7 在自增计数上的区别 作者：Ar 阅读更多…