MySQL 角色

一、角色概述

角色是一个权限的集合，用于集中管理权限，简化多用户的权限分配与回收操作。

二、角色名称

1. 格式

‘user_name’ @ ‘host_name’
- 与账户名称格式类似
- host_name 部分可省略，默认为 %
- user_name 和 host_name 通常无需引号，除非包含特殊字符（如 - 或 %）
- user_name 不能为空

2. 与账户名的对比

（1）不同点

角色的 user 部分不能为空，不支持匿名角色
角色的 host 部分不支持通配符 % 和子网掩码
账户名不允许为 CURRENT_USER()

（2）相同点

账户和角色信息均保存在 mysql.user 表中，同一条记录可同时作为账户和角色
通常仅用 user_name 部分指定角色，host_name 隐式为 %

三、角色的使用

1. 功能列表

创建和删除角色：CREATE ROLE、DROP ROLE
授权和回收权限：GRANT、REVOKE
显示分配情况：SHOW GRANTS
指定默认角色：SET DEFAULT ROLE
更改会话活动角色：SET ROLE
显示会话活动角色：CURRENT_ROLE()
定义并激活强制性角色：系统变量 mandatory_roles 和 activate_all_roles_on_login

2. 创建角色与授予权限

（1）创建角色

mysql> create role app_developer, app_read, app_writer;

角色创建后默认被锁定（account_locked = Y），无密码，使用默认身份认证插件（如 caching_sha2_password）
可通过 ALTER USER 语句修改角色属性（需 CREATE USER 权限）
验证角色信息：

mysql> select user, host, plugin, account_locked from mysql.user where host = '%';
+---------------+------+-----------------------+----------------+
| user          | host | plugin                | account_locked |
+---------------+------+-----------------------+----------------+
| app_developer | %    | caching_sha2_password | Y              |
| app_read      | %    | caching_sha2_password | Y              |
| app_writer    | %    | caching_sha2_password | Y              |
+---------------+------+-----------------------+----------------+

（2）为角色分配权限

-- 语法与为账户分配权限相同
mysql> grant all on zabbix.* to app_developer;
mysql> grant select on zabbix.* to app_read;
mysql> grant insert, update, delete on zabbix.* to app_writer;

（3）创建用户账户

mysql> create user dev1@localhost identified by 'dev1';
mysql> create user read_user1@localhost identified by 'read_user1';
mysql> create user read_user2@localhost identified by 'read_user2';
mysql> create user rw_user1@localhost identified by 'rw_user1';

（4）为账户分配角色

mysql> grant app_developer to dev1@localhost;
mysql> grant app_read to read_user1@localhost, read_user2@localhost;
mysql> grant app_writer to rw_user1@localhost;

区别：带 ON 子句为分配权限，不带 ON 子句为分配角色

（5）检查角色权限

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 `zabbix`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

四、强制性角色

强制性角色会被自动授予所有用户，无需明确指定。

1. 相关系统变量

（1）mandatory_roles

功能：指定强制性角色
所需权限：ROLE_ADMIN、SYSTEM_VARIABLES_ADMIN
限制：
- 无法通过 REVOKE、DROP ROLE 或 DROP USER 收回
- 角色不能拥有 SYSTEM_USER 权限（避免会话设为系统会话）
- 角色必须存在于 mysql.user 表中，否则不生效；角色后续创建后需执行 FLUSH PRIVILEGES

（2）activate_all_roles_on_login

功能：激活强制性角色（未激活则不生效）
默认状态：禁用（off）
说明：动态设置的角色为激活状态，需同时将角色设为默认角色

2. 配置方法

（1）修改配置文件（/etc/my.cnf）

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

（2）持久化设置系统变量

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

五、角色的激活

1. 查看当前会话活动角色

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+

2. 设置默认激活角色

（1）动态设置（连接时默认激活）

