mysql数据库中的用户权限与角色管理

MySQL 8.0+ 角色是独立权限容器，非用户组别名，需先授角色再激活才生效；WITH GRANT OPTION 易致权限逃逸，应禁用；SHOW GRANTS 默认不显示角色权限，须加 USING；FLUSH PRIVILEGES 仅在直改系统表后需要。

MySQL 8.0+ 的角色（ROLE）不是“用户组”的简单别名

MySQL 5.7 不支持角色，角色是 8.0 引入的正式特性，底层由 mysql.role_edges 和 mysql.default_roles 系统表维护。直接对旧版本执行 CREATE ROLE 会报错 ERROR 1064 (42000)。

角色本身不登录、无密码、不能被直接授权给客户端连接；它只是权限容器。真正生效的是「把角色授予用户」+「用户激活该角色」两个动作。

• 创建角色：

  CREATE ROLE 'app_reader';

• 授予权限给角色：

  GRANT SELECT ON mydb.* TO 'app_reader';

• 把角色授予用户：

  GRANT 'app_reader' TO 'webuser'@'10.20.%';

• 用户需显式激活（否则权限不生效）：

  SET ROLE 'app_reader';

  或启动时默认激活：

  SET DEFAULT ROLE 'app_reader' TO 'webuser'@'10.20.%';

GRANT 语句里带 WITH GRANT OPTION 很危险

一旦用户 A 被授予 GRANT SELECT ON sales.* TO 'a'@'%' WITH GRANT OPTION，A 就能再把 SELECT 权限转授给 B、C，甚至授予自己没被允许的权限（如 INSERT），只要目标对象在 sales.* 范围内。

更隐蔽的风险：A 可以用 GRANT …… TO 'a'@'%' 把权限回授给自己，绕过管理员管控；或者创建新用户并立即赋权，形成权限逃逸链。

• 检查谁拥有传递权：

  SELECT * FROM mysql.role_edges WHERE to_host = '%' AND from_host = '%';

  （需有 SELECT 权限访问系统表）

• 回收传递能力：

  REVOKE GRANT OPTION ON sales.* FROM 'a'@'%';

• 生产环境应禁用 WITH GRANT OPTION，改用角色集中管控

SHOW GRANTS 输出结果容易误读

SHOW GRANTS FOR 'dev'@'localhost' 默认只显示「直接授予该用户的权限」，不包含其被授予的角色权限，也不显示默认激活状态。你看到的可能是空或极简列表，但用户实际权限远不止这些。

要查全量有效权限（含角色继承），必须用 SHOW GRANTS FOR 'dev'@'localhost' USING 'role_dev';，其中 'role_dev' 是已授予该用户的某个角色名。没有 USING 子句，就看不到角色带来的权限。

• 查看用户所有被授予的角色：

  SELECT * FROM mysql.role_edges WHERE to_user = 'dev' AND to_host = 'localhost';

• 查看用户默认激活的角色：

  SELECT * FROM mysql.default_roles WHERE user = 'dev' AND host = 'localhost';

• 验证当前会话实际权限：

  SELECT * FROM information_schema.role_table_grants WHERE grantee = "'dev'@'localhost'";

FLUSH PRIVILEGES 并不总是必要

通过 GRANT、CREATE ROLE、DROP USER 等 DCL 语句修改权限后，MySQL 自动重载内存中的权限缓存，FLUSH PRIVILEGES 完全不需要。它只在 ** 直接修改 mysql 系统表 **（如用 UPDATE mysql.user SET authentication_string=……）后才需要执行。

滥用 FLUSH PRIVILEGES 会导致短暂的全局锁，阻塞其他权限相关操作，在高并发写入场景下可能引发连接堆积。

• 安全做法：永远用 GRANT/REVOKE 操作权限，避免手写 UPDATE mysql.*
• 如果真改了系统表，且不确定是否生效，先查 SELECT plugin, authentication_string FROM mysql.user WHERE user='xxx'; 确认字段已更新，再执行 FLUSH PRIVILEGES
• MySQL 8.0+ 推荐用 ALTER USER …… IDENTIFIED BY 替代直接更新 authentication_string

角色和用户权限的边界比看起来薄——一个未激活的角色、一条漏掉的 USING、一次多余的 FLUSH，都可能让预期权限失效或意外放大。实际运维中，优先走角色路径，少碰系统表，查权限时记得加 USING。