MySQL 角色管理

MySQL 8.0 引入了角色（Role）功能，这是一种权限管理的新方式，可以简化用户权限的管理。

版本说明

本文写于 2022-03，基于 MySQL 8.0。
角色（Role）功能自 8.0 引入，语法未变，经 2026-07 复核仍适用。

为什么值得从零散 GRANT 换成角色：没有角色时，权限是直接挂在每个用户上的。新加一张表要放开只读权限，就得对着 30 个只读账号各 GRANT 一遍；某个账号权限要收回，得先查清楚它当初被授过什么。角色把「一组权限」抽象成一个可复用的对象——权限改一次，所有绑定该角色的用户同时生效，这才是它的价值所在，语法简洁只是顺带的。

角色本质上就是一个不能登录的用户。它和用户存在同一张 mysql.user 表里，区别只有一个：角色的 account_locked = 'Y' 且没有密码，所以登录不了。理解这一点后很多行为就不奇怪了——比如角色也要写成 'name'@'host' 的形式、DROP ROLE 和 DROP USER 效果基本一致。

创建角色

CREATE ROLE 'test_role'@'%';

授权角色

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'test_role'@'%';

将角色分配给用户

CREATE USER 'test'@'192.0.2.11' IDENTIFIED BY '';
CREATE USER 'test1'@'192.0.2.12' IDENTIFIED BY '';
GRANT 'test_role'@'%' TO 'test'@'192.0.2.11', 'test1'@'192.0.2.12';

激活角色

SET DEFAULT ROLE 'test_role'@'%' TO 'test1'@'192.0.2.12';
SELECT CURRENT_ROLE();

为什么授了角色还要"激活"：GRANT role TO user 只是建立了「这个用户可以使用该角色」的关系，不等于该角色在会话里生效。用户登录后默认激活哪些角色由 SET DEFAULT ROLE 决定；没设过的话，登录后 SELECT CURRENT_ROLE() 返回 NONE，用户实际上一个权限都没有——这是角色功能最常见的「我明明授权了怎么没权限」的原因。

会话内也可以临时切换当前生效的角色：

SET ROLE 'test_role'@'%';   -- 只激活指定角色
SET ROLE ALL;               -- 激活所有已授予的角色
SET ROLE NONE;              -- 全部关闭（用来验证"没有角色时到底能做什么"）

注意 SET ROLE 只影响当前会话，断开就没了。

每次新建用户都要单独 SET DEFAULT ROLE 一次确实麻烦，如何让授予的角色登录即生效？

MySQL提供了一个系统参数来解决这个问题，该参数是：

mysql> SHOW VARIABLES LIKE '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

-- 该参数默认关闭，打开后用户登录时会自动激活所有已授予的角色，不必再 SET DEFAULT ROLE
SET GLOBAL activate_all_roles_on_login=ON;

-- 持久化到 datadir/mysqld-auto.cnf（推荐，重启仍生效）
SET PERSIST activate_all_roles_on_login=ON;

撤销角色权限

REVOKE INSERT, UPDATE, DELETE ON testdb.* FROM 'test_role'@'%';

删除角色

DROP ROLE 'test_role'@'%';

角色管理相关命令

查看用户角色

-- 查看当前会话生效中的角色
SELECT CURRENT_ROLE();

-- 全实例的默认角色配置（注意：这是所有用户的，不是"当前用户的"）
SELECT * FROM mysql.default_roles;

-- 角色授予关系：谁被授予了哪个角色（FROM_* 是角色，TO_* 是被授予者）
SELECT * FROM mysql.role_edges;

-- 某个用户通过角色最终拿到了什么权限（最实用的一条）
SHOW GRANTS FOR 'test1'@'192.0.2.12' USING 'test_role'@'%';

最后这条的 USING 子句是关键：不加 USING，SHOW GRANTS 只会显示「该用户被授予了某角色」，不会展开角色里具体有哪些权限。排查「这个账号到底能干什么」时必须带上它。

角色继承

-- 创建一个基础角色
CREATE ROLE 'basic_role'@'%';
GRANT SELECT ON testdb.* TO 'basic_role'@'%';

-- 创建一个高级角色，继承基础角色
CREATE ROLE 'advanced_role'@'%';
GRANT 'basic_role'@'%' TO 'advanced_role'@'%';
GRANT INSERT, UPDATE ON testdb.* TO 'advanced_role'@'%';

最佳实践

1. 合理设计角色层次：避免过于复杂的角色继承关系
2. 最小权限原则：给角色分配最小必需的权限
3. 定期审查：定期检查角色权限分配的合理性
4. 使用持久化设置：通过SET PERSIST确保配置的持久性

验证

角色配置对不对，不能只看 SHOW GRANTS，要用目标账号实际连上去试。

1. 确认角色本身有权限：

SHOW GRANTS FOR 'test_role'@'%';

预期输出：

+-------------------------------------------------------------------+
| Grants for test_role@%                                            |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_role`@`%`                             |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO `test_role`@`%` |
+-------------------------------------------------------------------+

2. 用目标账号登录，确认角色已激活：

SELECT CURRENT_ROLE();

预期输出：

+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `test_role`@`%`     |
+---------------------+

返回 NONE 就是没生效——回去检查 SET DEFAULT ROLE 或 activate_all_roles_on_login。

3. 实际跑一条 SQL（这一步才算真的验证过）：

SELECT COUNT(*) FROM testdb.some_table;   -- 应当成功
DROP TABLE testdb.some_table;             -- 应当被拒绝

预期第二条报：

ERROR 1142 (42000): DROP command denied to user 'test'@'192.0.2.11' for table 'some_table'

权限的验证一定要包含「本不该有的权限确实没有」这一半，只测能做什么会漏掉授权过宽的问题。

坑与边界

• CURRENT_ROLE() 返回 NONE 是最高频的问题。授予 ≠ 激活，见上文。生产实例建议统一打开 activate_all_roles_on_login，省掉这一类排查。
• 角色的 host 部分必须对得上。'test_role'@'%' 和 'test_role'@'localhost' 是两个不同的角色。GRANT 'test_role' TO ... 省略 host 时默认按 @'%' 解析，与你创建时写的 host 不一致就会报「角色不存在」。习惯上角色一律建成 @'%'，把访问来源的限制放在用户上。
• 存储过程、函数、视图里角色不生效。这些对象按 DEFINER 的权限执行，而 DEFINER 的权限检查不走角色——一个仅通过角色拿到权限的账号，创建的存储过程在执行时会因权限不足失败。这类对象的 DEFINER 仍需直接 GRANT。
• 改了角色的权限，已登录会话不会立即生效。角色权限在会话建立时快照到内存，REVOKE 之后老连接可能还带着旧权限，直到重连。安全事件响应中收权限时，收完必须把该用户的现有连接 KILL 掉，否则等于没收。
• DROP ROLE 会静默影响所有绑定它的用户。没有依赖检查，删掉之后所有用户立刻失去这组权限，且不会有任何提示。删之前先查一遍 mysql.role_edges 看谁在用。
• 角色可以嵌套，但不要嵌太深。GRANT 'basic_role' TO 'advanced_role' 是支持的（文中「角色继承」一节），但多层嵌套后想搞清楚某个账号最终有什么权限会非常困难——排查时只能靠 SHOW GRANTS ... USING 逐层展开。两层是实践上的合理上限。
• 角色不适用于 MySQL 5.7 及更早版本。做主从升级或异构复制时，8.0 上建的角色语句在低版本上会直接语法错误；权限迁移脚本要按目标版本生成。