基于角色的访问控制（RBAC）递归SQL查询需求

嘿，我来帮你搞定这个递归权限继承的SQL查询问题！先理清楚你的场景：你设计了支持多父级结构的RBAC数据库Schema，现在需要编写递归SQL，输出role_operation_type的所有权限组合，每个角色还要继承其父级的全部权限对吧？

先把三张核心表的结构定义出来，方便后续写SQL（如果你的表结构和这个有差异，调整对应字段即可）：

• roles表（存储角色，支持多父级）：

  CREATE TABLE roles (
      role_id INT PRIMARY KEY,
      role_name VARCHAR(50) NOT NULL,
      parent_role_ids INT[] -- 多父级用数组存储；如果是单独的关联表（比如role_parents）也可以，后面会说明调整方式
  );
  

• operations表（存储操作类型）：

  CREATE TABLE operations (
      operation_id INT PRIMARY KEY,
      operation_name VARCHAR(50) NOT NULL -- 比如"create", "read", "update", "delete"这类操作
  );
  

• role_operation_type表（存储角色直接拥有的权限，即角色-操作-类型的关联）：

  CREATE TABLE role_operation_type (
      role_id INT REFERENCES roles(role_id),
      operation_id INT REFERENCES operations(operation_id),
      type_id INT NOT NULL, -- 业务类型，比如"user", "post", "comment"
      PRIMARY KEY (role_id, operation_id, type_id)
  );
  

这里用递归CTE（Common Table Expression）来遍历角色的继承链，再关联权限表得到完整的权限组合：

完整查询语句 #

WITH RECURSIVE role_hierarchy AS (
    -- 基础情况：先获取每个角色自身
    SELECT 
        role_id AS current_role_id,
        role_id AS inherited_role_id,
        0 AS hierarchy_level -- 0表示角色自身的权限，层级数越大表示继承的父级越远
    FROM roles
    UNION ALL
    -- 递归情况：遍历所有父级角色，直到没有父级为止
    SELECT 
        rh.current_role_id,
        r.role_id AS inherited_role_id,
        rh.hierarchy_level + 1
    FROM role_hierarchy rh
    JOIN roles r ON r.role_id = ANY(rh.inherited_role_id::INT[]) -- 匹配当前角色的所有父级
)
-- 关联权限表，去重后得到每个角色的全部权限（自身+继承）
SELECT DISTINCT
    rh.current_role_id,
    rot.operation_id,
    rot.type_id
FROM role_hierarchy rh
JOIN role_operation_type rot ON rot.role_id = rh.inherited_role_id
ORDER BY rh.current_role_id, rot.operation_id, rot.type_id;

针对不同场景的调整 #

1. 如果多父级用单独关联表存储：
   比如你用role_parents表（role_id + parent_role_id）来记录多父级关系，递归部分要改成：

   WITH RECURSIVE role_hierarchy AS (
       SELECT role_id AS current_role_id, role_id AS inherited_role_id, 0 AS hierarchy_level
       FROM roles
       UNION ALL
       SELECT rh.current_role_id, rp.parent_role_id, rh.hierarchy_level + 1
       FROM role_hierarchy rh
       JOIN role_parents rp ON rp.role_id = rh.inherited_role_id
   )
   

2. 如果用MySQL 8+：
   MySQL不支持数组类型，所以必须用关联表存储多父级，递归语法类似，只需要把数组相关的逻辑换成表关联即可，核心逻辑和上面一致。

• 去重处理：用DISTINCT是因为一个角色可能通过多个父级继承到同一个权限，避免结果重复。
• 层级标识：保留hierarchy_level字段可以区分权限是角色自身拥有的，还是从父级继承来的，方便后续排查权限来源。
• 性能优化：如果角色数量较多，建议给roles.role_id、role_operation_type.role_id、role_parents的关联字段加索引，提升递归查询的效率。

内容的提问来源于stack exchange，提问作者Gustavo