17370845950

MySQL 8.0+ 中需先用 CREATE ROLE 创建角色，再 GRANT 授权，最后通过 GRANT role TO user 绑定用户并 SET DEFAULT ROLE 激活；角色权限不显示在 SHOW GRANTS FOR user 中，须用 SHOW GRANTS FOR ROLE 查看。

MySQL 8.0+ 中如何创建角色并赋予权限

MySQL 5.7 不支持角色（ROLE），只有 MySQL 8.0 及以上版本才原生支持。创建角色本质是定义一组权限模板，后续可批量授予用户，而非逐个 GRANT。必须用 CREATE ROLE 显式创建，不能跳过这步直接授权。

常见错误：执行 GRANT SELECT ON db.* TO 'role_name'@'%' 报错 ERROR 1105 (HY000): Unknown error 或 ERROR 1396 (HY000): Operation CREATE ROLE failed —— 因为角色未预先创建，或当前用户缺少 CREATE ROLE 权限。

• CREATE ROLE 'analyst'@'%'; 创建角色（注意引号和主机名，与用户语法一致）
• GRANT SELECT, SHOW VIEW ON sales.* TO 'analyst'@'%'; 向角色授予权限（不能省略 TO role_name）
• FLUSH PRIVILEGES; 非必需，但部分部署中权限缓存可能延迟生效，建议执行

如何把角色分配给用户

角色本身不登录、不连接，必须通过 GRANT role_name TO user_name 绑定到具体用户。绑定后用户需显式激活角色（除非设为默认角色），否则权限不生效。

典型场景：一个 DBA 用户管理多个业务账号，希望统一控制只读权限范围。此时不应直接给每个用户重复授 SELECT，而应先建 'readonly' 角色，再批量绑定。

• CREATE USER 'u1'@'localhost' IDENTIFIED BY 'pwd123'; 先确保用户存在
• GRANT 'analyst'@'%' TO 'u1'@'localhost'; 注意语法：左边是角色，右边是用户，顺序不可颠倒
• SET DEFAULT ROLE 'analyst'@'%' TO 'u1'@'localhost'; 设为默认角色，用户登录即自动启用
• 若不设默认角色，用户需手动执行 SET ROLE 'analyst'@'%'; 才能使用该角色权限

查看角色权限与激活状态

角色权限不会自动出现在 SHOW GRANTS FOR user 中，必须查 mysql.role_edges 和 mysql.role_graph 等系统表，或用 SHOW GRANTS FOR ROLE 'role_name'（MySQL 8.0.16+ 支持）。

容易忽略的点：用户登录后即使绑定了角色，CURRENT_ROLE() 返回 NONE，说明角色未激活 —— 这时 SELECT 仍会报 ERROR 1142 (42000): SELECT command denied。

• SHOW GRANTS FOR ROLE 'analyst'@'%'; 查看角色被授予了哪些权限
• SELECT * FROM mysql.role_edges WHERE TO_HOST = '%'; 查看哪些用户绑定了该角色
• SELECT CURRENT_ROLE(); 在用户会话中检查当前激活的角色
• SHOW GRANTS; 在用户会话中运行，仅显示显式授予用户的权限，不含角色权限（除非已激活）

删除角色及回收权限的注意事项

删除角色前必须先解除所有绑定，否则报错 ERROR 3524 (HY000): Cannot drop role 'xxx'@'%' because it is still granted to one or more users。角色删除后，其权限定义彻底消失，但已激活过的用户会话可能仍缓存权限，需重新连接。

权限回收分两层：一是取消用户与角色的绑定（REVOKE role FROM user），二是删除角色本身（DROP ROLE）。后者不可逆，且不会自动撤销已激活会话中的权限。

• REVOKE 'analyst'@'%' FROM 'u1'@'localhost'; 先解绑
• DROP ROLE 'analyst'@'%'; 再删除角色
• 如果误删角色，只能重建角色并重新授予权限，无法恢复历史配置
• 生产环境建议用脚本批量处理绑定关系，避免遗漏用户

SELECT CONCAT('REVOKE ''', TO_USER, '''@''', TO_HOST, ''' FROM ''', FROM_USER, '''@''', FROM_HOST, ''' ;') 
FROM mysql.role_edges 
WHERE TO_USER = 'analyst';

角色机制在多租户、权限分级管理中很实用，但 MySQL 的角色不支持嵌套（角色不能授予另一个角色），也不支持动态参数化权限（比如按用户名过滤库名），这些限制需要靠应用层或外部权限系统补足。