如何在 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 服务器上启用了哪些权限以及哪些权限可用，以及它们相关的上下文，您可以使用以下命令

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）附加到授予语句，以允许用户帐户在特定范围内管理其他用户的授权。您不是仅仅将权限授予用户，而是还授予用户在相同范围内将他们拥有的任何权限传递给其他用户的权限。

例如，这里，我们可以给 '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命令非常接近。除了命令名称之外，您还从帐户中撤销权限，而不是授予它们to帐户。

基本语法如下所示

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 开始，支持部分撤销。这意味着您可以授予帐户广泛的权限，然后选择性地为特定范围删除这些权限。

例如，您可以设置一个帐户，该帐户对数据库具有完全权限，except对于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之外。