如何在 MySQL 中使用角色管理權限

指令

以下是我們將討論與管理 MySQL 角色相關的主要 SQL 指令。

• CREATE ROLE：CREATE ROLE 指令在資料庫系統中定義一個新角色。
• DROP ROLE：DROP ROLE 指令的作用相反，它會刪除現有角色。
• GRANT：GRANT 指令有兩個與角色相關的不同用途：將權限新增至角色，以及將使用者帳戶新增為角色成員。
• REVOKE：在角色的上下文中，REVOKE 指令會從角色中移除權限，也會從使用者帳戶中移除角色成員資格。
• SHOW GRANTS：SHOW GRANTS 指令會顯示給定使用者帳戶或角色的權限。
• SET ROLE：SET ROLE 指令會變更使用者帳戶正在主動使用的角色。這讓您可以指定哪些權限集適用於該工作階段的帳戶。
• SET DEFAULT ROLE：SET DEFAULT ROLE 指令定義當用戶端以特定使用者帳戶登入時，自動套用的角色。

必要權限

若要遵循本指南，您需要以下權限

• CREATE ROLE
• GRANT OPTION
• CREATE USER（為另一個使用者設定預設角色）
• ROLE_ADMIN（設定修改角色行為的系統變數）
• SYSTEM_VARIABLES_ADMIN（設定修改角色行為的系統變數）

CREATE ROLE 權限是 CREATE USER 權限的較低版本，允許您建立和管理角色。已經擁有 CREATE USER 權限的帳戶會自動擁有管理角色所需的所有功能。

需要 GRANT OPTION 權限才能將權限指派給角色。您必須為想要指派給角色的任何權限啟用 GRANT OPTION。

MySQL 角色的語法是什麼？

角色名稱必須遵循特定格式，MySQL 才會將其視為有效。在許多方面，它們都反映了用於定義 MySQL 使用者帳戶的格式，但有一些重要的差異。

角色遵循以下格式

'<role>'@'<host>'

與使用者一樣，角色也有兩個組成部分：角色名稱和用戶端連線的主機。但是，MySQL 解釋這些組成部分的方式有所不同。

對於角色，名稱的 '<role>' 部分永遠不能為空。角色沒有像使用者那樣的「匿名」概念。另一方面，省略 '<host>' 部分仍然是被允許的，MySQL 將使用 % 作為主機。但是，此上下文中的 % 被解釋為字面字元，而不是萬用字元。

實際上，這表示雖然角色名稱表面上與使用者帳戶名稱共享格式，但它們不會像使用者帳戶那樣進行任何類型的評估，而只是一個具有兩個組成部分的標籤。它們的名稱確實有兩個部分的原因是，您可以建立既可以用作使用者又可以用作角色的使用者帳戶。當用作使用者時，這些組成部分會受到使用者管理文章中描述的特殊評估規則的約束；當用作角色時，名稱只會使用字面組成部分名稱直接比對。

由於這些規則，在許多情況下，管理員選擇僅使用 '<role>' 組成部分來定義角色。這會導致 MySQL 將字面 % 字元替換為 '<host>' 組成部分，實際上使名稱的該部分不可見且無關緊要。如果您不打算將名稱同時用作使用者帳戶和角色，您可以執行相同的操作。

如何建立角色？

若要建立新角色，請使用 CREATE ROLE 指令。

基本語法如下所示

CREATE ROLE '<role>'@'<host>';

您也可以透過用逗號分隔每個角色名稱來同時建立多個角色

CREATE ROLE '<role_1>'@'<host>', '<role_2>'@'<host>', '<role_3>'@'<host>';

如果您指定的任何角色已存在於系統中，則指令將失敗並出現錯誤。

為了避免這種情況並使 MySQL 僅發出警告，您可以在 CREATE ROLE 指令後、角色名稱前包含 IF NOT EXISTS 子句

CREATE ROLE IF NOT EXISTS '<role>'@'<host>';

如上所述，許多時候管理員會省略角色名稱的 '<host>' 部分以簡化操作，隱式地將其設定為字面 % 字元。因此，在實務中，您的許多角色建立指令可能更像這樣

CREATE ROLE '<role>';

如何自動授予每個使用者某些角色？

有時，您可能希望系統上的每個使用者都具有存取權的角色。您可以透過設定 mandatory_roles 變數來定義每個帳戶自動授予哪些角色。

若要修改 mandatory_roles 變數，您的使用者必須具有 ROLE_ADMIN 和 SYSTEM_VARIABLES_ADMIN 權限。您可以透過輸入以下內容來設定您希望授予每個使用者的角色

SET PERSIST mandatory_roles = '`<role_1>`@`<host>`, `<role_2>`@`<host>`, `<role_3>`@`<host>`';

在這裡，我們自動授予系統上的每個使用者三個角色。在設定系統變數時，mandatory_roles 的值必須是字串，因此我們將整個角色列表封裝在單引號中，並使用反引號來引號個別角色組成部分。

您無法將具有 SYSTEM_USER 權限的角色新增至 mandatory_roles 列表。這是一種安全措施，旨在確保系統上的並非所有工作階段都是自動系統工作階段。

檢視目前作用中的角色

在啟用新角色之前，您可以檢查目前使用者工作階段中作用中的角色。

若要檢視工作階段的作用中角色，請輸入

SELECT CURRENT_ROLE()

輸出將顯示您目前工作階段中作用中的零個或多個角色。與這些角色相關聯的權限將新增到您被允許執行的動作中。

如何為工作階段啟用角色

若要變更工作階段期間作用中的角色，請使用 SET ROLE 指令。您可以使用多種不同的方式使用此指令。

基本語法如下所示

SET ROLE '<rolename>'@'<host>';

這將啟用有問題的角色。請務必注意，SET ROLE 指令中未提及的任何先前作用中的角色現在都將被停用。

若要一次啟用多個角色，請用逗號分隔每個角色

SET ROLE '<role_1>'@'<host>', '<role_2>'@'<host>', '<role_3>'@'<host>';

若要啟用已授予您帳戶的所有角色，您可以指定 ALL 而不是特定角色

SET ROLE ALL;

您也可以告訴 MySQL 啟用您的所有角色，但使用 ALL EXCEPT 排除特定角色

SET ROLL ALL EXCEPT '<role_1>'@'<host>';

另一個選項是透過指定 NONE 來停用您帳戶上的所有角色

SET ROLE NONE

這將停用您使用者角色的工作階段，只讓您擁有專門指派給您使用者帳戶的權限。

若要返回為您的帳戶定義的預設角色列表，請使用 DEFAULT 關鍵字

SET ROLE DEFAULT

如何為使用者帳戶定義預設角色

當您以使用者身分登入時自動啟用的角色，以及當您使用 SET ROLE DEFAULT 時重新啟用的角色，都是可配置的。

若要定義預設會啟用的角色，請使用 SET DEFAULT ROLE 指令，其使用方式與 SET ROLE 指令類似

SET DEFAULT ROLE '<role_1>'@'<host>';

這將設定預設角色，這些角色將在您登入或使用 SET ROLE DEFAULT 時為您自己的帳戶啟用。

如果您的使用者具有 CREATE USER 權限，您可以設定其他帳戶的預設角色

SET DEFAULT ROLE ALL TO '<user>'@'<host>';

在這裡，我們指定 '<user>'@'<host>' 帳戶應在身份驗證時自動啟用其所有角色。

此語法也可以用於定義多個帳戶的預設角色，方法是用逗號分隔每個使用者

SET DEFAULT ROLE ALL TO '<user_1>'@'<host>', '<user_2>'@'<host>';

預設為所有使用者啟用所有角色

如果您希望 MySQL 伺服器上的每個帳戶預設都啟用其所有角色，您可以變更系統設定來執行此操作。

當 activate_all_roles_on_login 變數設定為 true 時，MySQL 會在登入時自動啟用與帳戶相關聯的所有角色。這會取代 SET DEFAULT ROLE 指定的設定。

若要啟用此功能，您必須具有 SYSTEM_VARIABLES_ADMIN 和 ROLE_ADMIN 權限。透過輸入以下內容啟用此功能

SET PERSIST activate_all_roles_on_login = ON;

這將導致使用者帳戶在登入時自動啟用所有角色。但是，SET ROLE DEFAULT 仍允許您僅啟用與帳戶相關聯的預設角色。