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模式下，在預存程序執行時，會檢查預存程序調用者的許可權。

案例一：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_%';