MySQL :: MySQL 8.4 參考手冊 :: 27.6 儲存物件存取控制

版本 8.4

9.0
8.0
5.7

8.0 日文

MySQL 8.4 參考手冊 / 儲存物件 / 儲存物件存取控制

27.6 儲存物件存取控制

儲存程式（程序、函數、觸發程序和事件）和檢視是在使用前定義的，當被引用時，會在決定其權限的安全內容中執行。適用於執行儲存物件的權限由其 DEFINER 屬性和 SQL SECURITY 特性控制。

DEFINER 屬性

儲存物件定義可以包含一個 DEFINER 屬性，該屬性會命名一個 MySQL 帳戶。如果定義省略 DEFINER 屬性，則預設的物件定義者是建立它的使用者。

以下規則決定您可以指定哪些帳戶作為儲存物件的 DEFINER 屬性

如果您擁有 SET_ANY_DEFINER 權限，您可以指定任何帳戶作為 DEFINER 屬性。如果該帳戶不存在，則會產生警告。此外，若要將儲存物件的 DEFINER 屬性設定為具有 SYSTEM_USER 權限的帳戶，您必須具有 SYSTEM_USER 權限。
否則，唯一允許的帳戶是您自己的帳戶，可以字面指定或以 CURRENT_USER 或 CURRENT_USER() 表示。您無法將定義者設定為任何其他帳戶。

建立具有不存在的 DEFINER 帳戶的儲存物件會建立一個孤立物件，這可能會產生負面影響；請參閱孤立的儲存物件。

SQL SECURITY 特性

對於儲存常式（程序和函數）和檢視，物件定義可以包含一個 SQL SECURITY 特性，其值為 DEFINER 或 INVOKER，以指定物件是在定義者還是呼叫者內容中執行。如果定義省略 SQL SECURITY 特性，則預設為定義者內容。

觸發程序和事件沒有 SQL SECURITY 特性，並且始終在定義者內容中執行。伺服器會在必要時自動呼叫這些物件，因此沒有呼叫使用者。

定義者和呼叫者安全內容的差異如下

在定義者安全內容中執行的儲存物件，會以其 DEFINER 屬性所命名的帳戶權限執行。這些權限可能與呼叫使用者的權限完全不同。呼叫者必須具有適當的權限才能參考物件（例如，執行儲存程序的 EXECUTE 或從檢視中選取的 SELECT），但在物件執行期間，會忽略呼叫者的權限，而只有 DEFINER 帳戶權限才重要。如果 DEFINER 帳戶的權限很少，則物件可以執行的操作也會受到相應的限制。如果 DEFINER 帳戶具有很高的權限（例如管理帳戶），則物件可以執行強大的操作，無論誰呼叫它。
在呼叫者安全內容中執行的儲存常式或檢視，只能執行呼叫者具有權限的操作。DEFINER 屬性對物件執行沒有任何影響。

範例

考慮以下儲存程序，它宣告為 SQL SECURITY DEFINER 以在定義者安全內容中執行

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

任何擁有 p1 的 EXECUTE 權限的使用者都可以使用 CALL 陳述式呼叫它。但是，當 p1 執行時，它會在定義者安全內容中執行，因此會以其 DEFINER 屬性命名的帳戶 'admin'@'localhost' 的權限執行。此帳戶必須具有 p1 的 EXECUTE 權限，以及物件主體中引用的表 t1 的 UPDATE 權限。否則，程序會失敗。

現在考慮這個儲存程序，它與 p1 相同，只是它的 SQL SECURITY 特性是 INVOKER

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;

與 p1 不同，p2 在呼叫者安全內容中執行，因此使用呼叫使用者的權限，而不管 DEFINER 屬性值為何。如果呼叫者沒有 p2 的 EXECUTE 權限或表 t1 的 UPDATE 權限，則 p2 會失敗。

孤立的儲存物件

孤立的儲存物件是指其 DEFINER 屬性命名了一個不存在的帳戶的物件

孤立的儲存物件可以透過在物件建立時指定不存在的 DEFINER 帳戶來建立。
現有的儲存物件可以透過執行 DROP USER 陳述式來刪除物件的 DEFINER 帳戶，或透過執行 RENAME USER 陳述式來重新命名物件的 DEFINER 帳戶，從而變成孤立狀態。

孤立的儲存物件可能在以下方面產生問題

由於 DEFINER 帳戶不存在，如果物件在定義者安全內容中執行，則物件可能無法如預期運作
- 對於儲存常式，如果 SQL SECURITY 值為 DEFINER，但定義者帳戶不存在，則會在常式執行時發生錯誤。
- 對於觸發程序，最好不要在帳戶實際存在之前觸發觸發程序。否則，關於權限檢查的行為是未定義的。
- 對於事件，如果帳戶不存在，則會在事件執行時發生錯誤。
- 對於檢視，如果 SQL SECURITY 值為 DEFINER，但定義者帳戶不存在，則在引用檢視時會發生錯誤。
如果不存在的 DEFINER 帳戶隨後為了與物件無關的目的重新建立，則物件可能會產生安全風險。在這種情況下，該帳戶會「採用」該物件，並且在具有適當的權限下，即使不是故意的，也能夠執行該物件。

伺服器會強制執行以下帳戶管理安全檢查，旨在防止（可能無意中）導致儲存物件變成孤立狀態的操作，或導致採用目前孤立的儲存物件的操作

如果任何要刪除的帳戶被命名為任何儲存物件的 DEFINER 屬性，則 DROP USER 會失敗並顯示錯誤。（也就是說，如果刪除帳戶會導致儲存物件變成孤立狀態，則陳述式會失敗。）
如果任何要重新命名的帳戶被命名為任何儲存物件的 DEFINER 屬性，RENAME USER 會失敗並出現錯誤。(也就是說，如果重新命名帳戶會導致儲存物件變成孤立物件，則語句會失敗。)
如果任何要建立的帳戶被命名為任何儲存物件的 DEFINER 屬性，CREATE USER 會失敗並出現錯誤。(也就是說，如果建立帳戶會導致該帳戶採用目前孤立的儲存物件，則語句會失敗。)

在某些情況下，即使這些帳戶管理語句通常會失敗，也可能需要刻意執行它們。為了讓這種情況成為可能，如果使用者擁有 ALLOW_NONEXISTENT_DEFINER 權限，該權限會覆蓋孤立物件安全檢查，並且語句會成功執行並顯示警告，而不是失敗並出現錯誤。

若要取得有關 MySQL 安裝中用作儲存物件定義者的帳戶資訊，請查詢 INFORMATION_SCHEMA。

此查詢會識別哪些 INFORMATION_SCHEMA 表格描述具有 DEFINER 屬性的物件

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
       WHERE COLUMN_NAME = 'DEFINER';
+--------------------+------------+
| TABLE_SCHEMA       | TABLE_NAME |
+--------------------+------------+
| information_schema | EVENTS     |
| information_schema | ROUTINES   |
| information_schema | TRIGGERS   |
| information_schema | VIEWS      |
+--------------------+------------+

結果會告訴您要查詢哪些表格以找出存在哪些儲存物件 DEFINER 值，以及哪些物件具有特定的 DEFINER 值

若要識別每個表格中存在哪些 DEFINER 值，請使用以下查詢
```
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;
```
查詢結果對於以下顯示的任何帳戶都很重要
- 如果帳戶存在，則刪除或重新命名它會導致儲存物件變成孤立物件。如果您計劃刪除或重新命名該帳戶，請考慮先刪除其相關的儲存物件，或將它們重新定義為具有不同的定義者。
- 如果帳戶不存在，則建立它會導致它採用目前孤立的儲存物件。如果您計劃建立該帳戶，請考慮這些孤立物件是否應該與其相關聯。如果不是，請重新定義它們以具有不同的定義者。
若要使用不同的定義者重新定義物件，您可以使用 ALTER EVENT 或 ALTER VIEW 直接修改事件和檢視的 DEFINER 帳戶。對於儲存程序和函數以及觸發程序，您必須刪除物件並重新建立它，才能指派不同的 DEFINER 帳戶

