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

我們將使用的命令

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

• GRANT: 用於向用戶賬戶分配新許可權
• REVOKE: 用於從使用者賬戶中移除現有許可權

所需許可權

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

• GRANT OPTION: GRANT OPTION 許可權允許您授予或撤銷任何已授予您的許可權
• 您希望分配給其他使用者的任何許可權
• 在 mysql.* 上的 SELECT: 用於對其他賬戶執行 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';

這將授予 'salesadmin'@'localhost' 使用者在 sales 資料庫上您使用者能夠分配的所有許可權，但有幾個重要例外。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 簡寫所包含的所有動態許可權，同樣是全域性應用的。第三行顯示了適用於資料庫級別的所有許可權，但 SELECT 許可權已從 mysql 資料庫中撤銷。