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

程序调用 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模式下,在存储过程执行时,会检查存储过程调用者的权限。

示例

  • 新建一个存储过程:
1
2
3
4
5
6
7
8
9
10
11
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 ;
  • 查看存储过程的状态
1
2
3
4
5
6
7
8
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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';
  • 查看存储过程状态
1
2
3
4
5
6
7
8
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
  • 再次用测试用户调用
1
2
3
4
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

1
2
3
4
5
show create procedure DELETE_HOT_EVENT_PROC; -- 查询存储过程创建语句

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

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

参考:

  1. http://www.cnblogs.com/zejin2008/p/4767531.html
  2. https://my.oschina.net/u/1424662/blog/485118
hoxis wechat
提供 CSDN 资料免费下载服务,欢迎来撩~
赞赏一杯咖啡
0%