如何在 MySQL 中使用 `GRANT` 和 `REVOKE` 管理權限

我們將使用的命令

在本指南中，我們將使用的最重要的命令是 GRANT 和 REVOKE 命令

• GRANT：用於將新權限指派給使用者帳戶
• REVOKE：用於從使用者帳戶中移除現有權限

所需權限

要管理 MySQL 使用者的權限，您需要擁有以下權限

• GRANT OPTION：GRANT OPTION 權限可讓您授予或撤銷您已被授予的任何權限
• 您希望指派給其他使用者的任何權限
• SELECT on mysql.*：用於為其他帳戶執行 SHOW GRANTS

為了遵循本指南，我們將假設您正在使用具有完整管理權限（包括 GRANT OPTION 權限）的帳戶。這可能是安裝期間設定的常見 'root'@'localhost' 使用者，或任何其他具有完整權限的使用者。

MySQL 中有哪些權限可用？

MySQL 定義了許多適用於各種系統範圍的權限。其中一些對於日常使用和管理資料庫、表格和函式很有用，而另一些則專為管理任務而設計，例如複寫、備份和連線管理。

您可以在 MySQL 文件中的GRANT 和 REVOKE 表格允許的靜態權限中找到靜態權限（內建於 MySQL 本身的核心權限）及其各自範圍的完整列表。MySQL 文件的相關靜態權限描述章節提供了每個權限允許的功能的詳細概述，並在許多情況下，提供了關於哪些情境最適合使用它們的指導。

動態權限是另一種權限類型。動態權限在外掛程式或組件中定義，並在 MySQL 中註冊以啟用它們。它們始終是全域範圍的，並提供額外的功能或特性。MySQL 文件中的GRANT 和 REVOKE 表格允許的動態權限列出了每個動態權限及其上下文。您可以在 MySQL 文件的相關動態權限描述章節中找到每個權限用途的完整描述。

要找出您的 MySQL 伺服器上啟用和可用的權限，以及它們相關的上下文，您可以使用以下命令

SHOW PRIVILEGES

這可以幫助您了解哪些權限最適合使用者的職責。

基本語法

指派權限的 GRANT 命令的基本語法相當簡單。它遵循以下格式

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

可以提供多個權限，以逗號分隔。

目標資料庫、表格、欄位等

上面語法中的 <database>.<object> 部分決定了將授予權限的範圍。這將決定權限將針對哪些物件授予，以及 mysql 資料庫中將記錄新權限的特定表格。

要全域授予權限，允許使用者帳戶在整個系統中使用該權限，請對範圍組件的資料庫和資料庫物件部分都使用萬用字元

例如，要為 'sally'@'localhost' 全域授予 SELECT 權限，您將輸入

GRANT SELECT ON *.* TO 'sally'@'localhost';

要將授權範圍限制為單個資料庫，請將點左側的萬用字元替換為資料庫名稱

GRANT SELECT ON accounting.* TO 'meredith'@'localhost';

如果帳戶只需要存取資料庫中的單個表格，請在點的右側指定表格名稱

GRANT UPDATE ON accounting.revenue TO 'frank'@'localhost';

最後，將權限應用於特定欄位遵循稍微不同的格式。當範圍設定為欄位層級時，您必須在權限名稱後面的括號中提供應套用權限的欄位。

例如，要授予更新 library.loans 表格中 due_by 欄位值的權限，您可以輸入

GRANT UPDATE (due_by) ON library.loans TO 'autorenew'@'localhost';

使用 WITH GRANT OPTION 子句

可以將一個名為 WITH GRANT OPTION 的附加子句附加到 grant 語句中，以允許使用者帳戶在特定範圍內管理其他使用者的授權。您不僅僅是將權限授予使用者，還授予該使用者將其在相同範圍內擁有的任何權限傳遞給其他使用者的能力。

例如，在這裡，我們可以給予 'librarymanager'@'localhost' 帳戶 SELECT、INSERT、UPDATE 和 DELETE 權限，以及將其在 library 資料庫中的權限傳遞給其他使用者的能力

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost' WITH GRANT OPTION;

