mysql存储过程的definer和invoker解析

mysql存储过程的definer和invoker解析2014-10-28【存储过程的权限】

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

案例一：DEFINER

CREATE DEFINER = "admin"@"localhost" PROCEDURE account_count（）

BEGIN

SELECT "Number of accounts:", COUNT（*） FROM mysql.user;

END;

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

本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/

案例二：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_%";