基于元数据 ETL 输出页面授权与最终用户权限

简介

当客户希望系统化梳理“每个页面授给了谁”，并进一步回答“最终哪些用户可以访问这些页面”时，单靠页面权限弹窗逐个核对，效率较低，也不利于后续治理复用。

这类需求通常包含两层口径：

• 页面授权主体明细：页面直接授权给了哪些用户或用户组，哪些页面又继承了父目录批量授权
• 页面最终用户权限：如果页面授权给的是用户组，还需要继续展开到用户组成员，得到最终用户口径的页面权限明细

在 MySQL 5.7 这类不支持递归 CTE 的元数据库环境里，直接用一条长 SQL 虽然可以临时交付，但长期维护成本较高。更推荐的做法是：

• 先用元数据库基础表构建输入数据集
• 再通过 ETL 拆成“页面基础层”“最近批量授权目录层”“页面授权主体层”“最终用户展开层”
• 最终输出两张可复用结果表，供后续页面、卡片和权限治理场景直接复用

最终产出

本次落地了两版 ETL：

1. 页面授权主体明细

• ETL 名称：页面授权明细_ETL版
• 输出数据集：页面授权明细_含批量授权_ETL

建议输出字段包括：

• 域ID
• 页面ID
• 页面名称
• 页面路径
• 授权来源
• 批量授权目录ID
• 批量授权目录名称
• 授权角色
• 主体类型
• 主体ID
• 主体名称
• 用户登录名
• 用户邮箱
• 授权人
• 授权时间

2. 页面最终用户权限

• ETL 名称：页面最终用户权限_ETL版
• 输出数据集：页面最终用户权限_含用户组展开_ETL

建议输出字段包括：

• 域ID
• 页面ID
• 页面名称
• 页面路径
• 页面授权来源
• 批量授权目录ID
• 批量授权目录名称
• 授权角色
• 最终主体类型
• 授权主体类型
• 授权主体ID
• 授权主体名称
• 最终用户ID
• 最终用户名称
• 最终用户登录名
• 最终用户邮箱
• 最终权限来源
• 授权人
• 授权时间

依赖的元数据输入数据集

本次没有直接在查询型数据集里完成复杂权限逻辑，而是先把元数据库基础表拆成独立输入数据集，供 ETL 复用。

实际使用的数据集如下：

• ods_meta_page
• ods_meta_directory
• ods_meta_resource_role_rel
• ods_meta_user
• ods_meta_user_group
• ods_meta_user_group_rel

这些输入数据集分别对应元数据库中的：

• page
• directory
• resource_role_rel
• user
• user_group
• user_group_rel

这种拆法有两个好处：

• ETL 节点职责更清晰，后续排障更容易
• 后面如果还要做“数据集授权”“ETL 授权”“目录授权”类治理，也可以直接复用这些基础输入

输入数据集建议建法

为了让这套方案更容易复现，建议直接把每个输入数据集的建法写清楚，而不是只停留在“依赖哪些输入表”的描述上。

本次元数据输入数据集都可以按“直接读取元数据库原表”的方式准备。

1. ods_meta_page

SELECT
  id,
  pg_id,
  dom_id,
  parent_dir_id,
  pt_id,
  u_id,
  name,
  description,
  pg_type,
  meta,
  ordering,
  ctime,
  utime,
  is_del,
  settings,
  origin_pg_id
FROM page

2. ods_meta_directory

SELECT
  id,
  dom_id,
  u_id,
  dir_id,
  dir_type,
  name,
  parent_dir_id,
  config,
  ctime,
  utime,
  is_del
FROM directory

3. ods_meta_resource_role_rel

SELECT
  rel_id,
  domain_id,
  resource_role,
  resource_id,
  subject_id,
  resource_classifier,
  subject_classifier,
  batch_flag,
  created_on,
  created_by,
  is_del,
  utime
FROM resource_role_rel

4. ods_meta_user

SELECT
  id,
  u_id,
  dom_id,
  name,
  mobile,
  email,
  login_id,
  avatar,
  password,
  role,
  config,
  ctime,
  utime,
  is_del,
  last_login_time,
  pwd_version,
  enabled
FROM user

5. ods_meta_user_group

SELECT
  id,
  ug_id,
  dom_id,
  parent_id,
  name,
  avatar,
  settings,
  ctime,
  utime,
  is_del
FROM user_group

6. ods_meta_user_group_rel

SELECT
  id,
  dom_id,
  u_id,
  group_id,
  ctime,
  utime,
  is_del
FROM user_group_rel

如果客户只想先实现“页面授权主体明细”，则前 5 个输入数据集即可；如果还要实现“最终用户权限”，则再补第 6 个 ods_meta_user_group_rel。

页面授权主体明细 ETL 设计

建议按下面顺序新建节点。

1. 页面基础信息层

先用 page + directory 把页面所在目录往上展开，并拼出页面路径。

这里没有依赖 page_path，而是显式展开最多 6 层目录，主要是为了兼容当前环境使用的 MySQL 5.7。

建议节点名称：页面基础信息

建议输入：

• ods_meta_page
• ods_meta_directory

节点 SQL 如下：

SELECT
  p.dom_id,
  p.pg_id,
  p.name AS page_name,
  p.parent_dir_id,
  p.u_id AS page_owner_id,
  p.ctime,
  p.utime,
  d1.dir_id AS dir_lv1_id,
  d1.name AS dir_lv1_name,
  d2.dir_id AS dir_lv2_id,
  d2.name AS dir_lv2_name,
  d3.dir_id AS dir_lv3_id,
  d3.name AS dir_lv3_name,
  d4.dir_id AS dir_lv4_id,
  d4.name AS dir_lv4_name,
  d5.dir_id AS dir_lv5_id,
  d5.name AS dir_lv5_name,
  d6.dir_id AS dir_lv6_id,
  d6.name AS dir_lv6_name,
  CONCAT_WS(
    '/',
    NULLIF(d6.name, '根目录'),
    NULLIF(d5.name, '根目录'),
    NULLIF(d4.name, '根目录'),
    NULLIF(d3.name, '根目录'),
    NULLIF(d2.name, '根目录'),
    NULLIF(d1.name, '根目录'),
    p.name
  ) AS page_path
FROM input1 p
LEFT JOIN input2 d1
  ON d1.dom_id = p.dom_id
 AND d1.dir_id = p.parent_dir_id
 AND d1.is_del = 0
LEFT JOIN input2 d2
  ON d2.dom_id = p.dom_id
 AND d2.dir_id = d1.parent_dir_id
 AND d2.is_del = 0
LEFT JOIN input2 d3
  ON d3.dom_id = p.dom_id
 AND d3.dir_id = d2.parent_dir_id
 AND d3.is_del = 0
LEFT JOIN input2 d4
  ON d4.dom_id = p.dom_id
 AND d4.dir_id = d3.parent_dir_id
 AND d4.is_del = 0
LEFT JOIN input2 d5
  ON d5.dom_id = p.dom_id
 AND d5.dir_id = d4.parent_dir_id
 AND d5.is_del = 0
LEFT JOIN input2 d6
  ON d6.dom_id = p.dom_id
 AND d6.dir_id = d5.parent_dir_id
 AND d6.is_del = 0
WHERE p.is_del = 0

2. 页面直接授权层

直接从 resource_role_rel 中读取：

• resource_classifier = 'DASHBOARD'
• batch_flag = 0
• resource_id = page.pg_id

这一层回答的是：页面本身直接授给了哪些用户或用户组。

建议节点名称：页面直接授权

建议输入：

• 上一步 页面基础信息
• ods_meta_resource_role_rel

节点 SQL 如下：

SELECT
  pb.dom_id,
  pb.pg_id,
  pb.page_name,
  pb.page_path,
  '页面直接授权' AS auth_source,
  CAST(NULL AS STRING) AS batch_from_dir_id,
  CAST(NULL AS STRING) AS batch_from_dir_name,
  rr.resource_role,
  rr.subject_classifier,
  rr.subject_id,
  rr.created_by,
  rr.created_on
FROM input1 pb
JOIN input2 rr
  ON rr.domain_id = pb.dom_id
 AND rr.resource_id = pb.pg_id
 AND rr.resource_classifier = 'DASHBOARD'
 AND rr.batch_flag = 0
 AND rr.is_del = 0

3. 最近批量授权目录层

这一层是整套方案的关键。

在 resource_role_rel 中，当：

• batch_flag = 1
• resource_classifier = 'DASHBOARD'

时，resource_id 记录的不是页面 ID，而是批量授权目录 ID。

因此需要先从页面所在目录开始，逐层向上检查父目录，看哪一层存在页面批量授权。这里采用的口径是：

• 只取离当前页面最近的一层批量授权目录
• 如果更高层目录也有批量授权，不继续向上继承

这样和产品中的“最近祖先目录继承批量授权”口径更一致。

建议节点名称：最近批量授权目录

建议输入：

• 页面基础信息
• ods_meta_resource_role_rel

节点 SQL 如下：

SELECT
  pb.dom_id,
  pb.pg_id,
  pb.page_name,
  pb.page_path,
  COALESCE(
    b1.resource_id,
    b2.resource_id,
    b3.resource_id,
    b4.resource_id,
    b5.resource_id,
    b6.resource_id
  ) AS batch_dir_id
FROM input1 pb
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b1 ON b1.domain_id = pb.dom_id AND b1.resource_id = pb.dir_lv1_id
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b2 ON b2.domain_id = pb.dom_id AND b2.resource_id = pb.dir_lv2_id
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b3 ON b3.domain_id = pb.dom_id AND b3.resource_id = pb.dir_lv3_id
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b4 ON b4.domain_id = pb.dom_id AND b4.resource_id = pb.dir_lv4_id
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b5 ON b5.domain_id = pb.dom_id AND b5.resource_id = pb.dir_lv5_id
LEFT JOIN (
  SELECT DISTINCT domain_id, resource_id
  FROM input2
  WHERE is_del = 0
    AND batch_flag = 1
    AND resource_classifier = 'DASHBOARD'
) b6 ON b6.domain_id = pb.dom_id AND b6.resource_id = pb.dir_lv6_id
WHERE COALESCE(b1.resource_id, b2.resource_id, b3.resource_id, b4.resource_id, b5.resource_id, b6.resource_id) IS NOT NULL

4. 批量授权继承明细层

拿到最近批量授权目录后，再回连 resource_role_rel，展开该目录下的实际授权主体和角色。

这样就能得到“页面通过父目录批量授权继承而来的访问权限”。

建议节点名称：批量授权继承明细

建议输入：

• 最近批量授权目录
• ods_meta_directory
• ods_meta_resource_role_rel

节点 SQL 如下：

SELECT
  nbd.dom_id,
  nbd.pg_id,
  nbd.page_name,
  nbd.page_path,
  '父目录批量授权继承' AS auth_source,
  nbd.batch_dir_id AS batch_from_dir_id,
  d.name AS batch_from_dir_name,
  rr.resource_role,
  rr.subject_classifier,
  rr.subject_id,
  rr.created_by,
  rr.created_on
FROM input1 nbd
JOIN input2 d
  ON d.dom_id = nbd.dom_id
 AND d.dir_id = nbd.batch_dir_id
 AND d.is_del = 0
JOIN input3 rr
  ON rr.domain_id = nbd.dom_id
 AND rr.resource_id = nbd.batch_dir_id
 AND rr.resource_classifier = 'DASHBOARD'
 AND rr.batch_flag = 1
 AND rr.is_del = 0

5. 页面授权主体合并层

最后把：

• 页面直接授权
• 父目录批量授权继承

合并成一张统一的页面授权主体明细表。

建议节点名称：页面授权主体明细

建议输入：

• 页面直接授权
• 批量授权继承明细
• ods_meta_user
• ods_meta_user_group

节点 SQL 如下：

SELECT
  x.dom_id AS `域ID`,
  x.pg_id AS `页面ID`,
  x.page_name AS `页面名称`,
  x.page_path AS `页面路径`,
  x.auth_source AS `授权来源`,
  x.batch_from_dir_id AS `批量授权目录ID`,
  x.batch_from_dir_name AS `批量授权目录名称`,
  CASE x.resource_role
    WHEN 0 THEN '所有者'
    WHEN 1 THEN '访问者'
    WHEN 2 THEN '导出者'
    WHEN 3 THEN '目录访问者'
    ELSE CONCAT('未知角色(', CAST(x.resource_role AS STRING), ')')
  END AS `授权角色`,
  x.subject_classifier AS `主体类型`,
  x.subject_id AS `主体ID`,
  CASE
    WHEN x.subject_classifier = 'USER' THEN u.name
    WHEN x.subject_classifier = 'GROUP' THEN ug.name
    ELSE NULL
  END AS `主体名称`,
  CASE WHEN x.subject_classifier = 'USER' THEN u.login_id ELSE NULL END AS `用户登录名`,
  CASE WHEN x.subject_classifier = 'USER' THEN u.email ELSE NULL END AS `用户邮箱`,
  cu.name AS `授权人`,
  x.created_on AS `授权时间`
FROM (
  SELECT * FROM input1
  UNION ALL
  SELECT * FROM input2
) x
LEFT JOIN input3 u
  ON x.subject_classifier = 'USER'
 AND x.subject_id = u.u_id
 AND x.dom_id = u.dom_id
 AND u.is_del = 0
LEFT JOIN input4 ug
  ON x.subject_classifier = 'GROUP'
 AND x.subject_id = ug.ug_id
 AND x.dom_id = ug.dom_id
 AND ug.is_del = 0
LEFT JOIN input3 cu
  ON x.created_by = cu.u_id
 AND x.dom_id = cu.dom_id
 AND cu.is_del = 0

页面最终用户权限 ETL 设计

第二版 ETL 不是重新计算页面授权，而是在“页面授权主体明细”的基础上，继续把授权主体展开到最终用户。

1. 直接用户授权支路

如果授权主体本身就是 USER，则直接回连 user 表，补齐：

• 用户名
• 登录名
• 邮箱

并将 最终权限来源 标记为 直接用户授权。

2. 用户组成员继承支路

如果授权主体是 GROUP，则需要先通过 user_group_rel 找到组内成员，再回连 user 表，得到最终用户口径的权限明细。

这一层输出时会保留：

• 授权主体还是哪个用户组
• 最终用户是谁
• 最终权限来源是 用户组成员继承

3. 最终用户权限合并层

最后将两条支路合并，就能得到一张最终用户粒度的页面权限明细表。

建议节点名称：页面最终用户权限

建议输入：

• 页面授权主体明细
• ods_meta_user
• ods_meta_user_group
• ods_meta_user_group_rel

节点 SQL 如下：

SELECT DISTINCT
  x.dom_id AS `域ID`,
  x.pg_id AS `页面ID`,
  x.page_name AS `页面名称`,
  x.page_path AS `页面路径`,
  x.auth_source AS `页面授权来源`,
  x.batch_from_dir_id AS `批量授权目录ID`,
  x.batch_from_dir_name AS `批量授权目录名称`,
  CASE x.resource_role
    WHEN 0 THEN '所有者'
    WHEN 1 THEN '访问者'
    WHEN 2 THEN '导出者'
    WHEN 3 THEN '目录访问者'
    ELSE CONCAT('未知角色(', CAST(x.resource_role AS STRING), ')')
  END AS `授权角色`,
  'USER' AS `最终主体类型`,
  x.subject_classifier AS `授权主体类型`,
  x.subject_id AS `授权主体ID`,
  CASE
    WHEN x.subject_classifier = 'USER' THEN u.name
    WHEN x.subject_classifier = 'GROUP' THEN ug.name
    ELSE NULL
  END AS `授权主体名称`,
  u.u_id AS `最终用户ID`,
  u.name AS `最终用户名称`,
  u.login_id AS `最终用户登录名`,
  u.email AS `最终用户邮箱`,
  '直接用户授权' AS `最终权限来源`,
  cu.name AS `授权人`,
  x.created_on AS `授权时间`
FROM input1 x
JOIN input2 u
  ON x.subject_classifier = 'USER'
 AND x.subject_id = u.u_id
 AND x.dom_id = u.dom_id
 AND u.is_del = 0
LEFT JOIN input3 ug
  ON x.subject_classifier = 'GROUP'
 AND x.subject_id = ug.ug_id
 AND x.dom_id = ug.dom_id
 AND ug.is_del = 0
LEFT JOIN input2 cu
  ON x.created_by = cu.u_id
 AND x.dom_id = cu.dom_id
 AND cu.is_del = 0

UNION ALL

SELECT DISTINCT
  x.dom_id AS `域ID`,
  x.pg_id AS `页面ID`,
  x.page_name AS `页面名称`,
  x.page_path AS `页面路径`,
  x.auth_source AS `页面授权来源`,
  x.batch_from_dir_id AS `批量授权目录ID`,
  x.batch_from_dir_name AS `批量授权目录名称`,
  CASE x.resource_role
    WHEN 0 THEN '所有者'
    WHEN 1 THEN '访问者'
    WHEN 2 THEN '导出者'
    WHEN 3 THEN '目录访问者'
    ELSE CONCAT('未知角色(', CAST(x.resource_role AS STRING), ')')
  END AS `授权角色`,
  'USER' AS `最终主体类型`,
  x.subject_classifier AS `授权主体类型`,
  x.subject_id AS `授权主体ID`,
  ug.name AS `授权主体名称`,
  u.u_id AS `最终用户ID`,
  u.name AS `最终用户名称`,
  u.login_id AS `最终用户登录名`,
  u.email AS `最终用户邮箱`,
  '用户组成员继承' AS `最终权限来源`,
  cu.name AS `授权人`,
  x.created_on AS `授权时间`
FROM input1 x
JOIN input3 ug
  ON x.subject_classifier = 'GROUP'
 AND x.subject_id = ug.ug_id
 AND x.dom_id = ug.dom_id
 AND ug.is_del = 0
