Documentation Home
MySQL 8.4 Reference Manual
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


8.2.10 使用角色

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

用户账户可以被授予角色，这将将每个角色的权限授予该账户。这使得可以将权限集分配给账户，并提供了一种替代授予单个权限的便捷方法，既用于概念化所需的权限分配，又用于实现它们。

以下是 MySQL 提供的角色管理功能的总结：

  • CREATE ROLE 和 DROP ROLE 创建和删除角色。

  • GRANT 和 REVOKE 将权限授予或撤销用户账户和角色。

  • SHOW GRANTS 显示用户账户和角色的权限和角色分配。

  • SET DEFAULT ROLE 指定默认激活的账户角色。

  • SET ROLE 更改当前会话中的激活角色。

  • 当前会话中的激活角色可以使用 CURRENT_ROLE() 函数显示。

  • mandatory_roles 和 activate_all_roles_on_login 系统变量使得定义强制角色和自动激活授予的角色，当用户登录到服务器时。

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

考虑以下场景：

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

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

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

为了避免为可能许多用户账户授予单独的权限，创建角色作为所需的权限集的名称。这使得可以轻松地将所需的权限授予用户账户，通过授予适当的角色。

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

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

角色名称类似于用户账户名称，包括用户部分和主机部分，以'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';

GRANT语句为rw_user1账户授予读写角色，这些角色组合起来提供所需的读写权限。

GRANT语句授予角色到账户的语法不同于授予权限的语法：有一个ON子句来分配权限，而没有ON子句来分配角色。由于语法不同，您不能在同一个语句中混合授予权限和角色。 (可以授予同时权限和角色，但必须使用单独的GRANT语句，每个语句使用适当的语法。)角色不能授予匿名用户。

创建角色时，它将被锁定，没有密码，并且将被分配默认身份验证插件。 (这些角色属性可以在使用ALTER USER语句后更改，需要具有全局CREATE USER特权的用户。)

当角色被锁定时，它不能用来身份验证到服务器。如果解锁，角色可以用来身份验证。这是因为角色和用户都是身份验证标识符，具有许多共同点，但又有所不同。请参见用户和角色互换性。

可以将角色指定为强制性角色，通过在mandatory_roles系统变量的值中列出它们。服务器将强制性角色视为授予所有用户的角色，因此不需要对任何帐户进行明确授予。

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

[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。请参见Section 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来撤销或删除。

为了防止会话默认变为系统会话，可以在mandatory_roles系统变量中不列出具有SYSTEM_USER特权的角色。

即使有这个保护机制，仍然建议避免通过角色授予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 Statement”中描述的规则显示强制角色。

为用户账户授予的角色可以在账户会话中处于活动或非活动状态。如果授予的角色在会话中处于活动状态，它们的权限将生效；否则它们将不生效。要确定当前会话中哪些角色是活动的，可以使用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语句 деактивates所有角色。第二个使rw_user1实际上只读。第三个恢复默认角色。

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

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

  • 存储程序和视图对象，执行时使用定义器上下文的默认角色。如果启用了activate_all_roles_on_login，这些对象将使用DEFINER用户的所有角色，包括强制角色。对于存储程序，如果执行时需要使用不同的角色，可以在程序体中执行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.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb