創建視圖與函數，你注意過 Definer是啥意思嗎
本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。
前言：
在 MySQL 數據庫中，在創建視圖及函數的時候，你有注意過 definer 選項嗎?在遷移視圖或函數后是否有過報錯情況，這些其實都可能和 definer 有關系。本篇文章主要介紹下 MySQL 中 definer 的含義及作用。
1.DEFINER簡單介紹
以視圖為例，我們來看下官方給出的視圖創建基礎語法：
- CREATE
- [OR REPLACE]
- [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
- [DEFINER = user]
- [SQL SECURITY { DEFINER | INVOKER }]
- VIEW view_name [(column_list)]
- AS select_statement
- [WITH [CASCADED | LOCAL] CHECK OPTION]
仔細看上面語法，發現 definer 出現了兩次，一次是 DEFINER = user 一次是 SQL SECURITY 選項可以設置為 DEFINER 或 INVOKER ，看到這里，你有猜到 definer 的作用了嗎?
definer 翻譯成中文是“定義者”的意思。MySQL中，創建視圖(view)、函數(function)、存儲過程(procedure)、觸發器(trigger)、事件(event)時，都可以指定 DEFINER = user 選項，即指定此對象的定義者是誰，若不顯式指定，則創建此對象的用戶就是定義者。
對于視圖、函數及存儲過程，還可以指定 SQL SECURITY 屬性，其值可以為 DEFINER(定義者) 或 INVOKER(調用者)，表示在執行過程中，使用誰的權限來執行。DEFINER 表示按定義者擁有的權限來執行，INVOKER 表示用調用者的權限來執行。
默認情況下，SQL SECURITY 屬性為 DEFINER 。其值為 DEFINER 時，數據庫中必須存在 DEFINER 指定的定義者用戶，并且該定義者用戶擁有對應的操作權限及引用的相關對象的權限，執行者只需擁有調用權限就能成功執行。當 SQL SECURITY 屬性為 INVOKER 時，則需要執行者有調用權限并且有引用的相關對象的權限，才能成功執行。
簡單來說，假設一個視圖查詢了 a b c 三張表，若此視圖的 SQL SECURITY 屬性為 DEFINER ，當使用用戶 u 查詢此視圖時，用戶 u 只需此視圖的查詢權限即可;若此視圖的 SQL SECURITY 屬性為 INVOKER ，則用戶 u 需要有此視圖的查詢權限且有 a b c 三張表的查詢權限。下面通過示例來具體演示下：
- # 創建兩個視圖 定義者都是testuser 查詢的是test_tb表
- mysql> show grants for 'testuser'@'%';
- +------------------------------------------------------------------------------------------------------+
- | Grants for testuser@% |
- +------------------------------------------------------------------------------------------------------+
- | GRANT USAGE ON *.* TO 'testuser'@'%' |
- | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE VIEW, SHOW VIEW ON `testdb`.* TO 'testuser'@'%' |
- +------------------------------------------------------------------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql> show create view view_definer\G
- *************************** 1. row ***************************
- View: view_definer
- Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY DEFINER VIEW `view_definer` AS select `test_tb`.`stu_id` AS `stu_id`,`test_tb`.`stu_name` AS `stu_name` from `test_tb`
- character_set_client: utf8mb4
- collation_connection: utf8mb4_general_ci
- 1 row in set (0.00 sec)
- mysql> show create view view_invoker\G
- *************************** 1. row ***************************
- View: view_invoker
- Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY INVOKER VIEW `view_invoker` AS select `test_tb`.`stu_id` AS `stu_id`,`test_tb`.`stu_name` AS `stu_name` from `test_tb`
- character_set_client: utf8mb4
- collation_connection: utf8mb4_general_ci
- 1 row in set (0.00 sec)
- # 只給uview用戶查詢這兩個視圖的權限 來進行查詢測試
- mysql> select user();
- +-----------------+
- | user() |
- +-----------------+
- | uview@localhost |
- +-----------------+
- 1 row in set (0.00 sec)
- mysql> show grants;
- +--------------------------------------------------------+
- | Grants for uview@% |
- +--------------------------------------------------------+
- | GRANT USAGE ON *.* TO 'uview'@'%' |
- | GRANT SELECT ON `testdb`.`view_definer` TO 'uview'@'%' |
- | GRANT SELECT ON `testdb`.`view_invoker` TO 'uview'@'%' |
- +--------------------------------------------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from view_definer;
- +--------+----------+
- | stu_id | stu_name |
- +--------+----------+
- | 1001 | from1 |
- | 1002 | dfsfd |
- | 1003 | fdgfg |
- +--------+----------+
- 9 rows in set (0.00 sec)
- mysql> select * from view_invoker;
- ERROR 1356 (HY000): View 'testdb.view_invoker' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
- # 結果是view_definer查詢正常，而view_invoker無法查詢 因為uview用戶不具有test_tb表的查詢權限
自定義函數及存儲過程也是類似，若 SQL SECURITY 屬性為 INVOKER ，同樣需要調用者有執行權限并且有引用的相關對象的權限，才能成功執行。
2.一些注意事項
額外補充點知識，只有擁有創建權限且有 SUPER 權限的用戶才可以建 DEFINER = 其他用戶的對象。例如：root 賬號可以創建 DEFINER = testuser 的視圖，而 testuser 在有創建視圖的前提下只能創建 DEFINER 為自己的視圖。
為了更細致的了解 DEFINER 相關作用，以視圖為例再來說幾個特殊情況下的示例：
假設用戶 u1 不存在，使用 root 賬號可以創建 DEFINER = u1 的視圖，若該視圖的 SQL SECURITY 屬性為 DEFINER ，則查詢時會報用戶不存在的錯誤，若該視圖的 SQL SECURITY 屬性為 INVOKER ，則使用 root 賬號可正常查詢該視圖。
假設用戶 u2 存在但不具有查詢表 a 的權限，使用 root 賬號可以創建 DEFINER = u2 的視圖來查詢表 a ，若該視圖的 SQL SECURITY 屬性為 DEFINER ，則查詢時報缺少權限的錯誤，若該視圖的 SQL SECURITY 屬性為 INVOKER ，則使用 root 賬號可正常查詢該視圖。當使用用戶 u2 登錄時，則創建視圖來查詢表 a 會直接報錯缺少權限，即創建不了查詢表 a 的視圖，無論此視圖的 SQL SECURITY 屬性是什么。
看完上述示例后，不清楚你對 DEFINER 是否有了更清晰的認識，有興趣的同學可以自己測試看一看。結合筆者日常經驗，說下 DEFINER 相關注意事項吧：
- SQL SECURITY 屬性建議使用默認的 DEFINER 。
- 某個庫內的視圖、函數、存儲過程建議使用統一的 DEFINER 用戶。
- 不要輕易修改及刪除數據庫用戶，因為此用戶可能是相關對象的定義者。
- 若要修改 SQL SECURITY 屬性，請做好測試，清楚修改前后的區別。
- 數據庫遷移時，要注意新環境存在相關對象的定義者用戶。
- 做數據庫遷移時，建議首先在新環境創建相關用戶及賦予權限。
總結：
本篇文章主要介紹了 DEFINER 相關知識，這些主要在創建視圖、函數、存儲過程等對象時會遇到，平時比較容易被忽略。但這些細節還是應該注意的，多了解多學習下，這樣到真正用到的時候可以避免很多錯誤。
