如何用SQL从Snowflake单表生成角色权限矩阵？

要实现从SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES表生成指定格式的权限矩阵，完全可以通过SQL的**透视（Pivot）**逻辑实现，以下针对你的场景提供两种方案：

1. 静态透视（适合少量角色列） #

如果角色数量较少，可手动编写CASE语句实现透视，对应你给出的示例数据，SQL如下：

SELECT
  Name,
  -- 判断Role1是否拥有该对象的Select权限
  MAX(CASE WHEN Granted_On = 'Role1' AND Privilege = 'Select' THEN TRUE ELSE FALSE END) AS Role1,
  -- 判断Role2是否拥有该对象的Select权限
  MAX(CASE WHEN Granted_On = 'Role2' AND Privilege = 'Select' THEN TRUE ELSE FALSE END) AS Role2,
  -- 判断Role3是否拥有该对象的Select权限
  MAX(CASE WHEN Granted_On = 'Role3' AND Privilege = 'Select' THEN TRUE ELSE FALSE END) AS Role3
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE Privilege = 'Select' -- 仅统计Select权限，可根据需求调整或删除
GROUP BY Name
ORDER BY Name;

逻辑说明： #

• 用CASE语句标记每个角色对当前对象是否存在指定权限，存在则返回TRUE，否则FALSE
• 通过MAX聚合函数保留有效结果（同一对象+角色+权限最多一条记录，MAX会优先保留TRUE）

2. 动态透视（适合大量角色列，如你的175列场景） #

当角色数量较多时，手动编写CASE语句不现实，可通过Snowflake的动态SQL自动生成透视列：

步骤1：生成动态透视列语句 #

先查询所有唯一角色，拼接出对应的CASE语句：

SET pivot_columns = (
  SELECT LISTAGG(DISTINCT 
    'MAX(CASE WHEN Granted_On = ''' || Granted_On || ''' AND Privilege = ''Select'' THEN TRUE ELSE FALSE END) AS "' || Granted_On || '"'
  , ', ') WITHIN GROUP (ORDER BY Granted_On)
  FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
  WHERE Privilege = 'Select' -- 过滤目标权限，按需调整
);

步骤2：执行动态生成的SQL #

通过EXECUTE IMMEDIATE执行拼接好的完整SQL：

EXECUTE IMMEDIATE '
SELECT
  Name,
  ' || $pivot_columns || '
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE Privilege = ''Select''
GROUP BY Name
ORDER BY Name;
';

关键说明： #

• LISTAGG函数将所有角色对应的CASE语句拼接成一个字符串，自动生成所有角色列
• 若需要统计所有权限（而非仅Select），可删除WHERE Privilege = 'Select'过滤，并将CASE语句调整为CASE WHEN Granted_On = ''' || Granted_On || ''' THEN TRUE ELSE FALSE END
• 角色名称含特殊字符（空格、引号等）时，SQL中用双引号包裹列名、单引号转义的方式可兼容大部分场景

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