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 的附加子句，以允许用户帐户在特定范围内管理其他用户的授权。你不仅是将权限授予用户，而且还授予该用户将其在同一范围内的任何权限传递给其他用户的能力。

例如，在这里，我们可以为 '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。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 中启用部分撤销，您需要启用它。您可以通过在受支持的版本（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

自 2013 年以来，Justin 一直在撰写关于数据库、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` 特权？