MySQL :: MySQL 9.0 參考手冊 :: 8.2.10 使用角色

版本 9.0

8.4 目前版本
8.0

8.0 日文

MySQL 9.0 參考手冊 / ... / 使用角色

8.2.10 使用角色

MySQL 角色是權限的具名集合。與使用者帳戶一樣，角色可以授予和撤銷權限。

使用者帳戶可以被授予角色，這會將與每個角色相關聯的權限授予該帳戶。這使得可以將一組權限指派給帳戶，並提供了一種方便的替代方法來授予個別權限，無論是概念化所需的權限指派還是實作它們。

以下清單總結了 MySQL 提供的角色管理功能

CREATE ROLE 和 DROP ROLE 建立和移除角色。
GRANT 和 REVOKE 指派權限以從使用者帳戶和角色撤銷權限。
SHOW GRANTS 顯示使用者帳戶和角色的權限和角色指派。
SET DEFAULT ROLE 指定預設啟用的帳戶角色。
SET ROLE 變更目前工作階段中啟用的角色。
CURRENT_ROLE() 函數顯示目前工作階段中啟用的角色。
mandatory_roles 和 activate_all_roles_on_login 系統變數可讓您定義強制角色，並在使用者登入伺服器時自動啟用授予的角色。

如需個別角色操作陳述式（包括使用它們所需的權限）的說明，請參閱第 15.7.1 節，「帳戶管理陳述式」。以下討論提供角色用法的範例。除非另有說明，否則此處顯示的 SQL 陳述式應使用具有足夠管理權限的 MySQL 帳戶執行，例如 root 帳戶。

建立角色並將權限授予它們

考量此情境

應用程式使用名為 app_db 的資料庫。
與應用程式關聯的，可能會有建立和維護應用程式的開發人員帳戶，以及與其互動的使用者帳戶。
開發人員需要完全存取資料庫。某些使用者只需要讀取權限，其他使用者則需要讀取/寫入權限。

為了避免個別授予權限給可能眾多的使用者帳戶，請建立角色作為所需權限集的名稱。這使得可以透過授予適當的角色來輕鬆地將所需的權限授予使用者帳戶。

若要建立角色，請使用 CREATE ROLE 陳述式

CREATE ROLE 'app_developer', 'app_read', 'app_write';

角色名稱很像使用者帳戶名稱，並且以 'user_name'@'host_name' 格式組成使用者部分和主機部分。如果省略主機部分，則預設為 '%'。除非使用者和主機部分包含特殊字元（例如 - 或 %），否則可以不使用引號。與帳戶名稱不同，角色名稱的使用者部分不能空白。如需其他資訊，請參閱第 8.2.5 節，「指定角色名稱」。

若要將權限指派給角色，請執行 GRANT 陳述式，其語法與指派權限給使用者帳戶的語法相同。

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

現在假設您一開始需要一個開發人員帳戶、兩個需要唯讀權限的使用者帳戶，以及一個需要讀寫權限的使用者帳戶。請使用 CREATE USER 來建立這些帳戶。

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

若要為每個使用者帳戶指派其所需的權限，您可以使用與先前所示格式相同的 GRANT 陳述式，但這樣需要為每個使用者列舉個別權限。請改用另一種 GRANT 語法，允許授與角色而不是權限。

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

針對 rw_user1 帳戶的 GRANT 陳述式，會授與讀取和寫入角色，它們結合在一起以提供所需的讀取和寫入權限。

將角色授與帳戶的 GRANT 語法與授與權限的語法不同：有一個 ON 子句用於指派權限，而指派角色時則沒有 ON 子句。因為語法不同，您無法在同一個陳述式中混合指派權限和角色。（允許將權限和角色同時指派給一個帳戶，但您必須使用不同的 GRANT 陳述式，每個陳述式都使用適用於要授與內容的語法。）無法將角色授與匿名使用者。

角色在建立時會被鎖定，沒有密碼，並且會指派預設的驗證外掛程式。（這些角色屬性稍後可以使用 ALTER USER 陳述式，由擁有全域 CREATE USER 權限的使用者變更。）

在鎖定狀態下，角色不能用於向伺服器進行驗證。如果角色已解除鎖定，則可以使用它進行驗證。這是因為角色和使用者都是授權識別碼，它們有很多共同點，並且幾乎沒有區別。另請參閱使用者和角色可互換性。

定義強制角色

可以透過在 mandatory_roles 系統變數的值中命名角色來將其指定為強制角色。伺服器會將強制角色視為已授與給所有使用者，因此不需要明確地將其授與任何帳戶。

若要在伺服器啟動時指定強制角色，請在您的伺服器 my.cnf 檔案中定義 mandatory_roles。

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

若要在執行階段設定並持久化 mandatory_roles，請使用如下的陳述式

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

