mysql中动态权限控制与会话级权限管理

MySQL 8.0+ 中 SET ROLE 不起作用，是因为角色需先被授予用户且显式激活：管理员执行 GRANT 和 SET DEFAULT ROLE 后，用户仍需在会话中运行 SET ROLE 或配置 activate_all_roles_on_login=ON。

MySQL 8.0+ 中 SET ROLE 为什么不起作用？

会话级权限管理依赖角色（ROLE）机制，但直接 SET ROLE 'role_name' 失败，通常是因为角色未被显式授予当前用户，或未启用角色激活机制。

• CREATE ROLE 'analyst' 后必须执行 GRANT SELECT ON sales.* TO 'analyst'，再 GRANT 'analyst' TO 'user1'@'%'
• 用户登录后默认不激活任何角色，需手动 SET DEFAULT ROLE 'analyst' TO 'user1'@'%'（注意：这是在管理员会话中执行的授权语句，不是用户自己运行）
• 用户首次连接后，还需在自己的会话中运行 SET ROLE 'analyst'，或提前配置 SET DEFAULT ROLE ALL TO 'user1'@'%' 实现自动激活
• 检查是否启用角色支持：SELECT @@global.activate_all_roles_on_login —— 若为 O

  

  FF（默认），则不会自动激活，默认角色需显式 SET ROLE

如何用 CONNECTION_ATTRS + 应用层传参实现动态权限过滤？

MySQL 本身不支持“按会话变量值动态限制行”，但可通过应用层写入连接属性，配合 information_schema 或代理层规则做轻量路由/拦截；更常用的是在 SQL 层用 USER() 或自定义变量结合视图封装。

• 应用连接时设置属性：mysql --defaults-file=client.cnf --connect-expired-password -A --init-command="SET @app_user_id = 123; SET @app_tenant_id = 'tenant_a'"
• 建视图时引用：

  CREATE VIEW user_orders AS
  SELECT * FROM orders 
  WHERE tenant_id = @app_tenant_id 
    AND (status != 'draft' OR created_by = @app_user_id);

• 注意：@app_* 是会话级用户变量，只在当前连接有效；不能用于存储过程内联编译，也不能替代行级安全策略（RLS）
• 真正动态权限控制建议上推到中间件（如 ProxySQL、MaxScale）或 ORM 层，MySQL 原生 RLS（CREATE ROW POLICY）目前仅限 HeatWave 和部分云托管版本，社区版不支持

REVOKE 会话权限后，为什么旧查询还能跑？

MySQL 的权限检查发生在语句解析阶段，不是执行阶段。已预编译的语句（如存储过程、预处理语句）或长事务中的后续语句，不会因中途 REVOKE 而中断。

• REVOKE SELECT ON db.tbl FROM 'u'@'%' 仅影响该用户下一次新连接或新语句的权限校验
• 正在运行的查询、已打开的游标、处于 START TRANSACTION 中但尚未提交的 DML，全部不受影响
• 若需立即生效，唯一办法是杀掉对应连接：KILL CONNECTION （查 SHOW PROCESSLIST 获取 ID）
• 临时权限提升场景（如 DBA 临时授权）务必搭配连接生命周期管理，避免权限残留

会话级权限 vs 全局权限：哪些操作真的只能靠 SET SESSION？

严格来说，MySQL 没有“会话级权限”这个概念——所有权限都是账户级（GRANT ... ON ... TO）或角色级。但某些系统变量可会话级修改，间接影响行为边界，常被误认为“权限”。

• SET SESSION max_execution_time = 3000：限制单条语句最长执行时间（毫秒），超时返回 Query execution was interrupted, maximum statement execution time exceeded
• SET SESSION sort_buffer_size = 4194304：影响 ORDER BY / GROUP BY 性能，但不改变权限范围
• SET SESSION sql_mode = 'STRICT_TRANS_TABLES'：影响数据校验强度，与权限无关
• 真正和权限强相关的只有 SET ROLE 和 SET DEFAULT ROLE —— 它们切换的是已授予角色的激活状态，不是授予权限本身

实际落地时，最易忽略的是角色激活的两步分离：管理员要先 GRANT + SET DEFAULT ROLE，用户登录后仍需 SET ROLE（除非 activate_all_roles_on_login=ON）。很多团队卡在这一步，以为授了角色就自动生效。