考虑这种情况：Consider this scenario:

为了避免单独授予可能很多用户帐户的权限，可以创建角色作为所需权限集的名称。这使得通过授予适当角色来轻松赋予用户帐户所需的权限。To avoid granting privileges individually to possibly many user accounts, create roles as names for the required privilege sets. This makes it easy to grant the required privileges to user accounts, by granting the appropriate roles.

要创建角色，请使用CREATE ROLE语句：To create the roles, use the CREATE ROLE statement:

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

角色名类似于用户账户名，由用户部分和主机部分组成，格式为'user_name'@'host_name'。如果省略主机部分，则默认为'%'。除非包含特殊字符如-或%，否则用户和主机部分可以不加引号。与账户名不同，角色名的用户部分不能为空。有关更多信息，请参阅第 8.2.5 节，“指定角色名”。Role names are much like user account names and consist of a user part and host part in 'user_name'@'host_name' format. The host part, if omitted, defaults to '%'. The user and host parts can be unquoted unless they contain special characters such as - or %. Unlike account names, the user part of role names cannot be blank. For additional information, see Section 8.2.5, “Specifying Role Names”.

要将权限分配给角色，请使用与分配权限给用户账户相同的语法执行 GRANT 语句：To assign privileges to the roles, execute GRANT statements using the same syntax as for assigning privileges to user accounts:

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 来创建这些账户：Now suppose that initially you require one developer account, two user accounts that need read-only access, and one user account that needs read/write access. Use CREATE USER to create the accounts:

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 语法，它允许授予角色而不是权限：To assign each user account its required privileges, you could use GRANT statements of the same form as just shown, but that requires enumerating individual privileges for each user. Instead, use an alternative GRANT syntax that permits granting roles rather than privileges:

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 账户授予了读取和写入角色，这些角色组合提供了所需的读写权限。The GRANT statement for the rw_user1 account grants the read and write roles, which combine to provide the required read and write privileges.

将角色授予账户的 GRANT 语法与授予权限的语法不同：授予权限时有一个 ON 子句，而授予角色时没有 ON 子句。由于语法不同，您不能在同一语句中混合授予权限和角色。（允许将权限和角色都授予一个账户，但必须使用单独的 GRANT 语句，每个语句的语法应与所授予的内容相符。）角色不能授予匿名用户。The GRANT syntax for granting roles to an account differs from the syntax for granting privileges: There is an ON clause to assign privileges, whereas there is no ON clause to assign roles. Because the syntaxes are distinct, you cannot mix assigning privileges and roles in the same statement. (It is permitted to assign both privileges and roles to an account, but you must use separate GRANT statements, each with syntax appropriate to what is to be granted.) Roles cannot be granted to anonymous users.

创建角色时，它是锁定状态，没有密码，并分配了默认的认证插件。（这些角色属性可以在以后通过拥有全局CREATE USER权限的用户使用ALTER USER语句更改。）A role when created is locked, has no password, and is assigned the default authentication plugin. (These role attributes can be changed later with the ALTER USER statement, by users who have the global CREATE USER privilege.)

在锁定状态下，角色无法用于身份验证服务器。如果解锁，角色可以用于身份验证。这是因为角色和用户都是授权标识符，具有许多相似之处，很少有区别。另请参见用户和角色互换性。While locked, a role cannot be used to authenticate to the server. If unlocked, a role can be used to authenticate. This is because roles and users are both authorization identifiers with much in common and little to distinguish them. See also User and Role Interchangeability.

通过在mandatory_roles系统变量的值中指定角色名称，可以将角色指定为必需。服务器将必需角色视为授予所有用户，因此无需显式授予任何账户。It is possible to specify roles as mandatory by naming them in the value of the mandatory_roles system variable. The server treats a mandatory role as granted to all users, so that it need not be granted explicitly to any account.

要在服务器启动时指定必需角色，请在服务器的my.cnf文件中定义mandatory_roles：To specify mandatory roles at server startup, define mandatory_roles in your server my.cnf file:

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

