Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


8.2.10 使用角色

MySQL 角色是一组命名的权限集合。像用户账户一样，角色可以授予和撤销权限。

用户账户可以授予角色，从而授予账户每个角色的权限。这使得将权限集分配给账户变得方便，并提供了一个概念化所需权限分配和实现它们的替代方案。

以下列表总结了 MySQL 提供的角色管理功能：

有关单个角色操作语句（包括使用它们所需的权限）的描述，请参阅 第 15.7.1 节，“账户管理语句”。以下讨论提供了角色使用示例。除非另有指定，否则这里显示的 SQL 语句应该使用具有足够管理权限的 MySQL 账户执行，例如 root 账户。

创建角色和授予权限

考虑以下场景：

  • 应用程序使用名为 app_db 的数据库。

  • 与应用程序相关联的账户有开发者账户，用于创建和维护应用程序，以及用户账户，用于与应用程序交互。

  • 开发者需要对数据库的完全访问权限。一些用户只需要读取访问权限，而其他用户需要读写访问权限。

为了避免单独授予许多用户账户权限，可以创建角色作为所需权限集的名称。这使得授予用户账户所需权限变得容易，只需授予适当的角色。

要创建角色，使用 CREATE ROLE 语句：

CREATE ROLE 'app_developer', 'app_read', 'app_write';

角色名称与用户账户名称类似，consist of a user part and host part in 'user_name'@'host_name' 格式。主机部分，如果省略，默认为 '%'。用户和主机部分可以不加引号，除非它们包含特殊字符，如 - 或 %。与账户名称不同，角色名称的用户部分不能为空。有关更多信息，请参阅 第 8.2.5 节，“指定角色名称”。

要将权限分配给角色，请执行 GRANT 语句，使用与分配权限给用户账户相同的语法：

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

现在假设您最初需要一个开发者账户、两个只读访问的用户账户和一个需要读写访问的用户账户。使用 CREATE USER 创建账户：

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

要将每个用户账户的所需权限分配，可以使用 GRANT 语句，形式与刚刚显示的相同，但这需要枚举每个用户的单个权限。相反，使用备用 GRANT 语法，授予角色而不是权限：

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

对 rw_user1 账户的 GRANT 语句授予读取和写入角色，这些角色结合提供所需的读取和写入权限。

授予账户角色时的 GRANT 语法不同于授予权限的语法：没有 ON 子句来分配权限，而没有 ON 子句来分配角色。因为语法不同，因此您不能在同一语句中混合授予权限和角色。（您可以授予账户权限和角色，但必须使用单独的 GRANT 语句，每个语句都适合要授予的内容。）角色不能授予匿名用户。

创建角色时，它被锁定，没有密码，并分配默认身份验证插件。（这些角色属性可以使用 ALTER USER 语句更改，by users who have the global CREATE USER 权限。）

在锁定状态下，角色不能用于服务器身份验证。如果解锁，角色可以用于身份验证。这是因为角色和用户都是授权标识符，具有许多共同点和少数区别。另请参阅 用户和角色互换性。

定义强制角色

可以通过在 mandatory_roles 系统变量的值中指定强制角色来定义强制角色。服务器将强制角色视为授予所有用户的，因此不需要明确授予任何账户。

要在服务器启动时指定强制角色，请在服务器的 my.cnf 文件中定义 mandatory_roles：

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

要在运行时设置和持久化 mandatory_roles，使用类似以下的语句：

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

SET PERSIST 设置当前 MySQL 实例的值，并将其保存，以便在后续服务器重新启动时生效。要在当前 MySQL 实例中更改值，而不使其生效，请使用 GLOBAL 关键字，而不是 PERSIST。请参阅 第 15.7.6.1 节，“SET 变量赋值语法”。

设置 mandatory_roles 需要 ROLE_ADMIN 权限，除了通常需要设置全局系统变量的 SYSTEM_VARIABLES_ADMIN 权限（或已弃用的 SUPER 权限）.

强制角色，如明确授予的角色，不会生效，直到激活（见 激活角色）。在登录时，如果启用了 activate_all_roles_on_login 系统变量，则所有授予的角色都会激活；否则，只有默认角色会激活。在运行时，SET ROLE 激活角色。

在 mandatory_roles 值中的角色不能使用 REVOKE 或 DROP ROLE 或 DROP USER 撤销。

为了防止会话默认成为系统会话，拥有 SYSTEM_USER 权限的角色不能列在 mandatory_roles 系统变量的值中：

即使有这种保护措施，还是最好避免通过角色授予 SYSTEM_USER 权限，以防止可能的权限升级。

如果 mandatory_roles 中命名的角色不在 mysql.user 系统表中，该角色不会被授予用户。当服务器尝试激活用户的角色时，它不会将不存在的角色视为强制角色，并写入错误日志。如果该角色稍后被创建并因此变得有效，可能需要 FLUSH PRIVILEGES 使服务器将其视为强制角色。

SHOW GRANTS 根据 第 15.7.7.22 节，“SHOW GRANTS 语句” 中的规则显示强制角色。

检查角色权限

要验证账户的权限，使用 SHOW GRANTS。例如：

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

然而，这只显示授予的角色，而不“展开”到角色所代表的权限。要显示角色权限，也可以添加一个 USING 子句，指定要显示权限的授予角色：

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

类似地，验证其他类型的用户：

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

SHOW GRANTS 根据 第 15.7.7.22 节，“SHOW GRANTS 语句” 中的规则显示强制角色。

激活角色

