基于字段值对条件表的SQL数据权限匹配实现方案咨询

核心思路 #

放弃硬编码字段匹配的Intersect方案，采用规则命中计数校验逻辑适配动态字段、不等规则数量的场景：将每个用户的所有权限规则视为必须同时满足的AND条件，统计单条员工数据对应用户所有规则的命中数量，若命中数等于该用户的总规则数，即判定该员工对该用户可见。

涉及表定义 #

• 权限规则表 permission_rules：

  -- 示例表结构
  CREATE TABLE permission_rules (
      field VARCHAR(64) NOT NULL,
      value VARCHAR(255) NOT NULL,
      permissioned_user VARCHAR(64) NOT NULL
  );
  

• 员工数据表 employee：包含emp_id、region、division、manager等业务字段。

实现代码 #

WITH user_rule_count AS (
    -- 先统计每个用户配置的权限规则总条数
    SELECT 
        permissioned_user,
        COUNT(*) AS total_rule_cnt
    FROM permission_rules
    GROUP BY permissioned_user
),
emp_rule_match AS (
    -- 逐行匹配员工数据和所有权限规则，统计命中次数
    SELECT
        e.*,
        r.permissioned_user,
        COUNT(*) AS matched_rule_cnt
    FROM employee e
    JOIN permission_rules r
    ON (
        -- 动态匹配对应字段的值，新增字段只需在这里加对应分支即可
        (r.field = 'region' AND e.region = r.value)
        OR (r.field = 'division' AND e.division = r.value)
        OR (r.field = 'manager' AND e.manager = r.value)
        OR (r.field = 'business_unit' AND e.business_unit = r.value)
        OR (r.field = 'dept_head' AND e.dept_head = r.value)
        -- 可继续扩展最多8种甚至更多自定义字段，兼容后续新增场景
    )
    GROUP BY e.emp_id, r.permissioned_user -- 按员工ID和授权用户分组
)
-- 最终输出：命中规则数等于用户总规则数的可见数据
SELECT 
    m.*
FROM emp_rule_match m
JOIN user_rule_count c 
    ON m.permissioned_user = c.permissioned_user
    AND m.matched_rule_cnt = c.total_rule_cnt;

方案优势 #

• 天然适配不同用户规则字段不一致、规则数量不同的场景，不需要为每个用户单独写查询逻辑
• 字段扩展成本极低：新增自定义权限字段时，只需要在ON条件的分支中增加对应字段的匹配逻辑即可，不需要调整其他部分代码
• 支持单字段多值配置：如果同一个用户同一个字段配置了多个允许值，只要修改匹配逻辑为IN或者调整分组计数逻辑即可快速适配。

注意事项 #

• 如果权限规则存在排除型配置（比如禁止访问某区域），只需在ON条件中增加反向匹配分支，同时调整计数逻辑即可
• 员工表主键（此处示例为emp_id）需要作为分组依据，避免重复统计

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