MySQL DEFINER具體使用詳解

前言：

在 MySQL 數(shù)據(jù)庫(kù)中，在創(chuàng)建視圖及函數(shù)的時(shí)候，你有注意過(guò) definer 選項(xiàng)嗎？在遷移視圖或函數(shù)后是否有過(guò)報(bào)錯(cuò)情況，這些其實(shí)都可能和 definer 有關(guān)系。本篇文章主要介紹下 MySQL 中 definer 的含義及作用。

1.DEFINER簡(jiǎn)單介紹

以視圖為例，我們來(lái)看下官方給出的視圖創(chuàng)建基礎(chǔ)語(yǔ)法：

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]

仔細(xì)看上面語(yǔ)法，發(fā)現(xiàn) definer 出現(xiàn)了兩次，一次是 DEFINER = user 一次是 SQL SECURITY 選項(xiàng)可以設(shè)置為 DEFINER 或 INVOKER ，看到這里，你有猜到 definer 的作用了嗎？

definer 翻譯成中文是“定義者”的意思。MySQL中，創(chuàng)建視圖(view)、函數(shù)(function)、存儲(chǔ)過(guò)程(procedure)、觸發(fā)器(trigger)、事件(event)時(shí)，都可以指定 DEFINER = user 選項(xiàng)，即指定此對(duì)象的定義者是誰(shuí)，若不顯式指定，則創(chuàng)建此對(duì)象的用戶就是定義者。

對(duì)于視圖、函數(shù)及存儲(chǔ)過(guò)程，還可以指定 SQL SECURITY 屬性，其值可以為 DEFINER（定義者） 或 INVOKER（調(diào)用者），表示在執(zhí)行過(guò)程中，使用誰(shuí)的權(quán)限來(lái)執(zhí)行。DEFINER 表示按定義者擁有的權(quán)限來(lái)執(zhí)行，INVOKER 表示用調(diào)用者的權(quán)限來(lái)執(zhí)行。

默認(rèn)情況下，SQL SECURITY 屬性為 DEFINER 。其值為 DEFINER 時(shí)，數(shù)據(jù)庫(kù)中必須存在 DEFINER 指定的定義者用戶，并且該定義者用戶擁有對(duì)應(yīng)的操作權(quán)限及引用的相關(guān)對(duì)象的權(quán)限，執(zhí)行者只需擁有調(diào)用權(quán)限就能成功執(zhí)行。當(dāng) SQL SECURITY 屬性為 INVOKER 時(shí)，則需要執(zhí)行者有調(diào)用權(quán)限并且有引用的相關(guān)對(duì)象的權(quán)限，才能成功執(zhí)行。

簡(jiǎn)單來(lái)說(shuō)，假設(shè)一個(gè)視圖查詢了 a b c 三張表，若此視圖的 SQL SECURITY 屬性為 DEFINER ，當(dāng)使用用戶 u 查詢此視圖時(shí)，用戶 u 只需此視圖的查詢權(quán)限即可；若此視圖的 SQL SECURITY 屬性為 INVOKER ，則用戶 u 需要有此視圖的查詢權(quán)限且有 a b c 三張表的查詢權(quán)限。下面通過(guò)示例來(lái)具體演示下：

# 創(chuàng)建兩個(gè)視圖 定義者都是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用戶查詢這兩個(gè)視圖的權(quán)限 來(lái)進(jìn)行查詢測(cè)試
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

# 結(jié)果是view_definer查詢正常，而view_invoker無(wú)法查詢 因?yàn)閡view用戶不具有test_tb表的查詢權(quán)限

自定義函數(shù)及存儲(chǔ)過(guò)程也是類(lèi)似，若 SQL SECURITY 屬性為 INVOKER ，同樣需要調(diào)用者有執(zhí)行權(quán)限并且有引用的相關(guān)對(duì)象的權(quán)限，才能成功執(zhí)行。

2.一些注意事項(xiàng)

額外補(bǔ)充點(diǎn)知識(shí)，只有擁有創(chuàng)建權(quán)限且有 SUPER 權(quán)限的用戶才可以建 DEFINER = 其他用戶的對(duì)象。例如：root 賬號(hào)可以創(chuàng)建 DEFINER = testuser 的視圖，而 testuser 在有創(chuàng)建視圖的前提下只能創(chuàng)建 DEFINER 為自己的視圖。

為了更細(xì)致的了解 DEFINER 相關(guān)作用，以視圖為例再來(lái)說(shuō)幾個(gè)特殊情況下的示例：

假設(shè)用戶 u1 不存在，使用 root 賬號(hào)可以創(chuàng)建 DEFINER = u1 的視圖，若該視圖的 SQL SECURITY 屬性為 DEFINER ，則查詢時(shí)會(huì)報(bào)用戶不存在的錯(cuò)誤，若該視圖的 SQL SECURITY 屬性為 INVOKER ，則使用 root 賬號(hào)可正常查詢?cè)撘晥D。

假設(shè)用戶 u2 存在但不具有查詢表 a 的權(quán)限，使用 root 賬號(hào)可以創(chuàng)建 DEFINER = u2 的視圖來(lái)查詢表 a ，若該視圖的 SQL SECURITY 屬性為 DEFINER ，則查詢時(shí)報(bào)缺少權(quán)限的錯(cuò)誤，若該視圖的 SQL SECURITY 屬性為 INVOKER ，則使用 root 賬號(hào)可正常查詢?cè)撘晥D。當(dāng)使用用戶 u2 登錄時(shí)，則創(chuàng)建視圖來(lái)查詢表 a 會(huì)直接報(bào)錯(cuò)缺少權(quán)限，即創(chuàng)建不了查詢表 a 的視圖，無(wú)論此視圖的 SQL SECURITY 屬性是什么。

看完上述示例后，不清楚你對(duì) DEFINER 是否有了更清晰的認(rèn)識(shí)，有興趣的同學(xué)可以自己測(cè)試看一看。結(jié)合筆者日常經(jīng)驗(yàn)，說(shuō)下 DEFINER 相關(guān)注意事項(xiàng)吧：

• SQL SECURITY 屬性建議使用默認(rèn)的 DEFINER 。
• 某個(gè)庫(kù)內(nèi)的視圖、函數(shù)、存儲(chǔ)過(guò)程建議使用統(tǒng)一的 DEFINER 用戶。
• 不要輕易修改及刪除數(shù)據(jù)庫(kù)用戶，因?yàn)榇擞脩艨赡苁窍嚓P(guān)對(duì)象的定義者。
• 若要修改 SQL SECURITY 屬性，請(qǐng)做好測(cè)試，清楚修改前后的區(qū)別。
• 數(shù)據(jù)庫(kù)遷移時(shí)，要注意新環(huán)境存在相關(guān)對(duì)象的定義者用戶。
• 做數(shù)據(jù)庫(kù)遷移時(shí)，建議首先在新環(huán)境創(chuàng)建相關(guān)用戶及賦予權(quán)限。

總結(jié)：

本篇文章主要介紹了 DEFINER 相關(guān)知識(shí)，這些主要在創(chuàng)建視圖、函數(shù)、存儲(chǔ)過(guò)程等對(duì)象時(shí)會(huì)遇到，平時(shí)比較容易被忽略。但這些細(xì)節(jié)還是應(yīng)該注意的，多了解多學(xué)習(xí)下，這樣到真正用到的時(shí)候可以避免很多錯(cuò)誤。

到此這篇關(guān)于MySQL DEFINER具體使用詳解的文章就介紹到這了,更多相關(guān)MySQL DEFINER內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！