MySQL 更改用户host留下的坑

前言：

我们在创建数据库用户的时候都会指定host，即一个完整的用户可描述为 ‘username’@’host’ 。创建用户时不显式指定host则默认为%，%代表所有ip段都可以使用这个用户，我们也可以指定host为某个ip或ip段，这样会仅允许在指定的ip主机使用该数据库用户。不过你也应该明白 ‘username’@’%’ 和 ‘username’@’192.168.6.%’ 是两个毫无关联的用户，这两个用户可以有不同的密码和权限，这里不建议创建多个同名不同host的用户，还有不要轻易更改用户的host，笔者曾经遇到过因为更改用户host引发的故障，下面将其分享出来，为你讲述前因后果。

1.故障模拟

当时为了规范安全，将某个程序用户的host由%改为了应用服务器ip段，过段时间业务反馈某些功能报错，经排查发现是因为无法调用存储过程（大家可以先思考下原因），下面模拟下故障操作。

# 原有用户、表、存储过程模拟创建
mysql> create user 'testuser'@'%' identified by '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> grant select,insert,update,delete,execute on `testdb`.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'testuser'@'%';
+-------------------------------------------------------------------------------+
| Grants for testuser@%                                                         |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testdb`.* TO 'testuser'@'%' |
+-------------------------------------------------------------------------------+

CREATE TABLE `students` (
 `id` int(11) NOT NULL ,
 `name` varchar(20),
 `age` int(11),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
INSERT INTO `students` VALUES ('1001', 'lodd', '23');
INSERT INTO `students` VALUES ('1002', 'sdfs', '21');
INSERT INTO `students` VALUES ('1003', 'sdfsa', '24');

DROP PROCEDURE IF EXISTS select_students_count;
DELIMITER $$
CREATE DEFINER=`testuser`@`%` PROCEDURE `select_students_count`()
BEGIN
   SELECT count(id) from students;
END
$$
DELIMITER ;

# 使用testuser用户调用存储过程 调用正常
mysql> call select_students_count();
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+

# 更改用户host 重命名用户
mysql> RENAME USER 'testuser'@'%' to 'testuser'@'192.168.6.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for  'testuser'@'192.168.6.%';
+---------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                         |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost'                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `testdb`.* TO 'testuser'@'localhost' |
+---------------------------------------------------------------------------------------+

# 再次用testuser用户调用存储过程 无法调用 出现故障
mysql> call select_students_count();
ERROR 1449 (HY000): The user specified as a definer ('testuser'@'%') does not exist

2.故障排查与解决

其实我们手动调用下存储过程后，从报错内容明显可以看出是因为’testuser@’%’用户不存在的问题。因为该存储过程的定义者是’testuser@’%’，而我们将此用户的host改成了192.168.6.%，那么当我们之后调用该存储过程时，系统判别到此存储过程的属主用户不存在，因此系统拒绝请求并抛出异常。

当知道上述原因后，解决方法就会明朗许多，我们只需要将该存储过程的属主改为新的用户即可。其实更改过用户后，该用户下的视图、存储过程、函数、触发器、事件都会受到影响，当我们定义视图、存储过程、函数时使用 DEFINER 属性时，若调用这些对象，系统会首先判别此对象的属主用户是否存在，不存在会直接抛出错误。

此问题的解决方案有两种，一是将此存储过程的安全属性由 DEFINER 改为 INVOKER ，个人不推荐这个方案，至于 DEFINER 和 INVOKER 的区别，下个章节会额外讲解。二是更改此存储过程的属主，下面给出更改方法并加以验证：

# 通过系统表更改存储过程的属主
mysql> update mysql.proc set definer='testuser@192.168.6.%' where db='testdb' and name='select_students_count' and type='PROCEDURE';           
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 使用testuser用户调用验证 调用成功
mysql> call select_students_count();
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

3.DEFINER与INVOKER拓展知识

MySQL中，创建视图(view)、函数(function)、存储过程(procedure)、触发器(trigger)、事件(event)时，可以指定安全验证方式(也就是SQL SECURITY)属性，其值可以为DEFINER或INVOKER，表示在执行过程中，使用谁的权限来执行。

DEFINER：由definer(定义者)指定的用户的权限来执行
INVOKER：由调用这个视图(存储过程)的用户的权限来执行

默认情况下，系统指定为DEFINER。当SQL SECURITY属性为DEFINER时，数据库中必须存在DEFINER指定的用户，并且该用户拥有对应的操作权限及引用的相关对象的权限，才能成功执行。与当前用户是否有权限无关。当SQL SECURITY属性为INVOKER时，只要执行者有执行权限并且有引用的相关对象的权限，就可以成功执行。

了解了上述知识后，可能你早已明白上述故障发生的前因后果。在日常生产中，不建议使用INVOKER属性，因为将SQL SECURITY定义为INVOKER后，其他用户想调用此对象时不仅需要有该对象的执行权限还要有其他引用到的相关对象的权限，极大的增加了运维复杂性。下面回顾整篇文章，整理出一下几点个人建议，以供大家参考：

不创建多个同名不同host的用户。
不要轻易更改用户的host。
更改用户host请用RENAME USER语句，直接更新mysql.user系统表中的host属性会使权限丢失。
更改用户host后，要注意此用户下的各个对象的DEFINER属性。
创建视图、存储过程等对象建议将SQL SECURITY定义为DEFINER。
数据库迁移时，要注意新环境存在相关对象定义的DEFINER用户。

总结：

本文从一个故障出发，详细记录了故障发生的原因及背后涉及的知识，其实像DEFINER属性这些细节类的东西很容易被忽视，只有遇到问题了我们才会去探究。希望本篇文章能让你学到新东西，特别是上面总结的几点建议都是笔者日常运维总结出的。原创不易，请大家多多支持！

上次更新时间 20 3 月, 2023 at 09:33 上午

1.故障模拟

2.故障排查与解决

3.DEFINER与INVOKER拓展知识

江明涛