最近在做一个项目，由于服务器切换，所以需要将原有服务器的 mysql 数据表以及存储过程导入到另一个服务器的 mysql 数据库中。导入完成之后以为一切是那么的简单，却没有想到总还是出现了一些莫名其妙的问题。

我在用程序调用存储过程时，总是提示错误：

The user specified as a definer ('test'@'%') does not exist 1449

查看了自己 mysql 的用户表后，发现确实没有 test 这个用户，但是我程序用的是 root 登录的，所以感觉有些莫名其妙。

后来通过查资料发现，是由于自己存储过程设置的安全性为 definer，而当时的那个数据库存在 test 这个用户且用的 test 用户创建的存储过程。

所以解决方法主要有以下两种：

保持 definer 安全性

1）在 navicat 上进行修改

将定义者从 test 改为在该服务器存在的用户（一般每个服务器都有 root@localhost）

2）通过 sql 语句修改

1 mysql>update mysql.proc set DEFINER='root@localhost' WHERE NAME='' AND db='mydb';

其中，mysql.proc 是固定的，definer 即要改为的用户名,name 为存储过程名,db 为数据库名

将安全性修改为 invoker

1）在 navicat 上进行修改

2）通过 sql 语句进行修改

ALTER PROCEDURE proc_name SQL SECURITY INVOKER 2 ALTER PROCEDURE proc_name SQL SECURITY DEFINER

引申阅读：mysql 存储过程的 definer 和 invoker

【用户操作存储过程的权限】

ALTER ROUTINE -- 编辑或删除存储过程

CREATE ROUTINE -- 创建存储过程

EXECUTE          -- 运行存储过程

【存储过程的创建语法】

delimiter //    -- 声明分隔符(命令结束符)

create 

 definer = user@hostname | current_user 

 procedure 存储过程名 (参数)

 comment '注释'

 sql security definer | invoker   -- sql 的安全设置

begin

  存储过程的body

end

//

delimiter ;    -- 声明分隔符(命令结束符)

【函数的创建语句】

delimiter //    -- 声明分隔符(命令结束符)

create

 definer = user@hostname | current_user

 function 函数名(参数)

 return 返回值类型

 comment '注释'

 sql security definer | invoker   -- sql 的安全设置

begin

  函数的body

end
//

delimiter ;    -- 声明分隔符(命令结束符)

【definer 和 invoker 的解释】

   创建存储过程的时候可以指定 SQL SECURITY 属性，设置为 DEFINER 或者 INVOKER，用来告诉 mysql 在执行存储过程的时候，是以 DEFINER 用户的权限来执行，还是以调用者的权限来执行。

   默认情况下，使用 DEFINER 方式，此时调用存储过程的用户必须有存储过程的 EXECUTE 权限，并且 DEFINER 指定的用户必须是在 mysql.user 表中存在的用户。

   DEFINER 模式下，默认 DEFINER=CURRENT_USER，在存储过程执行时，mysql 会检查 DEFINER 定义的用户 'user_name'@'host_name' 的权限；

   INVOKER 模式下，在存储过程执行时，会检查存储过程调用者的权限。

  如果 SQL SECURITY 子句指定为 DEFINER，存储过程将使用存储过程的 DEFINER 执行存储过程，验证调用存储过程的用户是否具有存储过程的 execute 权限和 DEFINER 用户是否具有存储过程引用的相关对象的权限；
  如果 SQL SECURITY 子句指定为 INVOKER，那么 MySQL 将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的 execute 权限和存储过程引用的相关对象的权限；

  案例一：DEFINER

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

   在这个案例中，不论哪个用户 A 调用存储过程，存储过程都会以 'admin'@'localhost' 的权限去执行，即使这个用户 A 没有查询 mysql.user 表的权限。

** 案例二：INVOKER**

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

   在这个案例中，虽然存储过程语句中仍然带有 DEFINER 参数，但是由于 SQL SECURITY 指定了 INVOKER，所以在存储过程执行的时候，会以调用者的额身份去执行。此时这个存储过程是否能成功执行，取决于调用者是否有 mysql.user 表的查询权限。

【案例】

案例一：调用存储过程

 存储过程的调用者是 　 ：　admin@192.168.1.1

 存储过程的 DEFINER 是   ：　admin@%

 MySQL 中存在的用户是　：　admin@192.168.%.%

此时admin@192.168.1.1是可以访问数据库的，因为它符合admin@192.168.%.%
的授权规则，但是当它调用 DEFINER='admin@%' 的存储过程的时候，mysql 会检查 mysql.user 用户表中是否存在 admin@% 这个用户，mysql 的检查结果是 admin@% 这个用户不存在，此时就会返回报错，提示“Ther user specified as a definer ('admin@%') does not exist.。

案例二：创建存储过程

 使用用户admin@192.168.1.1连接mysql，该用户有test库的all privileges，执行创建存储过程的操作：

         存储过程中定义的 DEFINER 是   　：　admin@%

        MySQL中存在的用户是　　　　　：　admin@192.168.%.%

 此时，会遇到报错，提示”ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation“

 修复DEFINER='admin@192.168.%.%'，或者去掉 DEFINER 参数，都可以恢复正常。

说明：

   案例一中是存在问题的，存储过程的调用者和拥有者都是admin@192.168.1.1，但是DEFINER却是admin@%，这是由于创建存储过程的命令是由 root 用户执行的，所以没有遇到案例二中的报错。

【存储过程常用命令】

-- 查看存储过程的创建语句：

show create procedure 存储过程名;

-- 查看存储过程的信息：

show procedure status like '存储过程名'G

-- 查看存储过程的Definer信息：

select db,name,type,sql_security,definer from mysql.proc where  type='PROCEDURE' and db='数据库名' ;

-- 修改存储过程的DEFINER：

update  mysql.proc  set `definer` ='admin@192.168.%.%' where db like 'db_%';