导出中台项目下账号功能权限

适用模块

项目中心

具体说明

通过mammut数据库，根据项目获取账号权限信息；然后根据数据字典进行权限转换中文显示

使用示例

1、通过mammut数据库，根据项目获取账号权限信息；
注：sql中dataworks为项目名需要根据实际需要进行替换

SELECT
  a.*,
  b.email
FROM
  mammut.pf_policy_perm a
  JOIN (
    SELECT
      a.id,
      c.email
    FROM
      mammut.pf_policy a,
      mammut.pf_policy_role b,
      (
        SELECT
          roleid,
          email
        FROM
          mammut.pf_user_role
        WHERE
          email IN (
            SELECT
              email
            FROM
              mammut.pf_user_account
            WHERE
              account = 'dataworks'
          )
          AND account = 'dataworks'
      ) c
    WHERE
      a.id = b.policyid
      AND a.product = 'dataworks'
      AND b.roleid = c.roleid
  ) b ON a.policyid = b.id
UNION ALL
SELECT
  a.*,
  b.email
FROM
  mammut.pf_policy_perm a
  JOIN (
    SELECT
      a.id,
      b.email
    FROM
      mammut.pf_policy a,
      mammut.pf_policy_user b
    WHERE
      a.id = b.policyid
      AND a.product = 'dataworks'
      AND (
        b.email IN (
          SELECT
            email
          FROM
            mammut.pf_user_account
          WHERE
            account = 'dataworks'
        )
        OR b.email = '*'
      )
      AND type IN (1, 2, 4)
  ) b ON a.policyid = b.id;

2、将查询结果导出至excel，根据数据字典进行权限转换
数据字典参照附件，使用excel =vlookup公式进行批量替换
替换结果展示

作者：林帅