要在运行时设置并保持mandatory_roles，请使用如下语句：To set and persist mandatory_roles at runtime, use a statement like this:

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

SET PERSIST 为正在运行的 MySQL 实例设置一个值。它还会保存该值，使其在随后的服务器重启中保留。要更改正在运行的 MySQL 实例的值而不让其在随后的重启中保留，请使用 GLOBAL 关键字而不是 PERSIST。参见 第 15.7.6.1 节，“SET 变量赋值语法”。SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

设置 mandatory_roles 需要 ROLE_ADMIN 权限，除此之外，还需要通常用于设置全局系统变量的 SYSTEM_VARIABLES_ADMIN 权限（或已弃用的 SUPER 权限）。Setting mandatory_roles requires the ROLE_ADMIN privilege, in addition to the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege) normally required to set a global system variable.

强制角色（mandatory roles），就像明确授予的角色一样，在被激活之前不会生效（参见 激活角色）。在登录时，如果 activate_all_roles_on_login 系统变量已启用，则会对所有授予的角色进行激活，否则只对设置为默认角色的角色进行激活。在运行时，SET ROLE 会激活角色。Mandatory roles, like explicitly granted roles, do not take effect until activated (see Activating Roles). At login time, role activation occurs for all granted roles if the activate_all_roles_on_login system variable is enabled, or for roles that are set as default roles otherwise. At runtime, SET ROLE activates roles.

在 mandatory_roles 的值中命名的角色，不能使用 REVOKE 撤销，也不能使用 DROP ROLE 或 DROP USER 删除。Roles named in the value of mandatory_roles cannot be revoked with REVOKE or dropped with DROP ROLE or DROP USER.

为了防止会话默认成为系统会话，具有 SYSTEM_USER 权限的角色不能列在 mandatory_roles 系统变量的值中：To prevent sessions from being made system sessions by default, a role that has the SYSTEM_USER privilege cannot be listed in the value of the mandatory_roles system variable:

即使有此安全措施，最好也避免通过角色授予 SYSTEM_USER 权限，以防止出现权限升级的可能性。Even with this safeguard, it is better to avoid granting the SYSTEM_USER privilege through a role in order to guard against the possibility of privilege escalation.

如果在 mandatory_roles 中指定的角色在 mysql.user 系统表中不存在，则该角色不会授予用户。当服务器尝试为用户激活角色时，它不会将不存在的角色视为强制角色，并会将警告写入错误日志。如果该角色后来被创建并因此变为有效，可能需要 FLUSH PRIVILEGES，以使服务器将其视为强制角色。If a role named in mandatory_roles is not present in the mysql.user system table, the role is not granted to users. When the server attempts role activation for a user, it does not treat the nonexistent role as mandatory and writes a warning to the error log. If the role is created later and thus becomes valid, FLUSH PRIVILEGES may be necessary to cause the server to treat it as mandatory.

SHOW GRANTS 根据 第 15.7.7.22 节，“SHOW GRANTS 语句” 中描述的规则显示强制角色。SHOW GRANTS displays mandatory roles according to the rules described in Section 15.7.7.22, “SHOW GRANTS Statement”.

要验证分配给帐户的权限，请使用 SHOW GRANTS。例如：To verify the privileges assigned to an account, use SHOW GRANTS. For example:

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

