适配HUB-CLUSTER-ORG-FACILITY层级的用户权限关联SQL查询

前置说明 #

• 所有实现完全基于给定的三张表现有字段，不做任何表结构调整
• 严格遵循权限规则：某一层级的角色，可查看自身及下属所有层级（直到FACILITY级别）的全部关联角色
• 组织层级固定为：HUB → CLUSTER → ORG → FACILITY

核心实现逻辑 #

以当前登录用户自身关联的所有角色为遍历起点，顺着组织层级向下递归匹配覆盖范围内的所有角色，最终去重得到全量可访问的角色列表：

• 如果用户持有HUB层级角色，匹配对应HUB下所有CLUSTER、ORG、FACILITY层级的全部角色
• 如果用户持有CLUSTER层级角色，匹配对应CLUSTER下所有ORG、FACILITY层级的全部角色
• 如果用户持有ORG层级角色，匹配对应ORG下所有FACILITY层级的全部角色

可落地SQL脚本 #

WITH RECURSIVE permission_scope AS (
    -- 锚点层：取当前用户的自有角色作为遍历起点
    SELECT 
        r.ROLE, r.FACILITY, r.ORG, r.CLUSTER, r.HUB, r.USER
    FROM roles r
    WHERE r.USER = '替换为实际登录用户的唯一标识'

    UNION ALL

    -- 递归层：按层级规则匹配所有下属范围的角色
    SELECT 
        child_r.ROLE, child_r.FACILITY, child_r.ORG, child_r.CLUSTER, child_r.HUB, child_r.USER
    FROM permission_scope ps
    INNER JOIN roles child_r
        ON (
            -- HUB角色匹配同HUB下的所有下属层级角色
            (ps.ROLE = 'HUB' AND child_r.HUB = ps.HUB)
            -- CLUSTER角色匹配同CLUSTER下的所有下属层级角色
            OR (ps.ROLE = 'CLUSTER' AND child_r.CLUSTER = ps.CLUSTER)
            -- ORG角色匹配同ORG下的所有下属层级角色
            OR (ps.ROLE = 'ORG' AND child_r.ORG = ps.ORG)
        )
    -- 关联现有组织表、设施表补全层级映射，避免因角色记录字段不全导致漏匹配
    LEFT JOIN organisations o ON child_r.ORG = o.ORG
    LEFT JOIN facilities f ON child_r.FACILITY = f.NAME
    -- 去重过滤，防止递归死循环
    WHERE NOT EXISTS (
        SELECT 1 FROM permission_scope ps2 
        WHERE ps2.ROLE <=> child_r.ROLE 
          AND ps2.FACILITY <=> child_r.FACILITY
          AND ps2.ORG <=> child_r.ORG
          AND ps2.CLUSTER <=> child_r.CLUSTER
          AND ps2.HUB <=> child_r.HUB
          AND ps2.USER <=> child_r.USER
    )
)
-- 最终返回全量去重后的可访问角色
SELECT DISTINCT * FROM permission_scope;

脚本使用说明 #

• 脚本中替换为实际登录用户的唯一标识需替换为系统实际存储的用户ID
• 匹配逻辑完全对齐规则要求，不会出现跨层级越权匹配的问题
• 关联organisations、facilities表的设计，是为了兼容角色记录中部分层级字段为空的场景，通过关联关系补全匹配条件
• 内置的递归去重逻辑，可避免层级交叉场景下的重复遍历、无限递归问题
• 脚本中的<=>为空安全比较运算符，MySQL环境可直接使用；PostgreSQL环境需替换为等价的IS NOT DISTINCT FROM语法

内容的提问来源于stack exchange，提问作者Riël