MySQL / 身份验证和授权

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

内容

简介
先决条件
MySQL 中的权限如何运作？
如何查看帐户拥有的权限？
如何使用 GRANT 命令？
向用户帐户授予常用权限
如何使用 REVOKE 命令？
SUPER 权限是什么？
结论

分享到

简介

权限管理是系统和数据库管理的重要组成部分。决定谁应该拥有哪些组件和功能的哪些访问权限，然后设计一个能够实现这些策略的实现，需要大量的思考和细心。

MySQL 拥有一个强大的权限分配系统，允许您在整个数据库系统中实施访问策略。在本指南中，我们将讨论如何使用 GRANT 和 REVOKE 命令来添加和删除 MySQL 用户帐户的权限，并实施符合您要求的访问策略。

先决条件

要遵循本指南，您需要一个具有适当权限的 MySQL 服务器帐户。

我们将使用的命令

本指南中最重要的命令是 GRANT 和 REVOKE 命令

GRANT：用于向用户帐户分配新权限
REVOKE：用于从用户帐户中删除现有权限

所需权限

要管理 MySQL 用户的权限，您需要拥有以下权限

GRANT OPTION：GRANT OPTION 权限允许您授予或撤销您已拥有的任何权限
您希望分配给其他用户的任何权限
SELECT on mysql.*：用于对其他帐户执行 SHOW GRANTS

要遵循本指南，我们假设您正在使用具有完整管理权限（包括 GRANT OPTION 权限）的帐户。这可能是安装期间配置的常用 'root'@'localhost' 用户，或任何其他具有完整权限的用户。

MySQL 中的权限如何运作？

在 MySQL 中，权限系统决定用户是否可以执行给定命令。

每次客户端尝试执行操作时，MySQL 都会查阅其用户权限信息以确定是否应该允许。如果用户已获得执行操作所需的所有权限，MySQL 将执行语句。如果用户缺少任何所需权限，则会发生错误。

MySQL 将哪些用户拥有哪些权限的信息存储在 mysql 系统数据库中的多个不同表中。下面回顾了 MySQL 存储不同类型权限信息的位置，正如在MySQL 身份验证和授权简介文章中介绍的那样

user：user 表定义了每个用户的静态全局权限。这些权限适用于整个 MySQL 服务器，并且不受任何插件或组件可用性的影响。
global_grants：global_grants 表定义了每个用户的动态全局权限。由插件或组件定义的任何权限都注册在此表中。
db：db 表定义了数据库级权限。db 表匹配用户的 User 和 Host 值，就像 user 表一样，但还包含一个名为 Db 的列，用于定义行的数据库范围。
tables_priv：tables_priv 表以类似于 db 表定义数据库权限的方式定义表级权限。为了启用表级范围，除了 User、Host 和 Db 之外，还提供了一个名为 Table_name 的列。
columns_priv：比 tables_priv 表更进一步，columns_priv 表在列级别确定访问权限。为了增加这种额外的粒度，除了 tables_priv 表中可用的列之外，还包含一个名为 Column_name 的列。
procs_priv：procs_priv 表定义了执行存储过程和函数的权限。它使用 User、Host、Db、Routine_name 和 Routine_type 列来确定用户对不同类型进程的权限范围。
proxies_priv：proxies_priv 表定义了用户的代理权限。代理允许一个用户充当另一个用户，继承其权限。proxies_priv 表使用 User 和 Host 列来匹配用户，然后使用单独的列 Proxied_host 和 Proxied_user 来定义匹配用户可以充当谁。

MySQL 中有哪些权限可用？

MySQL 定义了许多适用于各种系统范围的权限。其中一些对数据库、表和函数的日常使用和管理很有用，而另一些则专为管理任务而设计，例如复制、备份和连接管理。

您可以在 MySQL 文档中的允许的 GRANT 和 REVOKE 静态权限表中找到静态权限（MySQL 本身内置的核心权限）及其各自范围的全面列表。MySQL 文档中相关的静态权限描述部分详细概述了每个权限允许什么，并在许多情况下提供了它们最有用的场景的指导。

动态权限是另一种权限类型。动态权限在插件或组件中定义，并注册到 MySQL 中以启用它们。它们始终是全局范围的，并提供额外的功能或特性。MySQL 文档中的允许的 GRANT 和 REVOKE 动态权限表列出了每个动态权限及其上下文。您可以在 MySQL 文档中相关的动态权限描述部分中找到每个动态权限的完整描述。

要了解您的 MySQL 服务器上启用了哪些权限以及它们的相关上下文，您可以使用以下命令

SHOW PRIVILEGES

这可以帮助您了解哪些权限最适合您的用户职责。

如何查看帐户拥有的权限？

现在我们已经回顾了 MySQL 中权限的工作方式以及有哪些权限可用，您如何找出每个帐户都授予了哪些权限？

您始终可以通过输入以下内容查看授予您自己用户的权限

SHOW GRANTS;

+--------------------------------------------------------------------+
  Grants for exampleuser@localhost                                   |
