MYSQL使用角色

MySQL角色是命名的特权集合。与用户帐户一样，角色可以拥有授予和撤销的特权。

用户帐户可以被授予角色，这将授予帐户与每个角色相关的权限。这允许向帐户分配一组权限，并为授予单个权限提供了一个方便的替代方案，既可以概念化所需的权限分配，也可以实现它们。

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

• CREATE ROLE和DROP ROLE创建和删除角色。
• GRANT和REVOKE分配权限以撤销用户帐户和角色的权限。
• SHOW GRANTS显示用户帐户和角色的权限和角色分配。
• SET DEFAULT ROLE指定哪些帐户角色默认处于活动状态。
• SET ROLE更改当前会话中的活动角色。
• 函数CURRENT_ROLE()显示当前会话中的活动角色。
• 当用户登录到服务器时，mandatory_roles和activate_all_roles_on_login系统变量允许定义强制角色和自动激活授予角色。

创建角色并授予他们特权

考虑这个场景：

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

• 与应用程序相关联，可以为创建和维护应用程序的开发人员以及与之交互的用户提供帐户。

• 开发人员需要对数据库的完全访问权限，有些用户只需要读访问权限，有些用户需要读/写访问权限。

为避免向可能多个用户帐户单独授予权限，请创建角色作为所需权限集的名称。这使得通过授予适当的角色向用户帐户授予所需权限变得容易。

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

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

角色名很像用户帐户名，由用户部分和主机部分组成'user_name'@'host_name'格式。如果省略主机部分，则默认为'%'。用户和主机部分可以不用引号，除非它们包含特殊字符，如-或%。与帐户名不同，角色名的用户部分不能为空。

要为角色分配权限，请使用与为用户帐户分配权限相同的语法执行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语句，每个语句的语法都适合要授予的内容。）角色不能授予匿名用户。

角色在创建时被锁定，没有密码，并被分配默认鉴权插件。（具有全局CREATE USER权限的用户可以稍后使用ALTER USER语句更改这些角色属性。）

锁定时，角色不能用于向服务器进行身份验证。如果解锁，角色可以用于身份验证。这是因为角色和用户都是授权标识符，它们有很多共同点，几乎没有区别。

定义强制性角色

可以通过在mandatory_roles系统变量的值中命名角色来指定它们为强制角色。服务器将强制角色视为授予所有用户，因此无需将其显式授予任何帐户。

要在服务器启动时指定强制角色，请在服务器mymy.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

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

强制角色，如显式授予的角色，在激活之前不会生效（请参阅激活角色）。在登录时，如果启用了activate_all_roles_on_login系统变量，则所有授予的角色都会激活角色，否则设置为默认角色的角色也会激活角色。在运行时，SET ROLE会激活角色。

以mandatory_roles值命名的角色不能用REVOKE撤销，也不能用DROP ROLE或DROP USER。

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

• 如果mandatory_roles在启动时分配了具有SYSTEM_USER权限的角色，则服务器会将消息写入错误日志并退出。
• 如果mandatory_roles在运行时分配了具有SYSTEM_USER权限的角色，则会发生错误并且mandatory_roles值保持不变。

即使有这种保障，最好避免通过角色授予SYSTEM_USER权限，以守卫越权的可能性。

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

检查角色权限

要验证分配给帐户的权限，请使用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`               |
+------------------------------------------------------------------------------+

激活角色

授予用户帐户的角色在帐户会话中可以是活动的或非活动的。如果授予的角色在会话中处于活动状态，则其权限适用；否则，它们不会。要确定当前会话中哪些角色处于活动状态，请使用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属性，这些属性确定执行是在调用者还是定义者上下文中进行

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

• 在定义器上下文中执行的存储程序和视图对象以其DEFINER属性中指定的用户的默认角色执行。如果启用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 ROLE和DROP ROLE语句。
• 使用CREATE USER权限可以使用ALTER USER、CREATE ROLE、CREATE USER、DROP ROLE、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES语句。

因此，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';

      效果是将原始开发者帐户权限分配给新帐户。