SET PERSIST 會為正在執行的 MySQL 執行個體設定一個值。它也會儲存該值，使其在後續的伺服器重新啟動時保留。若要變更正在執行的 MySQL 執行個體的值，而不使其在後續重新啟動時保留，請使用 GLOBAL 關鍵字，而不是 PERSIST。請參閱第 15.7.6.1 節「用於變數指派的 SET 語法」。

設定 mandatory_roles 需要 ROLE_ADMIN 權限，以及通常設定全域系統變數所需的 SYSTEM_VARIABLES_ADMIN 權限（或已棄用的 SUPER 權限）。

強制角色（如明確授與的角色）在啟用之前不會生效（請參閱啟動角色）。在登入時，如果啟用 activate_all_roles_on_login 系統變數，則會為所有已授與的角色執行角色啟動，否則會為設定為預設角色的角色執行角色啟動。在執行階段，SET ROLE 會啟動角色。

無法使用 REVOKE 撤銷或使用 DROP ROLE 或 DROP USER 移除在 mandatory_roles 值中命名的角色。

若要防止工作階段預設成為系統工作階段，具有 SYSTEM_USER 權限的角色不能列在 mandatory_roles 系統變數的值中。

如果 mandatory_roles 在啟動時被指派一個具有 SYSTEM_USER 權限的角色，伺服器會將訊息寫入錯誤日誌並結束。
如果 mandatory_roles 在執行階段被指派一個具有 SYSTEM_USER 權限的角色，則會發生錯誤，並且 mandatory_roles 值保持不變。

即使有這個安全措施，最好還是避免透過角色授與 SYSTEM_USER 權限，以防止權限提升的可能性。

如果 mandatory_roles 中命名的角色不存在於 mysql.user 系統資料表中，則不會將該角色授與使用者。當伺服器嘗試為使用者啟動角色時，它不會將不存在的角色視為強制角色，並且會將警告寫入錯誤日誌。如果角色稍後建立並因此變為有效，則可能需要 FLUSH PRIVILEGES 才能使伺服器將其視為強制角色。

SHOW GRANTS 會根據第 15.7.7.22 節「SHOW GRANTS 陳述式」中描述的規則顯示強制角色。

檢查角色權限

若要驗證指派給帳戶的權限，請使用 SHOW GRANTS。例如

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

但是，這會顯示每個已授與的角色，而不會將其「展開」為角色代表的權限。若要同時顯示角色權限，請新增一個 USING 子句，命名要顯示權限的已授與角色。

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

以類似的方式驗證其他每個類型的使用者。

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

SHOW GRANTS 會根據第 15.7.7.22 節「SHOW GRANTS 陳述式」中描述的規則顯示強制角色。

啟動角色

授與給使用者帳戶的角色可以在帳戶工作階段中處於啟用或停用狀態。如果授與的角色在工作階段中處於啟用狀態，則其權限會適用；否則，權限不會適用。若要判斷目前工作階段中哪些角色處於啟用狀態，請使用 CURRENT_ROLE() 函式。

預設情況下，將角色授與帳戶或在 mandatory_roles 系統變數值中命名它並不會自動導致該角色在帳戶工作階段中變成啟用狀態。例如，因為在先前的討論中，到目前為止尚未啟用任何 rw_user1 角色，如果您以 rw_user1 的身分連線至伺服器並呼叫 CURRENT_ROLE() 函式，結果會是 NONE（沒有啟用角色）。

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

若要指定每次使用者連線至伺服器並驗證身分時應啟用哪些角色，請使用 SET DEFAULT ROLE。若要將預設值設定為先前建立的每個帳戶的所有指派角色，請使用此陳述式：

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';

現在，如果您以 rw_user1 的身分連線，CURRENT_ROLE() 的初始值會反映新的預設角色指派：

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

若要使所有明確授與和強制角色在使用者連線至伺服器時自動啟用，請啟用 activate_all_roles_on_login 系統變數。預設情況下，會停用自動角色啟用。

在工作階段中，使用者可以執行 SET ROLE 來變更啟用角色的集合。例如，對於 rw_user1：

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

第一個 SET ROLE 陳述式會停用所有角色。第二個陳述式會使 rw_user1 實際上變成唯讀。第三個陳述式會還原預設角色。

儲存程式和檢視物件的有效使用者會受制於 DEFINER 和 SQL SECURITY 屬性，這些屬性會決定執行是在呼叫者還是定義者環境中發生（請參閱第 27.7 節「儲存物件存取控制」）。

