如何在 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 数据库中撤销。