重要的是要意識到 WITH GRANT OPTION 子句適用於帳戶 ('librarymanager'@'localhost') 和範圍 (library.*)，**而不是**語句中的特定權限。這表示儘管我們在此語句中為 'librarymanager'@'localhost' 帳戶指派了四個新權限，但 WITH GRANT OPTION 允許它傳遞其在 library.* 範圍內的**任何**權限。由於該帳戶現在擁有此範圍的 GRANT OPTION，如果我們將來為 'librarymanager'@'localhoast' 提供額外權限，它也將能夠自動傳遞這些權限。

儘管您可以如上所示使用 WITH GRANT OPTION 子句來允許帳戶在您授予他們額外權限時傳遞其權限，但如果您將這兩個操作分開，通常會更清晰，如下所示

GRANT SELECT,INSERT,UPDATE,DELETE ON library.* TO 'librarymanager'@'localhost';
GRANT GRANT OPTION ON library.* TO 'librarymanager'@'localhost';

當您將 GRANT OPTION 作為常規權限處理時，您也可以將其合併到您要指派的權限列表中

GRANT SELECT,INSERT,UPDATE,DELETE,GRANT OPTION ON library.* TO 'librarymanager'@'localhost';

在任何這些情況下，結果都是 'librarymanager'@'localhost' 帳戶將能夠將其針對 library 資料庫擁有的任何權限（現在和將來）授予其他使用者。這使得 GRANT OPTION 權限在不小心指派時尤其危險，因為它可能允許使用者授予帳戶管理員不希望擁有的額外權限。

如何授予使用者完整存取權？

通常，您希望為特定使用者指派對資料庫或資料庫組件的完整所有權。例如，您的 sales 資料庫可能有一個特定的使用者被指定來管理其中的表格、函式和索引。

您可以使用 ALL 或 ALL PRIVILEGES 簡寫在特定範圍內為使用者指派完整權限

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost';

這將授予您的使用者能夠在 sales 資料庫上指派的每個權限給 'salesadmin'@'localhost' 使用者，但有幾個重要的例外。ALL PRIVILEGES 權限包不包含 GRANT OPTION 或 PROXY 權限，這兩個權限必須單獨指派。這是為了更容易指派完整權限，而無需傳遞權限管理和使用者替換權限。

要全域指派除了 GRANT OPTION 和 PROXY 之外的所有權限，請使用 *.* 範圍

GRANT ALL PRIVILEGES ON *.* TO 'systemadmin'@'localhost';

如何授予使用者完整存取權，包括權限管理？

要指派完整權限，並讓使用者能夠傳遞其任何權限，請在語句中包含 GRANT OPTION。例如，要讓上一個範例中的 'salesadmin'@'localhost' 帳戶能夠控制其他使用者對 sales 資料庫的存取權，您可以改為輸入

GRANT ALL PRIVILEGES ON sales.* TO 'salesadmin'@'localhost' WITH GRANT OPTION;

然後，該帳戶不僅將擁有對 sales 資料庫的完整存取權，還能夠決定其他使用者可以在資料庫上執行哪些操作。

相同的邏輯可以應用於使用 *.* 上下文的全域。在這種情況下，它會使給定的帳戶成為完整的管理使用者

GRANT ALL PRIVILEGES ON *.* TO 'fulladmin'@'localhost' WITH GRANT OPTION;

如何授予使用者唯讀存取權？

通常，在資料庫或表格層級，您會有一些帳戶需要能夠存取資訊，但不應有能力以任何方式更改資料庫或物件。這些可能包括報表工具或任何需要存取資料但不可修改的情境，例如許多非互動式網頁。

SELECT 權限足以讓使用者在資料庫或物件上擁有唯讀權限。要給予 'salesreport'@'localhost' 使用者對 sales 資料庫的唯讀存取權，請輸入

GRANT SELECT ON sales.* TO 'salesreport'@'localhost';

此使用者將能夠查詢和提取其從 sales 資料庫所需的任何資料，但它無法進行任何更改。

與往常一樣，全域等效項使用 *.* 範圍

GRANT SELECT ON *.* TO 'globalread'@'localhost';

如何授予使用者讀取和寫入權限？

唯讀用例的典型伴隨用例是需要讀取和寫入存取權的使用者。這種存取權適用於任何需要管理資料庫或物件內資料的程序。例如，建立或編輯網站使用者個人資料的程序將需要讀取和寫入權限。

要為使用者指派讀取和寫入存取權，請授予他們對物件的 SELECT、INSERT、UPDATE 和 DELETE 權限。例如

GRANT SELECT,INSERT,UPDATE,DELETE ON website.profiles TO 'profilemanager'@'localhost';

如何授予使用者僅附加存取權？

另一種常見情境是建立一個只能將資料附加到表格或其他物件的帳戶。這樣，程序始終對物件具有附加權限，但無法重寫或修改已存在的條目。這對於僅附加事件日誌記錄或將更新實際儲存為新記錄以保留歷史記錄的情境很有用。

要允許帳戶在資料庫物件上擁有僅附加權限，只需授予他們 SELECT 和 INSERT 權限

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';

如果您希望帳戶能夠有選擇地更新記錄的某些部分，您可以額外授予他們對相應欄位的 UPDATE 權限

GRANT SELECT,INSERT ON website.eventlog TO 'weblogger'@'localhost';
GRANT UPDATE (comments) ON website.eventlog TO 'weblogger'@'localhost';

基本語法

REVOKE 指令與 GRANT 指令非常相似。除了指令名稱之外，您是從帳戶撤銷權限，而不是授予權限給帳戶。

基本語法如下：

REVOKE <privileges> ON <database>.<object> FROM '<user>'@'<host>';

與 GRANT 一樣，可以指定多個權限，並以逗號分隔。

目標資料庫、表格、欄位等

由於權限與特定範圍（全域、資料庫、表格等）相關聯，因此 REVOKE 指令必須指定要從哪個範圍移除權限，就像新增權限時一樣。

若要移除全域層級的權限，請使用 *.* 萬用字元來比對任何資料庫和任何資料庫物件

REVOKE SELECT ON *.* FROM 'sally'@'localhost';

若要從特定資料庫移除權限，請在點的左側指定資料庫名稱

REVOKE SELECT ON accounting.* FROM 'meredith'@'localhost';

最後，若要從資料庫物件移除權限，請指定資料庫名稱和物件名稱，並以點分隔

REVOKE UPDATE ON accounting.revenue FROM 'frank'@'localhost';

在撤銷權限後，最好檢查使用者的可用權限，以確保他們沒有仍然透過任何其他方式獲得不必要的存取權

SHOW GRANTS FOR 'frank'@'localhost';

使用部分撤銷來微調權限

從 MySQL 8.0.16 開始，支援部分撤銷。這表示您可以給予帳戶廣泛的權限，然後有選擇性地移除特定範圍的這些權限。

例如，您可以設定一個帳戶，使其擁有資料庫的完整權限，除了 mysql 資料庫，該資料庫用於儲存系統資訊，例如使用者的權限、身分驗證詳細資訊等。部分撤銷允許您授予完整權限，然後為該資料庫新增一個特殊例外。

若要在 MySQL 中啟用部分撤銷，您需要先啟用它。您可以透過在支援的版本（MySQL 8.0.16 或更新版本）中輸入以下內容來永久開啟它

SET PERSIST partial_revokes = ON;

現在，若要設定上述描述的使用者帳戶，您可以輸入

CREATE USER 'normaladmin'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON *.* TO 'normaladmin'@'localhost';
REVOKE ALL PRIVILEGES ON mysql.* FROM 'normaladmin'@'localhost';
GRANT SELECT ON mysql.* TO 'normaladmin'@'localhost';

在這裡，我們建立了一個使用者，並授予他們整個 MySQL 伺服器的完整權限。之後，我們特別在 mysql 資料庫的上下文中撤銷這些權限。然後，我們重新授予 SELECT 權限，以便該帳戶仍然可以從資料庫讀取值。

如果您查看此帳戶的權限，將會顯示類似以下的內容

SHOW GRANTS FOR 'normaladmin'@'localhost'\G

*************************** 1. row ***************************
Grants for normaladmin@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `normaladmin`@`localhost`
*************************** 2. row ***************************
Grants for normaladmin@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `normaladmin`@`localhost`
*************************** 3. row ***************************
Grants for normaladmin@localhost: REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `normaladmin`@`localhost`
3 rows in set (0.00 sec)

第一行是 ALL PRIVILEGES 簡寫在全球範圍（使用 *.*）中套用的所有靜態權限的展開列表。第二行顯示 ALL PRIVILEGES 簡寫所封裝的所有動態權限，同樣在全球範圍內套用。第三行顯示在資料庫層級套用的所有權限，但從 mysql 資料庫撤銷的 SELECT 除外。