The user specified as a definer does not exist

描述

mysql管理员给调用方创建了一个名为test的用户，并授权了指定host，效果如下：

mysql> select user,host from mysql.user where user = 'test';
+------+------------+
| user | host       |
+------+------------+
| test | 172.17.0.2 |
| test | 172.17.0.3 |
+------+------------+
2 rows in set (0.01 sec)

随后管理员创建了名为test_proc的存储过程，但调用方使用test用户调用存储过程时报如下错误：

mysql> call test_proc;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

复现

先创建名为test的用户，并授权指定host（不要授权%的host权限），再给予限定的SQL执行权限。

-- drop user test@'%';
CREATE USER 'test'@'172.17.0.2' IDENTIFIED BY '123456';
CREATE USER 'test'@'172.17.0.3' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'172.17.0.2';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'172.17.0.3';

创建名为test_proc的存储过程，并通过definer指明该存储过程的调用者权限为test用户。

delimiter ;;
CREATE definer='test' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

查看名为test_proc的存储过程，发现当definer的host值缺省时默认使用的%为host值。

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

definer='test'等效于'test'@'%'

delimiter ;;
CREATE definer='test'@'%' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

最后调用名为test_proc的存储过程，mysql返回异常The user specified as a definer ('test'@'%') does not exist。

mysql> call test_proc;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

解决

create-procedure.html

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
 SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

The procedure is assigned a DEFINER account of ‘admin’@‘localhost’ no matter which user defines it. It executes with the privileges of that account no matter which user invokes it (because the default security characteristic is DEFINER). The procedure succeeds or fails depending on whether invoker has the EXECUTE privilege for it and ‘admin’@‘localhost’ has the SELECT privilege for the mysql.user table.

无论哪个用户执行存储过程，都将以该存储过程的definer定义的用户来执行。由于存储过程创建时definer错误，导致该存储过程指定了一个不存在的用户，因此只需要将该不存在的用户创建出来或者修改存储过程，使其重新指定到一个已存在且有权限的用户即可。

创建缺失用户

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+---------+
| ROUTINE_NAME | DEFINER |
+--------------+---------+
| test_proc    | test@%  |
+--------------+---------+
1 row in set (0.00 sec)

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, EXECUTE ON *.* TO 'test'@'%';

mysql> call test_proc;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

修改DEFINER

为了修改definer，删除原先错误的存储过程，重新创建，并指定definer为当前用户。

mysql> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| test@172.17.0.2 |
+-----------------+
1 row in set (0.00 sec)

drop procedure test_proc;

delimiter ;;
CREATE definer='test'@'172.17.0.2' PROCEDURE test_proc() 
BEGIN 
	select 1;
END ;;
delimiter ;

mysql> select routine_name,definer from information_schema.routines where routine_name='test_proc';
+--------------+-----------------+
| ROUTINE_NAME | DEFINER         |
+--------------+-----------------+
| test_proc    | test@172.17.0.2 |
+--------------+-----------------+
1 row in set (0.01 sec)

mysql> call test_proc;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)