MySQL :: MySQL 9.0 參考手冊 :: 27.7 儲存物件存取控制

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

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

27.7 儲存物件存取控制

儲存程式（程序、函數、觸發器和事件）和視觀表會在使用前定義，並在被參照時，於決定其權限的安全性環境中執行。適用於執行儲存物件的權限由其 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 會在呼叫者沒有 p2 的 EXECUTE 權限或資料表 t1 的 UPDATE 權限時失敗。

孤立的儲存物件

孤立的儲存物件是指其 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