程序调用 MySQL 存储过程时出现 execute command denied to user 'xxx'@'%' for routine 'xxx.PROCEDURE' 的异常，这与 MySQL 的用户权限设置有关。

场景复现

在生产环境使用之前的数据库初始化文件导入数据后，一直可以正常使用，在有一次同事做访问权限配置后，程序在不同的地方开始出现下面两种异常：

1
2
3

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

execute command denied to user 'cluster'@'%' for routine 'x86.DELETE_PERFORMANCE_PROC'

同事当时是修改数据库访问权限限制为只有 'cluster'@'172.24.%' 网段的机器才能访问，但是我的机器是属于该网段的，按理说不会出现异常啊，那究竟是什么原因呢？

原因追究

definer 和 invoker

首先搞清楚 definer 是个什么东西，对于我这个平时基本上只用到增删改查的小白来说，这个名字还是比较陌生的，

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

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

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

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

示例

新建一个存储过程：

USE `test1`;
DROP PROCEDURE IF EXISTS `account_count`;
DELIMITER ;;
USE `test1`;;
CREATE DEFINER = 'root'@'localhost' PROCEDURE account_count()
    BEGIN
       SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
    END
;;

DELIMITER ;

查看存储过程的状态

mysql> select db, name, security_type,type, DEFINER from mysql.proc WHERE db='test1'; 

+-------+---------------+---------------+-----------+----------------+
| db    | name          | security_type | type      | DEFINER        |
+-------+---------------+---------------+-----------+----------------+
| test1 | account_count | DEFINER       | PROCEDURE | root@localhost |
+-------+---------------+---------------+-----------+----------------+
1 row in set

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

新建一个用户进行测试：

1
2
3

CREATE USER 'liuhao'@'%' IDENTIFIED BY '123456'; 
GRANT ALL PRIVILEGES ON `test1`.* TO 'liuhao'@'%' IDENTIFIED BY '123456'; 
FLUSH  PRIVILEGES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test1              |
+--------------------+
2 rows in set

mysql> use test1;
Database changed
mysql> call account_count();
+---------------------+----------+
| Number of accounts: | COUNT(*) |
+---------------------+----------+
| Number of accounts: |        7 |
+---------------------+----------+
1 row in set

Query OK, 0 rows affected

发现可以查询出来了，因为 liuhao 对存储过程 account_count 有执行的权限，虽然它依旧没有权限直接操作 mysql 库，由于我们定义的SQL SECURITY 为 DEFINER，所以在执行时是以 root 的身份执行的，所以可以正常查询出来。

虽然 liuhao 这个用户没有访问 mysql 数据库的权限，但是依然可以调用该存储过程，因为它是以定义者 root 用户的权限来调用的。

修改为 invoker 模式

用 root 用户运行：

1	update mysql.proc set security_type='invoker' where db='test1' and name='account_count';

查看存储过程状态

mysql> select db, name, security_type,type, DEFINER from mysql.proc WHERE db='test1'; 

+-------+---------------+---------------+-----------+----------------+
| db    | name          | security_type | type      | DEFINER        |
+-------+---------------+---------------+-----------+----------------+
| test1 | account_count | INVOKER       | PROCEDURE | root@localhost |
+-------+---------------+---------------+-----------+----------------+
1 row in set

再次用测试用户调用

mysql> use test1;
Database changed
mysql> call account_count();
1142 - SELECT command denied to user 'liuhao'@'localhost' for table 'user'

发现系统报错查询不到了，这是因为我们在上述定义的SQL SECURITY 值为 INVOKER，存储过程执行过程中会以 liuhao 具有的权限来执行，其中调用到了 mysql 的库，而我们的 liuhao 帐户只有 test1 库的使用权限，所以会返回失败。

修改已经定义的 definer

由于前期在测试库上开发的缘故，我们经常定义到的 definer 为 cluster@%，后来搬移到生产库上又得改回来，存在着大量的更新，上百个的视图、存储过程、函数等一个个改不免太麻烦并且也可能遗漏。如下为总结出的方便修改所有 definer 的方法，可以直到查漏补缺的作用。

现在在 mysql 涉及的 definer 有 view、trigger、function、procedure、event。我们一个个作介绍。

修改function、procedure的definer

1
2
3

select definer from mysql.proc;  --  函数、存储过程

update mysql.proc set definer='root@localhost' where db='xxx'; -- 如果有限定库或其它可以加上where条件

修改 event 的 definer

1
2
3

select DEFINER from mysql.EVENT; -- 定时事件

update mysql.EVENT set definer='root@localhost';

修改 view 的 definer

相比 function 的修改麻烦点：

1
2
3

select DEFINER from information_schema.VIEWS; 

select concat("alter DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER<>'user@localhost';

查询出来的语句再执行一遍就好了。

修改 trigger 的 definer

目前还没有具体方便的方法，可以借助工具端如 HeidiSQL、sqlyog 等来一个个修改。注意改前有必要锁表，因为如果改的过程中有其它表改变而触发，会造成数据不一致。

1
2
3

Flush tables with readlock

Unlock tables

其他sql

show create procedure DELETE_HOT_EVENT_PROC; -- 查询存储过程创建语句

SHOW PROCEDURE STATUS; -- 查询所有存储过程的状态

SHOW FUNCTION STATUS; -- 查询函数的状态

参考：

生命在于折腾

Mysql 异常：The user specified as a definer ('xxx'@'%') does not exist