• 背景
• 排查原因
• 錯誤提示一的原因
  • definer
  • invoker
  • 錯誤提示二的原因
    • log_bin_trust_function_creators
• 解決方案
  • 錯誤提示一的方案
    • 方案一
    • 方案二
  • 錯誤提示二的方案
• 總結

背景

在AWS RDS環境下，使用mysqldump備份了一個MySQL數據庫數據庫，然后想把它用mysql的命令還原到另外一個數據庫下面，結果在還原的過程中遇到的下面的錯誤提示信息，此時的MySQL實例是沒有開啟binlog。

ERROR 1227 (42000) at line 1163: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

如果在開啟了binlog的情況下，把mysqldump命令備份的SQL文件導入到新的schema下面，則會出現如下的錯誤提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

下面研究了一下出現上面兩個錯誤的原因分別是什么。

排查原因

錯誤提示一的原因

先看第一個錯誤是什么原因導致的

根據第一個錯誤提示信息，我找到了導出來的SQL文件的第1163行，發現這一行的代碼如下所示，在這一行定義了一個trigger觸發器。

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`app_user`@`10.10.%`*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

根據錯誤提示信息，再加上定位到的SQL語句，我猜測是當前我執行導入操作的用戶，沒有權限嗎？

為了驗證我的猜測，我把同樣的SQL文件，在本地的MySQL數據庫中，使用root用戶導入了一次，結果成功了。而使用了AWS RDS for MySQL的管理員用戶想RDS中導入卻失敗，確實是這個權限的問題。

我們都知道AWS RDS中的用戶是沒有root權限的，但是沒有想到它的權限是這么低。RDS中權限最大的用戶就是這個了，如果這個權限都不夠的話，那就沒有任何其他用戶可以用了。只能繼續想辦法看下具體是需要什么樣的權限才可以執行上面的創建觸發器的語句。

我把上面創建觸發器的SQL語句，稍做修改，發現把里面的DEFINER選項/*!50017 DEFINER=app_user@10.10.%*/給去掉就可以創建成功。改為如下的語句來執行是OK的：

DELIMITER ;;
/*!50003 CREATE*/ /*!50003 trigger SEC_RESOURCE_ROLE_DELETE_TS_NN_UPDATE_TRIGGER before update on SEC_RESOURCE_ROLE
            for each row
            begin
                    if not(NEW.DELETE_TS <=> OLD.DELETE_TS) then
                            set NEW.DELETE_TS_NN = if (NEW.DELETE_TS is null, '1000-01-01 00:00:00.000', NEW.DELETE_TS);
                    end if;
            end */;;
DELIMITER ;

這說明是definer的語句導致了上面的觸發器不能正常在MySQL版本的RDS中執行。

在MySQL中，創建函數、存儲過程、視圖、事件、觸發器的時候，可以為其指定definer屬性，但是只能指定執行當前創建函數、存儲過程、視圖、事件、觸發器對象DDL語句的用戶才可以，如果要指定為其他用戶作為definer，則需要使用超級用戶才可以。

definer

這里說明一下definer關鍵字的作用。

definer關鍵字的作用是用來指定當前的函數、存儲過程、視圖、事件、觸發器等數據庫對象是由哪個用戶創建的。這里在指定的時候可以為某一個數據庫對象指定其他用戶作為definer，但前提是擁有super權限的用戶才可以在創建數據庫對象的時候指定其他用戶作為definer，非super權限的用戶，不可以這么做，只能指定它自己作為definer。如果在創建對象的時候沒有顯示的聲明definer，會用當前執行創建對象DDL語句的用戶來作為默認的definer。

如下是定義了definer的存儲過程p1的示例。它使用了SQL SECURITY DEFINER屬性，并且指定了definer為u3@%這個用戶，存儲過程里面是對數據庫procedure_test下面的表t1的counter字段執行加1的一個update操作。也就是說，這個存儲過程p1每被調用一次，數據庫procedure_test下面的表t1的counter字段的值就會被加1。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