授予用户账户的角色可以在账户会话中处于活动或非活动状态。如果授予的角色在会话中处于活动状态，其特权将生效；否则，不生效。要确定当前会话中哪些角色处于活动状态，请使用 CURRENT_ROLE() 函数。

默认情况下，将角色授予账户或将其命名为 mandatory_roles 系统变量值不会自动使角色在账户会话中变为活动状态。例如，因为到目前为止没有激活 rw_user1 角色，如果您以 rw_user1 连接到服务器并调用 CURRENT_ROLE() 函数，结果将是 NONE（无活动角色）：

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

要指定哪些角色应该在用户连接到服务器并认证时变为活动状态，请使用 SET DEFAULT ROLE。要将默认值设置为每个账户创建时分配的所有角色，请使用以下语句：

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';

现在，如果您以 rw_user1 连接，初始值 CURRENT_ROLE() 将反映新的默认角色分配：

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

要使所有明确授予的和强制角色在用户连接到服务器时自动激活，请启用 activate_all_roles_on_login 系统变量。默认情况下，自动角色激活是禁用的。

在会话中，用户可以执行 SET ROLE 以更改活动角色集。例如，对于 rw_user1：

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

第一个 SET ROLE 语句停用所有角色。第二个使 rw_user1 成为只读。第三个恢复默认角色。

存储程序和视图对象的有效用户受 DEFINER 和 SQL SECURITY 属性的约束，这些属性确定执行是否在调用者或定义者上下文中发生（见 第 27.6 节，“存储对象访问控制”）：

  • 在调用者上下文中执行的存储程序和视图对象将使用当前会话中的活动角色。

  • 在定义者上下文中执行的存储程序和视图对象将使用定义者用户的默认角色。如果 activate_all_roles_on_login 启用，这些对象将使用定义者用户的所有角色，包括强制角色。对于存储程序，如果执行应该使用不同的角色，可以在程序体中执行 SET ROLE 以激活所需的角色。这必须小心，因为角色分配的权限可以更改。

撤销角色或角色权限

正如角色可以授予账户一样，角色也可以从账户中撤销：

REVOKE role FROM user;

命名在 mandatory_roles 系统变量值中的角色不能被撤销。

REVOKE 也可以应用于角色，以修改授予的权限。这不仅影响角色本身，还影响授予该角色的任何账户。假设您想临时使所有应用程序用户变为只读。为此，请使用 REVOKE 撤销 app_write 角色的修改权限：

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

结果是，该角色没有任何权限，如使用 SHOW GRANTS 可以看到（这也表明该语句可以与角色一起使用，而不仅仅是用户）：

mysql> SHOW GRANTS FOR 'app_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `app_write`@`%` |
+---------------------------------------+

因为从角色中撤销权限会影响分配了该角色的用户的权限，因此，rw_user1 现在没有表修改权限（INSERT、UPDATE 和 DELETE 不再存在）：

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+

实际上，rw_user1 读写用户现在变成了只读用户。这也适用于任何其他被授予 app_write 角色的账户，展示了使用角色如何使得不需要修改个别账户的权限。

要恢复角色修改权限，只需重新授予它们：

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

现在 rw_user1 再次拥有修改权限，如同任何其他被授予 app_write 角色的账户。

删除角色

要删除角色，使用 DROP ROLE：

DROP ROLE 'app_read', 'app_write';

删除角色将从每个账户中撤销该角色。

在 mandatory_roles 系统变量值中命名的角色不能被删除。

用户和角色互换性

如前所示，SHOW GRANTS，它显示用户账户或角色的授权，账户和角色可以互换使用。

用户和角色的一个区别是，CREATE ROLE 创建一个默认锁定的授权标识符，而 CREATE USER 创建一个默认解锁定的授权标识符。你应该记住，这一区别并不是不可变的；拥有适当权限的用户可以锁定或解锁角色或（其他）用户。

如果数据库管理员有一个特定的授权标识符必须是一个角色的偏好，可以使用命名方案来传达这个意图。例如，你可以使用 r_ 前缀来命名所有你打算作为角色的授权标识符。

用户和角色的另一个区别在于管理它们的权限：

因此，CREATE ROLE 和 DROP ROLE 权限不如 CREATE USER 强大，可能授予给那些只应该创建和删除角色的用户，而不是执行更一般的账户操作。

关于权限和用户与角色的互换性，你可以将用户账户视为角色，并将该账户授予另一个用户或角色。效果是授予账户的权限和角色给另一个用户或角色。

以下语句集演示了你可以将用户授予用户、角色授予用户、用户授予角色或角色授予角色：

CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

每种情况的结果都是授予被授予对象与授予权限相关的权限。执行这些语句后，每个 u2 和 r2 都被授予了来自用户 (u1) 和角色 (r1) 的权限：

mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+

前面的示例只是示例，但用户账户和角色的互换性在实践中有实际应用，例如在以下情况下：假设一个遗留应用程序开发项目在 MySQL 中引入角色之前开始，因此所有与项目相关的用户账户都是直接授予权限的（而不是通过授予权限的角色）。其中一个账户是一个开发者账户，最初授予权限如下：

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

如果这个开发者离开项目，就需要将权限分配给另一个用户，或者如果开发活动扩展了，可能需要多个用户。以下是一些处理该问题的方法：

  • 不使用角色：更改账户密码，以便原始开发者无法使用它，然后让新开发者使用该账户：

    ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';

  • 使用角色：锁定账户，以防止任何人使用它连接到服务器：

    ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;

    然后将账户视为角色。对于每个新加入项目的开发者，创建一个新账户并授予原始开发者账户：

    CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';

    这样做的效果是将原始开发者账户的权限分配给新账户。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows