17370845950

MySQL权限是最小可授单位，角色是权限的命名集合；权限按user→db→tables_priv→columns_priv层级短路匹配，不叠加；角色是8.0+的权限模板，需SET ROLE激活，不自动生效。

MySQL权限和角色不是并列概念，而是“原子”与“组合包”的关系：权限是最小可授单位（比如SELECT、UPDATE），角色是权限的命名集合，本身不直接执行操作，只用于批量分发和统一管理。

权限按层级生效，不是“叠加”而是“短路匹配”

MySQL验证权限时，严格按user → db → tables_priv → columns_priv顺序逐层检查，一旦某层匹配到Y（允许），就立即停止向下查——不会累加或合并多层权限。

• 例如：user表中Select_priv = 'Y'，哪怕db表里该用户对某个库设为'N'，他依然能查所有库
• 反之，如果user表全为'N'，才继续看db表；若db也否，再往下走

SELECT Host, User, Select_priv FROM mysql.user WHERE User = 'dev';
SELECT Host, Db, Select_priv FROM mysql.db WHERE User = 'dev' AND Db = 'mall';

常见踩坑：

• 直接改mysql.user表后忘记执行FLUSH PRIVILEGES，权限不生效
• 创建用户时用了'dev'@'%'，但误以为'dev'@'localhost'也会继承权限——其实它们是完全独立的两个账户
• 用GRANT授了SELECT，却没意识到SHOW CREATE TABLE还需要SELECT或ALTER权限（取决于版本）

角色是MySQL 8.0+的“权限模板”，不是用户，也不能登录

CREATE ROLE创建的是纯逻辑容器，它没有密码、不能连接、不存于mysql.user表，只在mysql.role_edges和mysql.default_roles中记录关系。

• 角色可以被授予其他角色（嵌套），但不能循环引用
• 用户必须显式SET DEFAULT ROLE或SET ROLE才能激活角色权限（会话级生效）
• 激活前，即使已GRANT 'role' TO 'user'@'host'，该用户仍无任何权限

CREATE ROLE 'app_reader';
GRANT
 SELECT ON mall.* TO 'app_reader';
GRANT 'app_reader' TO 'reporter'@'%';
SET DEFAULT ROLE 'app_reader' TO 'reporter'@'%';

关键注意点：

• MySQL 5.7及更早版本不支持角色，CREATE ROLE会报错
• 角色权限变更后，已登录用户的会话不会自动更新，需重新SET ROLE或重连
• SHOW GRANTS FOR 'user'@'host'默认不显示角色权限，要加USING子句：SHOW GRANTS FOR 'reporter'@'%' USING 'app_reader';

什么时候该用权限，什么时候该用角色？

核心判断标准就一条：是否有多人共用同一组权限需求

• 单点授权（如DBA给运维开一个临时备份账号）→ 直接GRANT权限，简单明确
• 多人同权（如12个报表开发都要SELECT全部业务库）→ 必须用角色，否则每次增删人就要重复6条GRANT
• 权限需动态切换（如开发人员白天写SQL、晚上跑ETL）→ 创建'dev_writer'和'etl_runner'两个角色，用SET ROLE按需切换，避免长期持有过高权限

另外：

• WITH GRANT OPTION只能授给用户，不能授给角色（MySQL 8.0.16+才支持角色间传递，且需显式开启activate_all_roles_on_login=ON）
• root用户默认不绑定任何角色，它的权限来自user表中的Super_priv='Y'，不是靠角色继承

权限系统真正的复杂点不在语法，而在于主机名绑定 + 层级短路 + 内存缓存刷新机制三者交织。哪怕GRANT命令执行成功，只要Host字段写错一个字符（比如'192.168.1.0/24'这种非法写法），或者忘了FLUSH PRIVILEGES（直改系统表时），就会卡在“明明给了权限却不生效”的死循环里。