mysql如何修改所有的definer

標籤：

 

mysql中的definer是什麼，有什麼作用？

我們在mysql建立view、trigger、function、procedure、event時都會定義一個Definer=‘xxx’,類似如下：

CREATE    ALGORITHM = UNDEFINED    DEFINER = `root`@`%`    SQL SECURITY DEFINERVIEW `v_ questions` AS    SELECT        `q`.`id` AS `id`,        `q`.`title` AS `title`    FROM         Test q;或者像這樣的：CREATE DEFINER=`root`@`%` PROCEDURE `user_count`()  LANGUAGE SQL  NOT DETERMINISTIC  CONTAINS SQL  SQL SECURITY DEFINER  COMMENT ‘‘BEGIN    select count(*) from mysql.user;END

 

加紅的部分SQL SECURITY 其實後面有兩個選項，一個為DEFINER，一個為INVOKER

SQL SECURITY { DEFINER | INVOKER } ：指明誰有許可權來執行。DEFINER 表示按定義者擁有的許可權來執行

INVOKER 表示用調用者的許可權來執行。預設情況下，系統指定為DEFINER 

 

以預存程序為例：

（1）MySQL預存程序是通過指定SQL SECURITY子句指定執行預存程序的實際使用者；

（2）如果SQL SECURITY子句指定為DEFINER，預存程序將使用預存程序的DEFINER執行預存程序，驗證調用預存程序的使用者是否具有預存程序的execute許可權和DEFINER使用者是否具有預存程序引用的相關對象的許可權；

（3）如果SQL SECURITY子句指定為INVOKER，那麼MySQL將使用當前調用預存程序的使用者執行此過程，並驗證使用者是否具有預存程序的execute許可權和預存程序引用的相關對象的許可權；

（4）如果不顯示的指定SQL SECURITY子句，MySQL預設將以DEFINER執行預存程序。

 

 

我們來看下面幾個小例子。

先授權一個：grant all on testdb.* to ‘user1‘@‘%‘ identified by ‘000000‘ with grant option;    然後我們建立一個預存程序如下：USE `testdb`;DROP procedure IF EXISTS `user_count`;DELIMITER $$USE `testdb`$$CREATE DEFINER=`root`@`%` PROCEDURE `user_count`()  LANGUAGE SQL  NOT DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER  COMMENT ‘‘BEGIN    select count(*) from mysql.user;END$$DELIMITER ;

 

 

用root帳號登陸：

mysql> use testdb;Database changedmysql> call user_count();+----------+| count(*) |+----------+|        3 |+----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)可以正常查詢出來。我們再用user1進行登陸：mysql> use testdb;Database changedmysql> call user_count();ERROR 1142 (42000): SELECT command denied to user ‘user1‘@‘localhost‘ for table ‘user‘

 

發現系統報錯查詢不到了，這是因為我們在上述定義的SQL SECURITY值為INVOKER，預存程序執行過程中會以user1具有的許可權來執行，其中調用到了mysql的庫，而我們的user1帳戶只有testdb庫的使用許可權，所以會返回失敗。

 

我們把上面的invoker改為definer再來試一下：

update mysql.proc set security_type=‘DEFINER‘ where db=‘testdb‘ and name=‘user_count‘;再次用user1進行登陸：mysql> use testdb;Database changedmysql> call user_count();+----------+| count(*) |+----------+|        3 |+----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

 

 

發現可以查詢出來了，因為user1對預存程序user_count有執行的許可權，雖然它依舊沒有許可權直接操作mysql庫，由於我們定義的SQL SECURITY為DEFINER,所以在執行時是以root的身份執行的，所以可以正常查詢出來。

 

如果方便修改mysql中所有已經定義到的definer？

由於前期在測試庫上開發的緣故，我們經常定義到的definer為`root`@`%`，後來搬移到生產庫上又得改回來，存在著大量的更新，上百個的視圖，函數等一個個改不免太麻煩並且也可能遺漏。如下為總結出的方便修改所有definer的方法，可以直到查漏補缺的作用。

現在在mysql涉及的definer有view、trigger、function、procedure、event。我們一個個作介紹。

1.修改function、procedure的definer

select definer from mysql.proc;  -- 函數、預存程序

update mysql.proc set definer=‘[email protected]‘; -- 如果有限定庫或其它可以加上where條件

 

2.修改event的definer

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

update mysql.EVENT set definer=‘ [email protected] ‘;

 

3.修改view的definer

相比function的修改麻煩點：

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<>‘[email protected]‘; 

查詢出來的語句再執行一遍就好了。

 

4.修改trigger的definer

目前還沒有具體方便的方法，可以藉助工具端如HeidiSQL、sqlyog等來一個個修改。注意改前有必要鎖表,因為如果改的過程中有其它表改變而觸發，會造成資料不一致。

Flush tables with readlock

Unlock tables

 

如果有找到方法的方法，記得留言，相互學習。

mysql如何修改所有的definer