/*上面的存儲過程，等價于下面的存儲過程*/
use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p1()
-- SQL SECURITY DEFINER /*可以省略該行，不指定的時候，默認就是使用sql security definer*/
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 1;
END;;
delimiter ;

對于上面的存儲過程p1，不管調用這個存儲過程的用戶是否對procedure_test數據庫下面的表t1是否有select、update的權限(注意：修改一個表，前提是可以查詢表中的數據，查詢出來后才可以修改，所以不僅僅需要update的權限，還需要select的權限)，只要這個用戶在procedure_test數據庫下面有EXECUTE的權限，這個用戶就可以調用procedure_test數據庫下面的p1這個存儲過程。

它是以definer中指定的用戶u3@%來執行這個存儲過程中的命令的。如果definer中定義的用戶u3@%對存儲過程中使用的數據庫對象procedure_test.t1沒有對應的select、update的權限，那么這個調用存儲過程p1的用戶在以definer用戶u3@%去調用該存儲過程的時候，也會失敗。

invoker

說道了definer屬性，就要提一下invoker屬性。

在定義函數、存儲過程、視圖對象的時候，除了可以指定definer屬性之外，還可以為其指定invoker屬性。invoker屬性的含義是，哪個用戶可以調用這個數據庫對象。

當前一個對象沒有在begin前面顯示的聲明SQL SECURITY DEFINER或SQL SECURITY INVOKER的時候，默認是使用SQL SECURITY DEFINER。當一個對象顯示的聲明了SQL SECURITY INVOKER則會覆蓋definer屬性的定義，真正在執行對應的對象的時候，會按照invoker的權限去判斷是否可以執行對應的命令。

如下是一個定義了invoker的存儲過程p2。這個存儲過程，和前面不同的是，這里使用了SQL SECURITY INVOKER屬性。

use procedure_test;
delimiter ;;
CREATE DEFINER = 'u3'@'%' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE procedure_test.t1 SET counter = counter + 100;
END;;
delimiter ;

對于上面的存儲過程p2，雖然定義的時候指定了definer為u3@%，但是由于還指定了SQL SECURITY INVOKER，所以在這個存儲過程被調用的時候，會根據invoker的屬性去判斷是否可以成功調用該存儲過程，忽略definer屬性的約束。這個存儲過程p2是否可以調用成功，取決于調用者是否對數據庫procedure_test下面的t1表擁有select、update的權限。不會判斷調用者對數據庫procedure_test是否有execute權限。

**注意：**觸發器、事件這兩個對象是沒有invoker屬性，不能為其指定哪些用戶可以調用執行它們，它們的調用執行由MySQL自己決定什么時候調用，最多只能為其指定definer屬性，標識是哪個用戶創建的觸發器、事件。其他的幾個像：存儲過程、函數、視圖是可以為其指定invoker屬性的，標識哪個用戶可以調用該對象。

錯誤提示二的原因

接下來我們再看第二個錯誤是什么原因導致的。

根據錯誤二的提示信息，我嘗試使用root用戶在我本地的MySQL中執行導入操作，在開啟binlog的情況下，是可以導入成功的。但是如果使用一個非root用戶，在本地MySQL開啟binlog的情況下， 導入確實會出現下面的錯誤提示：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

錯誤提示給出了關鍵的解決方式：那就通過參數
log_bin_trust_function_creators的配置可以解決這個問題。

log_bin_trust_function_creators

該參數只有在binlog開啟的情況下才會生效，如果binlog沒有開啟，這個參數不管配置成什么值，都不會生效，不起任何作用。所以，接下來我們討論的這個參數配置為off或者on的前提是：binlog已經是開啟的狀態。

參數
log_bin_trust_function_creators的取值范圍是0或1，對應著off或者on，其默認值為off。它是用來控制MySQL是否信任函數、存儲過程、觸發器的創建者所創建的這些數據庫對象。

• off，表示不信任。在創建函數、存儲過程、觸發器之前，MySQL會驗證這些對象是否可以被創建。
• on，表示信任。在創建函數、存儲過程、觸發器之前，MySQL不會去驗證這些對象是否可以被創建，待創建的對象只要語法上沒有問題，就可以創建成功。