然而，这显示了每个授予的角色，但没有“展开”到该角色所代表的权限。要同时显示角色权限，请添加一个USING子句，指定要显示权限的授予角色：However, that shows each granted role without “expanding” it to the privileges the role represents. To show role privileges as well, add a USING clause naming the granted roles for which to display privileges:

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`          |
+----------------------------------------------------------+

以类似方式验证每种类型的用户：Verify each other type of user similarly:

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 语句” 中描述的规则显示强制角色。SHOW GRANTS displays mandatory roles according to the rules described in Section 15.7.7.22, “SHOW GRANTS Statement”.

授予用户账户的角色在账户会话中可以是激活或非激活状态。如果授予的角色在会话中是激活的，则其权限适用；否则不适用。要确定当前会话中哪些角色处于激活状态，请使用 CURRENT_ROLE() 函数。Roles granted to a user account can be active or inactive within account sessions. If a granted role is active within a session, its privileges apply; otherwise, they do not. To determine which roles are active within the current session, use the CURRENT_ROLE() function.

默认情况下，将角色授予一个账户或在 mandatory_roles 系统变量值中命名它，并不会自动使角色在账户会话中激活。例如，由于在前面的讨论中还没有激活任何 rw_user1 角色，如果你以 rw_user1 身份连接到服务器并调用 CURRENT_ROLE() 函数，结果将是 NONE（没有活跃的角色）：By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions. For example, because thus far in the preceding discussion no rw_user1 roles have been activated, if you connect to the server as rw_user1 and invoke the CURRENT_ROLE() function, the result is NONE (no active roles):

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

要指定每次用户连接到服务器并进行身份验证时应激活哪些角色，请使用 SET DEFAULT ROLE。要将默认值设置为先前创建的每个帐户的所有分配角色，请使用以下语句：To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE. To set the default to all assigned roles for each account created earlier, use this statement:

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

现在，如果以 rw_user1 连接，CURRENT_ROLE() 的初始值将反映新的默认角色分配：Now if you connect as rw_user1, the initial value of CURRENT_ROLE() reflects the new default role assignments:

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

要使用户在连接到服务器时自动激活所有明确授予和强制性角色，请启用 activate_all_roles_on_login 系统变量。默认情况下，自动角色激活是禁用的。To cause all explicitly granted and mandatory roles to be automatically activated when users connect to the server, enable the activate_all_roles_on_login system variable. By default, automatic role activation is disabled.

在会话中，用户可以执行 SET ROLE 来更改活动角色的集合。例如，对于 rw_user1：Within a session, a user can execute SET ROLE to change the set of active roles. For example, for 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 实际上只能读取。第三个语句恢复默认角色。The first SET ROLE statement deactivates all roles. The second makes rw_user1 effectively read only. The third restores the default roles.

存储程序和视图对象的有效用户受 DEFINER 和 SQL SECURITY 属性的约束，这些属性决定执行是在调用者上下文还是定义者上下文中进行（参见 第 27.6 节，“存储对象访问控制”）：The effective user for stored program and view objects is subject to the DEFINER and SQL SECURITY attributes, which determine whether execution occurs in invoker or definer context (see Section 27.6, “Stored Object Access Control”):

正如角色可以被授予账户，它们也可以从账户中被撤销：Just as roles can be granted to an account, they can be revoked from an account:

REVOKE role FROM user;

在 mandatory_roles 系统变量值中命名的角色无法被撤销。Roles named in the mandatory_roles system variable value cannot be revoked.

REVOKE 也可以应用于一个角色以修改授予该角色的权限。这不仅影响角色本身，也影响任何被授予该角色的账户。假设您想暂时让所有应用程序用户只能读取。为此，使用 REVOKE 撤销 app_write 角色的修改权限:REVOKE can also be applied to a role to modify the privileges granted to it. This affects not only the role itself, but any account granted that role. Suppose that you want to temporarily make all application users read only. To do this, use REVOKE to revoke the modification privileges from the app_write role:

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

实际上，这会导致该角色完全没有任何权限，可以使用SHOW GRANTS查看（这表明该语句可以用于角色，而不仅仅是用户）：As it happens, that leaves the role with no privileges at all, as can be seen using SHOW GRANTS (which demonstrates that this statement can be used with roles, not just users):

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

因为从角色撤销权限会影响分配了该角色的任何用户的权限，所以rw_user1现在没有表修改权限（INSERT、UPDATE和DELETE不再存在）：Because revoking privileges from a role affects the privileges for any user who is assigned the modified role, rw_user1 now has no table modification privileges (INSERT, UPDATE, and DELETE are no longer present):

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 角色的其他账户也会发生这种情况，这说明使用角色可以使得无需修改单个账户的权限。In effect, the rw_user1 read/write user has become a read-only user. This also occurs for any other accounts that are granted the app_write role, illustrating how use of roles makes it unnecessary to modify privileges for individual accounts.

要恢复该角色的修改权限，只需重新授予它们：To restore modification privileges to the role, simply re-grant them:

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

现在 rw_user1 再次拥有修改权限，其他被授予 app_write 角色的账户亦然。Now rw_user1 again has modification privileges, as do any other accounts granted the app_write role.

要删除角色，请使用 DROP ROLE：To drop roles, use DROP ROLE:

DROP ROLE 'app_read', 'app_write';

删除一个角色会撤销授予该角色的每个账户的权限。Dropping a role revokes it from every account to which it was granted.

在 mandatory_roles 系统变量值中指定的角色不能被删除。Roles named in the mandatory_roles system variable value cannot be dropped.

正如前面在 SHOW GRANTS 中所暗示的那样，该语句显示用户账户或角色的授权，账户和角色可以互换使用。As has been hinted at earlier for SHOW GRANTS, which displays grants for user accounts or roles, accounts and roles can be used interchangeably.

角色和用户之间的一个区别是，CREATE ROLE 创建的授权标识符默认是锁定的，而 CREATE USER 创建的授权标识符默认是未锁定的。您应该记住，这一区别并非不可改变；具有适当权限的用户在创建后可以锁定或解锁角色或（其他）用户。One difference between roles and users is that CREATE ROLE creates an authorization identifier that is locked by default, whereas CREATE USER creates an authorization identifier that is unlocked by default. You should keep in mind that this distinction is not immutable; a user with appropriate privileges can lock or unlock roles or (other) users after they have been created.

如果数据库管理员希望特定的授权标识符必须是角色，可以使用命名方案来传达这一意图。例如，您可以对所有打算作为角色使用的授权标识符使用 r_ 前缀，而不用于其他任何用途。If a database administrator has a preference that a specific authorization identifier must be a role, a name scheme can be used to communicate this intention. For example, you could use a r_ prefix for all authorization identifiers that you intend to be roles and nothing else.

角色和用户之间的另一个区别在于管理它们时可用的权限：Another difference between roles and users lies in the privileges available for administering them:

因此，CREATE ROLE 和 DROP ROLE 权限并不像 CREATE USER 那么强大，可以授予那些只应被允许创建和删除角色，而不进行更一般账户操作的用户。Thus, the CREATE ROLE and DROP ROLE privileges are not as powerful as CREATE USER and may be granted to users who should only be permitted to create and drop roles, and not perform more general account manipulation.

关于用户和角色的权限及可互换性，您可以将用户账户视为一个角色，并将该账户授予另一个用户或角色。其效果是将该账户的权限和角色授予另一个用户或角色。With regard to privileges and interchangeability of users and roles, you can treat a user account like a role and grant that account to another user or a role. The effect is to grant the account's privileges and roles to the other user or role.

这组语句演示了您可以将用户授予用户、将角色授予用户、将用户授予角色或将角色授予角色：This set of statements demonstrates that you can grant a user to a user, a role to a user, a user to a role, or a role to a role:

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）的权限：The result in each case is to grant to the grantee object the privileges associated with the granted object. After executing those statements, each of u2 and r2 have been granted privileges from a user (u1) and a role (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 引入角色之前就已经开始，因此与该项目相关的所有用户账户都是直接授予权限的（而不是通过授予角色来获得权限）。其中一个账户是开发者账户，其最初的权限授予如下：The preceding example is illustrative only, but interchangeability of user accounts and roles has practical application, such as in the following situation: Suppose that a legacy application development project began before the advent of roles in MySQL, so all user accounts associated with the project are granted privileges directly (rather than granted privileges by virtue of being granted roles). One of these accounts is a developer account that was originally granted privileges as follows:

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

如果该开发者离开项目，就有必要将权限分配给另一个用户，或者如果开发活动扩展，可能需要分配给多个用户。以下是处理该问题的一些方法：If this developer leaves the project, it becomes necessary to assign the privileges to another user, or perhaps multiple users if development activities have expanded. Here are some ways to deal with the issue: