如何在 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 仍然允许您仅激活与帐户关联的默认角色。