JOIN input4 ugr
  ON ugr.group_id = ug.ug_id
 AND ugr.dom_id = ug.dom_id
 AND ugr.is_del = 0
JOIN input2 u
  ON u.u_id = ugr.u_id
 AND u.dom_id = ugr.dom_id
 AND u.is_del = 0
LEFT JOIN input2 cu
  ON x.created_by = cu.u_id
 AND x.dom_id = cu.dom_id
 AND cu.is_del = 0

ETL 截图

1. 页面授权主体明细 ETL 总览

2. 页面最终用户权限 ETL 总览

从截图可以看到，两版 ETL 都采用了“多个元数据输入 + 多个 SQL 节点分层处理 + 输出数据集”的结构。

其中第二版比第一版多了一层 user_group_rel 输入，以及最终用户展开逻辑。

关键实现说明

1. 为什么不用一条 SQL 全做完

如果只是一次性排查，单 SQL 可以先给结果。

但如果希望这套权限结果长期复用，ETL 有几个明显优势：

• 逻辑拆层更清楚
• 每一层都可以单独预览和排错
• 后续新增字段或扩展口径更方便
• 更适合作为页面治理和权限审计的数据资产沉淀

2. 为什么只取最近一层批量授权目录

这是为了贴近产品权限继承口径。

如果页面父目录和更上层祖先目录都存在批量授权，通常应该以离当前页面最近的一层目录为准。如果把所有祖先目录全部展开，会把权限关系放大，不利于排查。

3. 当前层级上限说明

本次页面目录向上展开写死为 6 层。

如果客户环境中的页面目录层级可能超过 6 层，建议把这两版 ETL 一并扩到 10 层或 12 层；如果目录树更深且结构长期变化，建议进一步沉淀一张目录祖先链中间表。

4. 目录层级扩展时如何改写

如果需要把当前方案从 6 层扩到 10 层，建议按下面的顺序同步改写，避免只改一处导致口径不一致：

第一步：扩展页面基础层

先修改 page_base.sql，继续往上补目录自连接，例如新增：

• d7.dir_id AS dir_lv7_id
• d7.name AS dir_lv7_name
• d8.dir_id AS dir_lv8_id
• d8.name AS dir_lv8_name

同时在 CONCAT_WS 中把新增目录名也补进去。

也就是说，这一层负责同时扩两件事：

• 页面的父目录链字段
• 页面路径字段

如果只补了 dir_lv7_id 但没有补 page_path，后面虽然权限继承能找到，但页面路径展示会断层。

第二步：扩展最近批量授权目录层

再修改 nearest_batch_dir.sql，按同样方式继续补：

• b7
• b8
• b9
• b10

并把 COALESCE(b1, b2, ... b6) 扩成：

• COALESCE(b1, b2, ... b10)

这一层的核心要求是：目录层级字段扩到哪里，批量授权检查层就必须跟着扩到哪里。

否则会出现一种常见问题：

• 页面路径已经能看到第 10 层目录
• 但批量授权仍然只检查到第 6 层
• 最终导致高层目录授权被漏算

第三步：两版 ETL 要一起改

本次落地的是两版 ETL：

• 页面授权主体明细 ETL
• 页面最终用户权限 ETL

这两版都各自有一份 page_base.sql 和 nearest_batch_dir.sql，所以扩层时必须同时修改两边，不能只改其中一版。

第四步：验证时重点看两类页面

改完后建议重点验证两类页面：

• 目录层级超过原始上限的页面
• 批量授权挂在高层祖先目录的页面

如果这两类页面都能正常出数，说明扩层修改基本是完整的。

推荐做法

如果只是预防性扩容，建议直接从 6 层扩到 10 层即可，维护成本还能接受。

如果已经明确存在更深目录树，或者后续层级变化会很频繁，则更推荐单独沉淀“目录祖先链”中间表，不再长期依赖手工加层的写法。

5. 当前用户组展开口径说明

本次最终用户权限 ETL 使用的是 user_group_rel 的直接成员关系。

如果客户环境中存在“用户组套用户组”的嵌套组场景，则还需要继续扩展一层递归展开逻辑；否则当前结果只覆盖直接组成员，不覆盖嵌套组成员。

小结

如果需求只是“看页面授权给了谁”，建议直接使用第一版 ETL。

如果需求已经进入“谁最终能访问这些页面”的治理场景，则建议直接落第二版 ETL，把用户组成员展开也一起做掉。这样后续无论是做权限审计、页面治理还是异常排查，都更容易形成统一口径。