导航：首页 > 数据库 >

更改用户host留下的坑

发表于：2024-11-11 作者：千家信息网编辑

千家信息网最后更新 2024年11月11日，前言：我们在创建数据库用户的时候都会指定host，即一个完整的用户可描述为 'username'@'host' 。创建用户时不显式指定host则默认为%，%代表所有ip段都可以使用这个用户，我们也可以

千家信息网最后更新 2024年11月11日更改用户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后，其他用户想调用此对象时不仅需要有该对象的执行权限还要有其他引用到的相关对象的权限，极大的增加了运维复杂性。下面回顾整篇文章，整理出一下几点个人建议，以供大家参考：