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

命令

以下是我们将要讨论的与管理 MySQL 角色相关的主要 SQL 命令。

• CREATE ROLE：CREATE ROLE 命令在数据库系统中定义一个新角色。
• DROP ROLE：DROP ROLE 命令的作用相反，删除现有角色。
• GRANT：GRANT 命令与角色相关有两个不同的用途：向角色添加权限以及将用户帐户添加为角色成员。
• REVOKE：在角色上下文中，REVOKE 命令从角色中删除权限，并从用户帐户中删除角色成员资格。
• SHOW GRANTS：SHOW GRANTS 命令显示给定用户帐户或角色的权限。
• SET ROLE：SET ROLE 命令更改用户帐户正在积极使用的角色。这允许您指示哪些权限集应用于会话的帐户。
• SET DEFAULT ROLE：SET DEFAULT ROLE 命令定义当客户端以特定用户帐户登录时自动应用的角色。

必需的权限

要遵循本指南，您将需要以下权限

• CREATE ROLE
• GRANT OPTION
• CREATE USER（为其他用户设置默认角色）
• ROLE_ADMIN（设置修改角色行为的系统变量）
• SYSTEM_VARIABLES_ADMIN（设置修改角色行为的系统变量）

CREATE ROLE 权限是 CREATE USER 权限的较低版本，允许您创建和管理角色。已经拥有 CREATE USER 权限的帐户自动拥有管理角色所需的所有功能。

需要 GRANT OPTION 权限才能将权限分配给角色。对于要分配给角色的任何权限，您必须启用 GRANT OPTION。

MySQL 角色的语法是什么？

角色名称必须遵循特定格式，MySQL 才能将其视为有效。在许多方面，它们都镜像了 用于定义 MySQL 用户帐户的格式，但存在一些重要的差异。

角色遵循以下格式

'<role>'@'<host>'

与用户一样，角色也有两个组成部分：角色名称和客户端连接的主机。但是，MySQL 解释这些组件的方式有所不同。

对于角色，名称的 '<role>' 部分永远不能为空。与用户一样，没有角色是“匿名”的概念。另一方面，省略 '<host>' 部分仍然是允许的，MySQL 将使用 % 作为主机。但是，此上下文中的 % 被解释为文字字符，而不是通配符。

实际上，这意味着尽管角色名称表面上共享用户帐户名称的格式，但它们不会像用户帐户那样进行任何类型的评估，而只是一个带有两个组件的标签。它们确实具有名称的两个部分的原因是，您可以创建可以用作用户和角色的用户帐户。当用作用户时，组件受特殊的评估规则约束，用户管理文章中描述了这些规则，当用作角色时，名称仅使用文字组件名称直接匹配。

由于这些规则，在许多情况下，管理员选择仅使用 '<role>' 组件来定义角色。这会导致 MySQL 将文字 % 字符替换为 '<host>' 组件，从而有效地使名称的该部分不可见且无关紧要。如果您不打算将名称同时用作用户帐户和角色，则可以执行相同的操作。

如何创建角色？

要创建新角色，请使用 CREATE ROLE 命令。

基本语法如下所示

CREATE ROLE '<role>'@'<host>';

您还可以通过用逗号分隔每个角色名称来同时创建多个角色

CREATE ROLE '<role_1>'@'<host>', '<role_2>'@'<host>', '<role_3>'@'<host>';

如果您指定的任何角色已存在于系统上，则该命令将失败并显示错误。

为了避免这种情况并使 MySQL 仅发出警告，您可以在 CREATE ROLE 命令之后、角色名称之前包含 IF NOT EXISTS 子句

CREATE ROLE IF NOT EXISTS '<role>'@'<host>';

如上所述，许多时候，管理员为了简单起见，省略了角色名称的 '<host>' 部分，隐式地将其设置为文字 % 字符。因此，在实践中，您的许多角色创建命令可能更像这样

CREATE ROLE '<role>';

如何自动向每个用户授予某些角色？