若要識別哪些物件具有給定的 DEFINER 帳戶，請使用以下查詢，並將感興趣的帳戶替換為 user_name@host_name

SELECT EVENT_SCHEMA, EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
WHERE DEFINER = 'user_name@host_name';
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE DEFINER = 'user_name@host_name';
SELECT TRIGGER_SCHEMA, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
WHERE DEFINER = 'user_name@host_name';
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE DEFINER = 'user_name@host_name';

對於 ROUTINES 表格，查詢包括 ROUTINE_TYPE 資料行，以便輸出列區分 DEFINER 是針對儲存程序還是儲存函數。

如果您正在搜尋的帳戶不存在，則這些查詢顯示的任何物件都是孤立物件。

風險最小化指南

若要將儲存物件建立和使用的風險降到最低，請遵循以下指南

請勿建立孤立的儲存物件；也就是說，DEFINER 屬性會命名為不存在的帳戶的物件。請勿透過刪除或重新命名任何現有物件的 DEFINER 屬性命名的帳戶來導致儲存物件變成孤立物件。
對於儲存程序或檢視，請盡可能在物件定義中使用 SQL SECURITY INVOKER，以便只有具有執行物件所執行作業適當權限的使用者才能使用它。
如果您在使用具有 SET_ANY_DEFINER 權限的帳戶時建立定義者內容的儲存物件，請指定一個明確的 DEFINER 屬性，該屬性會命名為僅擁有物件所執行作業所需權限的帳戶。僅在絕對必要時才指定具有高權限的 DEFINER 帳戶。
管理員可以透過不授予使用者 SET_ANY_DEFINER 權限來防止使用者建立指定高權限 DEFINER 帳戶的儲存物件。
定義者內容的物件在撰寫時應牢記，它們可能能夠存取調用使用者沒有權限的資料。在某些情況下，您可以透過不授予未經授權的使用者特定權限來防止對這些物件的參照
- 不具有該儲存程序 EXECUTE 權限的使用者無法參照該儲存程序。
- 不具有該檢視適當權限的使用者無法參照該檢視 (若要從中選取，則需要 SELECT；若要插入其中，則需要 INSERT，依此類推)。
但是，觸發程序和事件不存在此類控制項，因為它們總是會在定義者內容中執行。伺服器會在必要時自動調用這些物件，並且使用者不會直接參照它們
- 觸發程序是透過存取與其關聯的表格來啟動的，即使是沒有特殊權限的使用者對表格的普通存取也會啟動。
- 事件由伺服器按排程執行。
在這兩種情況下，如果 DEFINER 帳戶具有高權限，則該物件可能能夠執行敏感或危險的操作。即使建立物件所需的權限已從建立該物件的使用者帳戶撤銷，情況仍然如此。管理員應特別注意授予使用者物件建立權限。
預設情況下，當執行具有 SQL SECURITY DEFINER 特性的程序時，MySQL Server 不會為 DEFINER 子句中命名的 MySQL 帳戶設定任何使用中角色，只會設定預設角色。例外情況是，如果啟用了 activate_all_roles_on_login 系統變數，在這種情況下，MySQL Server 會設定授予 DEFINER 使用者的所有角色，包括強制角色。因此，當發出 CREATE PROCEDURE 或 CREATE FUNCTION 語句時，預設不會檢查透過角色授予的任何權限。對於儲存的程式，如果應使用與預設不同的角色執行，則程式主體可以執行 SET ROLE 來啟動所需的角色。必須謹慎執行此操作，因為指派給角色的權限可能會變更。

PREV HOME UP NEXT