那么參數
log_bin_trust_function_creators到底是對待創建的數據庫對象(function、procedure、trigger)做什么驗證呢？

如果待創建的數據庫對象，在定義過程中引用了非確定性因素的函數事件，比如在SQL語句中引用了rand()、uuid()、now()等MySQL內置的函數，就認為這個待創建的數據庫對象是不安全的。因為每次調用或執行這個數據庫對象后，它所產生的結果是不確定的。此時MySQL就認為這個對象是安全的數據庫對象。

• 如果參數log_bin_trust_function_creators=off的時候，MySQL就會對待創建的數據庫對象進行上面我們描述的檢查和驗證。是安全的對象，則可以創建成功，如果是不安全的對象則不能創建成功(擁有超級權限的用戶除外，超級權限的用戶在log_bin_trust_function_creators=off的情況下，即便是非安全的數據庫對象，也可以創建成功)。會拋出一個異常信息如下：

ERROR 1419 (HY000) at line 1163: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

• 如果參數log_bin_trust_function_creators=on的時候，MySQL就不會對待創建的數據庫對象進行安全性的檢查，在語法沒有錯誤的前提下，可以直接創建成功。

題外話：為什么說引用rand()、uuid()、now()等不確定結果函數的數據庫對象就是不安全的呢？

這要從MySQL數據庫binlog和主從復制說起。

我們知道在binlog開啟的情況下，我們對數據庫中數據的任何變更操作都會被記錄在binlog中，從庫在同步主庫的數據的時候，就是讀取主庫中binlog的記錄，然后再從庫在執行一遍，來達到從庫數據庫和主庫數據一致的要求。

但是如果我們調用或執行一些數據庫對象的時候，它們引用了MySQL內置的不確定性的函數如uuid()。那么binlog中記錄的這個操作的SQL語句在不同的MySQL實例上重放的時候，將會得到不同的結果。

例如一個存儲過程中對某個表的某一行進行了修改，其語句為update t set a=uuid() where id = 1;。那么在binlog的記錄格式是statement格式的時候，binlog中就會記錄這樣的一個SQL語句，并不是把調用uuid()之后的得到的數據行修改后的內容記錄在binlog中。那么這樣的binlog在同步到從庫之后，從庫重放binlog中的這樣的記錄時，在從庫上執行的update t set a=uuid() where id = 1;這個SQL語句后的結果得到的uuid()的值，和主庫上的值是相同的可能性幾乎為零。這就導致了從庫上面id=1的數據行，和主庫上面id=1的數據行的內容不一致，進而可能導致主從同步中斷。這就是為什么引用不確定性內置函數的數據庫對象被視為不安全的原因。

如果在binlog格式是row格式的情況下，就不會出現上面我們描述這樣因為不安全的內置函數引用而導致的主從數據不一致的情況，因為row格式的binlog記錄的是id=1這一行數據修改后的數據內容，這樣的binlog在從庫中重放的時候，就直接把修改后的數據內容應用到從庫上，而不是把在主庫上執行是SQL語句在從庫上重新執行一遍。所以，在binlog開啟并且格式為row的前提下，即便是把參數
log_bin_trust_function_creators=on，表示在創建數據庫對象的時候不做驗證，也不會導致主從數據不一致的問題。

如果開啟了binlog之后并且搭建了主從，同時binlog的格式為statement，參數
log_bin_trust_function_creators改為on之后，表示不會對函數、存儲過程、觸發器等對象在創建的時候，進行驗證它們的安全性。就很有可能導致主從數據不一致。

當然，如果沒有開啟binlog，或者開啟了binlog但沒有配置主從同步，只是一個單實例的MySQL服務，設置這個參數為on，不會導致主從不同步。但是如果開啟了binlog，并且格式為statement，在使用binlog做數據恢復還原的時候，也是有可能導致還原后的數據和原先的數據不一致。所以，只要開啟了binlog，格式請一定設置為row格式。

對于binlog開啟與否，以及binlog的格式，主從鏈路的狀態和
log_bin_trust_function_creators參數之間的影響關系如下：