+--------------------------------------------------------------------+
  GRANT USAGE ON *.* TO `exampleuser`@`localhost`                    |
  GRANT ALL PRIVILEGES ON `exampledb`.* TO `exampleuser`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

在这里，我们看到 'exampleuser'@'localhost' 定义了两组权限。第一个条目显示它已全局授予 USAGE（由通配符 <database>.<table> 范围 *.* 指示）。尽管其名称如此，USAGE 在此上下文中实际上表示“未授予任何权限”。因此，默认情况下，此用户未获得任何权限。第二个记录显示他们已获得 ALL PRIVILEGES，即对 exampledb 数据库的完全访问权限。

如果您登录的用户帐户对内部 mysql 数据库具有 SELECT 权限，则可以查看授予其他用户帐户的权限。要显示其他帐户的权限，请使用以下格式

SHOW GRANTS FOR '<user>'@'<host>';

输出将显示所提供帐户的权限。

如何使用 `GRANT` 命令？

GRANT 命令用于向帐户分配新权限。它是向用户帐户添加对他们以前没有的数据库、对象或操作的访问权限的主要方式。每当您希望向用户帐户提供额外访问权限时，GRANT 命令都可以提供帮助。

基本语法

用于分配权限的 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` 子句

可以向 grant 语句附加一个名为 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'@'localhost' 授予额外权限，它也将能够自动传递这些权限。

尽管您可以使用如上所示的 WITH GRANT OPTION 子句来允许帐户在您授予他们额外权限的同时传递其权限，但如果将这两个操作分开，通常会更清晰，如下所示

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

在任何这些情况下，结果是 'librarymanager'@'localhost' 帐户将能够授予它现在和将来拥有的所有 library 数据库权限给其他用户。这使得 GRANT OPTION 权限如果粗心分配会特别危险，因为它可能允许用户授予帐户管理员不希望的额外权限。

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

向用户帐户授予常用权限

现在我们已经讨论了授予权限的一般工作方式，我们可以通过一些示例来了解如何向用户帐户分配各种常用权限。

如何授予用户完全访问权限？

通常，您希望将特定用户对数据库或数据库组件的完全所有权分配给他们。例如，您的 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。GRANT 命令在特定范围内向用户分配额外权限，而 REVOKE 命令允许您从帐户中删除权限。

基本语法

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 权限。

`SUPER` 权限是什么？

SUPER 权限是一种特殊权限，具有许多强大且潜在危险的能力。从 MySQL 8 开始，SUPER 权限已被弃用，取而代之的是更细粒度的动态权限，以允许更精细的控制级别。

要了解 SUPER 权限允许的功能以及现在可以使用的动态权限，请查看 MySQL 文档中包含的这些资源

如果您尚未在使用 SUPER 权限，MySQL 建议您使用您需要的动态权限子集，而不是向新帐户授予 SUPER 权限。

结论

在本指南中，我们讨论了 MySQL 的权限系统如何允许您控制用户帐户对不同范围的各种资源的访问级别。权限可以全局分配给用户帐户，也可以在数据库级别分配，或者在数据库对象级别更细粒度地分配。

我们介绍了 GRANT 命令，用于向用户帐户添加新权限以提高其访问级别。我们讨论了 GRANT OPTION 如何允许用户传递其权限，以便管理员可以分配其权限管理职责，然后讨论了如何向用户帐户分配常用权限。我们演示了如何使用 REVOKE 命令删除分配给帐户的权限，以及部分撤销如何允许您制定对广泛许可的例外情况。

了解如何向用户帐户分配权限可以帮助您使用最小权限原则来设置您的访问管理系统。通过仅授予帐户执行其工作所需的特定权限，您可以防止未经授权的行为，最大程度地减少安全问题的影响，并实施隔离策略以防止系统的不同部分相互影响。

作者简介

Justin Ellingwood

Justin 自 2013 年以来一直撰写关于数据库、Linux、基础设施和开发工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

如何在 MySQL 中管理用户和身份验证

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

在 GitHub 上编辑此页面

MySQL / 身份验证和授权

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

内容

简介

先决条件

我们将使用的命令

所需权限

MySQL 中的权限如何运作？

MySQL 中有哪些权限可用？

如何查看帐户拥有的权限？

如何使用 GRANT 命令？

基本语法

定位数据库、表、列等。

使用 WITH GRANT OPTION 子句

向用户帐户授予常用权限

如何授予用户完全访问权限？

如何授予用户包括权限管理在内的完全访问权限？

如何授予用户只读访问权限？

如何授予用户读写访问权限？

如何授予用户仅追加访问权限？

如何使用 REVOKE 命令？

基本语法

定位数据库、表、列等。

使用部分撤销来微调权限

SUPER 权限是什么？

结论

Justin Ellingwood

Prisma 的数据指南

如何使用 `GRANT` 命令？

使用 `WITH GRANT OPTION` 子句

如何使用 `REVOKE` 命令？

`SUPER` 权限是什么？