在呼叫者環境中執行的儲存程式和檢視物件會使用目前工作階段中啟用的角色來執行。
在定義者環境中執行的儲存程式和檢視物件會使用在其 DEFINER 屬性中命名的使用者的預設角色來執行。如果啟用 activate_all_roles_on_login，則此類物件會使用授與給 DEFINER 使用者的所有角色（包括強制角色）來執行。對於儲存程式，如果執行應該使用與預設不同的角色，則程式主體可以執行 SET ROLE 來啟用所需的角色。必須小心地執行此操作，因為指派給角色的權限可能會變更。

撤銷角色或角色權限

就像可以將角色授與帳戶一樣，也可以從帳戶撤銷角色。

REVOKE role FROM user;

無法撤銷在 mandatory_roles 系統變數值中命名的角色。

REVOKE 也可以應用於角色，以修改授與給該角色的權限。這不僅會影響角色本身，也會影響被授與該角色的任何帳戶。假設您要暫時將所有應用程式使用者設為唯讀。若要執行此操作，請使用 REVOKE 從 app_write 角色撤銷修改權限。

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

事實上，這會導致該角色完全沒有任何權限，如使用 SHOW GRANTS 所見（這顯示此陳述式可用於角色，而不僅限於使用者）。

mysql> SHOW GRANTS FOR 'app_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `app_write`@`%` |
+---------------------------------------+

因為撤銷角色的權限會影響任何被指派該修改角色的使用者的權限，rw_user1 現在沒有任何表格修改權限（INSERT、UPDATE 和 DELETE 不再存在）。

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+

實際上，rw_user1 讀寫使用者已變為唯讀使用者。這也會發生在任何其他被授予 app_write 角色的帳戶上，說明了使用角色如何使其無需修改個別帳戶的權限。

要將修改權限還原給角色，只需重新授予它們即可。

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

現在 rw_user1 再次擁有修改權限，任何其他被授予 app_write 角色的帳戶也是如此。

刪除角色

要刪除角色，請使用 DROP ROLE。

DROP ROLE 'app_read', 'app_write';

刪除角色會從每個被授予該角色的帳戶撤銷該角色。

在 mandatory_roles 系統變數值中命名的角色無法刪除。

使用者和角色的互換性

正如先前在 SHOW GRANTS 中暗示的，該陳述式會顯示使用者帳戶或角色的授權，帳戶和角色可以互換使用。

角色和使用者之間的一個區別是，CREATE ROLE 會建立一個預設為鎖定的授權識別碼，而 CREATE USER 會建立一個預設為未鎖定的授權識別碼。您應該記住，這種區別不是不可變的；具有適當權限的使用者可以在角色或（其他）使用者建立後鎖定或解除鎖定它們。

如果資料庫管理員偏好特定的授權識別碼必須是角色，則可以使用命名方案來傳達此意圖。例如，您可以為所有您打算作為角色的授權識別碼使用 r_ 前綴，而不用於其他用途。

角色和使用者之間的另一個區別在於管理它們的可用權限。

CREATE ROLE 和 DROP ROLE 權限僅分別允許使用 CREATE ROLE 和 DROP ROLE 陳述式。
CREATE USER 權限允許使用 ALTER USER、CREATE ROLE、CREATE USER、DROP ROLE、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES 陳述式。

因此，CREATE ROLE 和 DROP ROLE 權限不如 CREATE USER 強大，並且可以授予給僅應允許建立和刪除角色，而不應執行更一般帳戶操作的使用者。

關於使用者和角色的權限和互換性，您可以將使用者帳戶視為角色，並將該帳戶授予其他使用者或角色。效果是將帳戶的權限和角色授予其他使用者或角色。

這組陳述式示範了您可以將使用者授予使用者、將角色授予使用者、將使用者授予角色或將角色授予角色。

CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

在每種情況下，結果都是將與被授予的物件關聯的權限授予被授予的物件。執行這些陳述式之後，u2 和 r2 都已從使用者 (u1) 和角色 (r1) 獲得了權限。

mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+

前面的範例僅供說明，但使用者帳戶和角色的互換性具有實際應用，例如在以下情況中：假設一個舊版應用程式開發專案在 MySQL 中出現角色之前就開始了，因此與該專案關聯的所有使用者帳戶都直接被授予權限（而不是透過被授予角色來獲得權限）。其中一個帳戶是開發人員帳戶，該帳戶最初被授予的權限如下：

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

如果此開發人員離開專案，則有必要將權限指派給其他使用者，或者如果開發活動擴展，則可能指派給多個使用者。以下是一些解決此問題的方法：

不使用角色的情況：變更帳戶密碼，使原始開發人員無法使用它，並讓新的開發人員改為使用該帳戶。
```
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';
```
使用角色的情況：鎖定帳戶以防止任何人使用它連線到伺服器。
```
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
```
然後將該帳戶視為角色。對於每個新加入專案的開發人員，建立一個新帳戶並將原始開發人員帳戶授予給它。
```
CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
```
這樣做的效果是將原始開發人員帳戶的權限指派給新帳戶。