有时，您可能希望系统上的每个用户都能够访问某些角色。您可以通过设置 mandatory_roles 变量来定义每个帐户自动授予的角色。

要修改 mandatory_roles 变量，您的用户必须具有 ROLE_ADMIN 和 SYSTEM_VARIABLES_ADMIN 权限。您可以通过键入以下内容来设置您希望授予每个用户的角色

SET PERSIST mandatory_roles = '`<role_1>`@`<host>`, `<role_2>`@`<host>`, `<role_3>`@`<host>`';

在这里，我们自动为系统上的每个用户提供三个角色。设置系统变量时，mandatory_roles 的值必须是字符串，因此我们将整个角色列表封装在单引号中，并使用反引号引用单个角色组件。

您不能将具有 SYSTEM_USER 权限的角色添加到 mandatory_roles 列表中。这是一种安全措施，旨在确保并非系统上的所有会话都是自动系统会话。

查看当前活动角色

在激活新角色之前，您可以检查哪些角色当前在您的用户会话中处于活动状态。

要查看会话的活动角色，请键入

SELECT CURRENT_ROLE()

输出将显示当前会话中处于活动状态的零个或多个角色。与这些角色关联的权限将添加到您被允许执行的操作中。

如何激活会话的角色

要更改会话期间处于活动状态的角色，请使用 SET ROLE 命令。您可以通过多种不同的方式使用此命令。

基本语法如下所示

SET ROLE '<rolename>'@'<host>';

这将激活所讨论的角色。重要的是要注意，SET ROLE 命令中未提及的任何先前活动的角色现在都将被停用。

要一次激活多个角色，请用逗号分隔每个角色

SET ROLE '<role_1>'@'<host>', '<role_2>'@'<host>', '<role_3>'@'<host>';

要激活已授予您帐户的所有角色，您可以指定 ALL 而不是特定角色

SET ROLE ALL;

您还可以通过使用 ALL EXCEPT 告诉 MySQL 激活除特定角色外的所有角色

SET ROLL ALL EXCEPT '<role_1>'@'<host>';

另一个选项是通过指定 NONE 来禁用帐户上的所有角色

SET ROLE NONE

这将停用会话中所有用户的角色，仅为您提供专门分配给您的用户帐户的权限。

要返回到为您的帐户定义的默认角色列表，请使用 DEFAULT 关键字

SET ROLE DEFAULT

如何为用户帐户定义默认角色

当您以用户身份登录时自动激活的角色以及当您使用 SET ROLE DEFAULT 时重新激活的角色是可配置的。

要定义默认情况下将激活的角色，请使用 SET DEFAULT ROLE 命令，类似于您使用 SET ROLE 命令的方式

SET DEFAULT ROLE '<role_1>'@'<host>';

这将设置在您登录自己的帐户或使用 SET ROLE DEFAULT 时将激活的默认角色。

如果您的用户具有 CREATE USER 权限，则可以为其他帐户设置默认角色

SET DEFAULT ROLE ALL TO '<user>'@'<host>';

在这里，我们指定 '<user>'@'<host>' 帐户应在身份验证后自动激活其所有角色。

此语法也可用于通过用逗号分隔每个用户来定义多个帐户的默认角色

SET DEFAULT ROLE ALL TO '<user_1>'@'<host>', '<user_2>'@'<host>';

默认情况下为所有用户激活所有角色

如果您希望 MySQL 服务器上的每个帐户默认激活其所有角色，则可以更改系统设置来实现此目的。

当 activate_all_roles_on_login 变量设置为 true 时，MySQL 将在登录时自动激活与帐户关联的所有角色。这取代了 SET DEFAULT ROLE 指定的设置。

要启用此功能，您必须具有 SYSTEM_VARIABLES_ADMIN 和 ROLE_ADMIN 权限。通过键入以下内容启用该功能

SET PERSIST activate_all_roles_on_login = ON;

这将导致用户帐户在登录时自动激活所有角色。但是，SET ROLE DEFAULT 仍然允许您仅激活与帐户关联的默认角色。