解決方案

錯誤提示一的方案

了解了definer和revoker的作用之后，根據MySQL給拋出來的錯誤信息，結合AWS RDS版本的MySQL用戶是沒有超級用戶的權限的，我們就可以理解為什么使用mysqldump導出來的創建觸發器的語句會帶有definer，并且也能理解為什么導入到新的schema下面就不能導入的原因了。

理解了為什么，我們就可以針對性的進行解決這個問題了。目前的解決方案有以下幾種：

方案一

因為AWS RDS版本的MySQL用戶沒有超級權限，不能指定definer為其他用戶，所以我們可以把這個definer定義給刪除掉。這樣在導入的時候，就會默認使用當前執行導入的用戶來作為該觸發器的definer。

如果你導出來的SQL文件比較小，可以直接雙擊打開編輯或者使用vi命令來編輯，找到對應的definer定義，將其刪除。

如果你導出來的SQL文件比較大，雙擊打開或者使用vi命令編輯基本不可能，那么就使用如下的sed語句可以將函數、存儲過程、視圖、事件、觸發器中的definer屬性給刪除掉。在使用sed命令之前，請先備份你的原來的SQL文件，避免刪除失敗后，不能恢復。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`//g' your_mysqldump_file.sql

方案二

由于AWS RDS版本的MySQL提供的用戶，沒有超級權限，所以它不能創建入觸發器的時候，指定其他用戶作為definer，那么我們可以將dump出來的SQL文件中的definer改為當前RDS提供的用戶。同樣需要備份源SQL文件，給自己留個后悔藥吃。

cp your_mysqldump_file.sql your_mysqldump_file.sql.bak

sed -i -e 's/DEFINER=`app_user`@`10.10.\%`/DEFINER=`your_rds_admin_user`@`\%`/g' your_mysqldump_file.sql

這樣修改后的SQL文件，就可以使用RDS提供的用戶導入到新的schema下面了。

這里在替換為RDS admin的用戶的時候，需要注意，用戶名稱后面是需要跟上%還是跟上具體的某一個網段，則需要根據你的mysql.user表中定義的RDS admin用戶對應的host列中的值是什么。一般情況下面，RDS提供的admin用戶他們的網段都是%，而不是具體的某一個網段。

錯誤提示二的方案

目前我的RDS版本的MySQL是開啟了binlog，并且binlog的格式是row格式，但是我沒有配置主從同步的鏈路。所以，我們要修復前面開始遇到的問題，只需要把參數
log_bin_trust_function_creators由原先默認的off改為on，來開啟信任函數的創建者就可以避免創建觸發器、存儲過程等數據庫對象的驗證了。

修改參數的操作如下：

mysql> set global log_bin_trust_function_creators = on;
Query OK, 0 rows affected (0.00 sec)

修改完成上面的參數后，再重新導入SQL文件，即便是里面有觸發器、函數的創建，也可以創建成功了。不會對這些將要創建的觸發器、函數、存儲過程進行安全性的檢查了。

但是如果需要這個參數長時間生效，需要在MySQL的參數配置文件中增加這個參數的配置。自己安裝部署的MySQL服務，可以修改my.cnf配置文件，如果是RDS版本的MySQL，則需要修改參數組中的這個參數，找到對應的參數，修改后，保存既可以，如果是多個RDS實例，使用同一個參數組，則需要復制出來一份新的參數組來給當前需要修改參數的RDS來使用。為了讓新的參數組生效，則需要重啟MySQL的RDS服務。

總結

這里簡單總結一下這篇文章的內容。

這里，我們主要分析了MySQL中創建函數、存儲過程、觸發器中時候，定義者definer和調用者invoker的使用規則，它們用來控制MySQL數據庫中函數、存儲過程、觸發器等數據庫對象的被調用的時候，哪些用戶可以調用它們。

同時，還分析了參數
log_bin_trust_function_creators的作用，它用來控制在MySQL中創建函數、存儲過程、觸發器等數據庫對象的時候，是否會對這些待創建的數據庫對象進行數據安全性的檢查。