mysql> set default role all to dev1@localhost, read_user1@localhost, read_user2@localhost, rw_user1@localhost;

（2）配置文件设置

通过系统变量 activate_all_roles_on_login 启用（见“强制性角色”部分）

（3）验证

使用 rw_user1 连接后查看：

mysql> select current_role();
+------------------+
| current_role()   |
+------------------+
| `app_writer`@`%` |
+------------------+

3. 在会话中更改活动角色

示例：为 rw_user1 分配 app_read 和 app_writer 角色并激活

mysql> grant app_read, app_writer to rw_user1@localhost;
mysql> set default role all to rw_user1@localhost;

通过 SET ROLE 语句修改会话角色状态：

mysql> select current_role();
+---------------------------------+
| current_role()                  |
+---------------------------------+
| `app_read`@`%`,`app_writer`@`%` |
+---------------------------------+

mysql> set role none; select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+

mysql> set role all except app_writer; select current_role();
+----------------+
| current_role() |
+----------------+
| `app_read`@`%` |
+----------------+

mysql> set role default; select current_role();
+---------------------------------+
| current_role()                  |
+---------------------------------+
| `app_read`@`%`,`app_writer`@`%` |
+---------------------------------+

六、角色与权限的回收

1. 回收操作

mysql> revoke app_read from read_user1@localhost; -- 从账户回收角色
mysql> revoke select on zabbix.* from app_read; -- 从角色回收权限

注意：mandatory_roles 指定的强制性角色不能通过 REVOKE、DROP ROLE 或 DROP USER 收回

2. 验证回收结果

mysql> show grants for app_read;
+--------------------------------------+
| Grants for app_read@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%` |
+--------------------------------------+

3. 恢复权限（重新授权）

mysql> grant select on zabbix.* to app_read;
mysql> show grants for app_read;
+----------------------------------------------+
| Grants for app_read@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%`         |
| GRANT SELECT ON `zabbix`.* TO `app_read`@`%` |
+----------------------------------------------+

七、角色的删除

mysql> drop role app_read, app_writer;
mysql> show grants for rw_user1@localhost;
+----------------------------------------------+
| Grants for rw_user1@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost` |
+----------------------------------------------+

注意：删除角色后，关联账户的对应权限恢复默认；mandatory_roles 指定的角色不能通过 REVOKE、DROP ROLE 或 DROP USER 收回。

八、用户与角色的互换性

用户和角色可相互授予，即用户可视为角色，角色也可被授予其他用户或角色。

1. 示例

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'; -- 将用户 u1 和角色 r1 授予用户 u2
GRANT 'u1', 'r1' TO 'r2'; -- 将用户 u1 和角色 r1 授予角色 r2

2. 查看权限

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

3. 老账户作为角色使用

-- 创建老账户并授权
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';

4. 命名规范

角色名建议加 r_ 前缀
账户名建议加 u_ 前缀

红炉点雪

MySQL 角色

MySQL 角色

一、角色概述

二、角色名称

1. 格式

2. 与账户名的对比

（1）不同点

（2）相同点

三、角色的使用

1. 功能列表

2. 创建角色与授予权限

（1）创建角色

（2）为角色分配权限

（3）创建用户账户

（4）为账户分配角色

（5）检查角色权限

四、强制性角色

1. 相关系统变量

（1）mandatory_roles

（2）activate_all_roles_on_login

2. 配置方法

（1）修改配置文件（/etc/my.cnf）

（2）持久化设置系统变量

五、角色的激活

1. 查看当前会话活动角色

2. 设置默认激活角色

（1）动态设置（连接时默认激活）

（2）配置文件设置

（3）验证

3. 在会话中更改活动角色

六、角色与权限的回收

1. 回收操作

2. 验证回收结果

3. 恢复权限（重新授权）

七、角色的删除

八、用户与角色的互换性

1. 示例

2. 查看权限

3. 老账户作为角色使用

4. 命名规范