MySQL存储过程执行授权的深入解析（）

项目开发中设计期的创建存储过程的MySQL账号与投产后可能会不同，遇到了错误：

execute command denied to use 'dba'@'192.168.3.100' for routine 'db.proc'

1、Stored Routines and MySQL Privileges

以下是MySQL5.7官方文档：
23.2.2 Stored Routines and MySQL Privileges
The MySQL grant system takes stored routines into account as follows:

The CREATE ROUTINE privilege is needed to create stored routines.

The ALTER ROUTINE privilege is needed to alter or drop stored routines. This privilege is granted automatically to the creator of a routine if necessary, and dropped from the creator when the routine is dropped.

The EXECUTE privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary (and dropped from the creator when the routine is dropped). Also, the default SQL SECURITY characteristic for a routine is DEFINER, which enables users who have access to the database with which the routine is associated to execute the routine.

If the automatic_sp_privileges system variable is 0, the EXECUTE and ALTER ROUTINE privileges are not automatically granted to and dropped from the routine creator.

The creator of a routine is the account used to execute the CREATE statement for it. This might not be the same as the account named as the DEFINER in the routine definition.

The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. Manual manipulation of this table is not supported.

2、测试

proc：

CREATE DEFINER=`dba`@`192.168.3.108` PROCEDURE `proc`(IN `vs` bigint) 
# SQL SECURITY DEFINER / INVOKER
BEGIN
DECLARE vthisok int;
Set vthisok = 1;
select vthisok ;
END

注意：DEFINER=dba@192.168.3.108

GRANT ALL ON `db`.`proc` TO 'dba'@'192.168.3.100'# 理论上，这一句包含了下一句的授权.

GRANT EXECUTE ON PROCEDURE  `db`.`proc` TO 'dba'@'192.168.3.100' # 但是不单独授权就是不能访问！

按照说明文档描述，在服务器 192.168.3.100 没有 GRANT EXECUTE ON PROCEDURE db.proc TO ‘dba’@‘192.168.3.100’ ，理论上应该可以正常执行 call (12312); 实际情况是：execute command denied to use ‘dba’@‘192.168.3.100’ for routine ‘db.proc’。

3、结论

或许是社区班的原因，在开发中尽量避免：Porcedure 非定义者调用的情况，如不可避免：

GRANT EXECUTE ON PROCEDURE  `db`.`proc` TO 'dba'@'192.168.3.100' # 但是不单独授权就是不能访问！

时间关系不做详细讲解，欢迎高手指点。