如何查看Snowflake中指定角色下关联的所有用户列表

日常正向查某个用户绑定了哪些角色很方便，反向查某一角色下归属的所有用户，直接调用系统内置的授权视图查询即可，以你要查的svn_dev_admin角色为例，分两种场景：

场景1：仅查询直接被授予该角色的用户 #

直接执行如下SQL即可拿到直接绑定该角色的用户清单，同时会返回授权人、授权时间信息：

SELECT
  grantee_name AS 用户名,
  name AS 关联角色名,
  granted_by AS 授权操作人,
  created_on AS 授权时间
FROM snowflake.account_usage.grants_to_roles
WHERE
  privilege = 'USAGE'
  AND granted_on = 'ROLE'
  AND name = UPPER('svn_dev_admin') -- 替换为你需要查询的目标角色名即可
  -- 过滤掉「角色授予给其他角色」的记录，只保留授权给用户的结果
  AND grantee_name NOT IN (SELECT name FROM snowflake.account_usage.roles);

注意点：

• ACCOUNT_USAGE schema下的视图存在最长2小时的数据延迟，刚完成的授权操作如果没查到，等待1-2小时后重跑即可
• 如果你的账号没有ACCOUNT_USAGE的查询权限，可以替换成INFORMATION_SCHEMA.GRANTS_TO_ROLES表查询，不过该表仅能返回你当前权限可见范围内的授权记录，无法覆盖全账号数据

场景2：查询所有可继承该角色权限的用户（含角色嵌套授权场景） #

如果你的账号存在角色嵌套授权的情况（比如将svn_dev_admin授予给另一个公共角色，拿到公共角色的用户自然也能使用svn_dev_admin的权限），可以用递归CTE把所有嵌套链路下的用户全部查出来：

WITH RECURSIVE role_relation AS (
  -- 锚点：查询直接关联目标角色的授权记录
  SELECT
    grantee_name,
    name AS related_role
  FROM snowflake.account_usage.grants_to_roles
  WHERE
    privilege = 'USAGE'
    AND granted_on = 'ROLE'
    AND name = UPPER('svn_dev_admin')

  UNION ALL

  -- 递归遍历所有嵌套授权的角色链路
  SELECT
    g.grantee_name,
    g.name AS related_role
  FROM snowflake.account_usage.grants_to_roles g
  INNER JOIN role_relation rr ON g.name = rr.grantee_name
  WHERE
    g.privilege = 'USAGE'
    AND g.granted_on = 'ROLE'
)
-- 最终过滤出所有用户类型的主体，排除中间链路的角色
SELECT grantee_name AS 用户名
FROM role_relation
WHERE grantee_name IN (SELECT name FROM snowflake.account_usage.users);

补充说明：如果你当前仅持有svn_dev_admin角色、没有账号级的视图查询权限，返回的结果只会包含你权限可见范围内的用户，需要全量完整名单的话，找持有ACCOUNTADMIN角色的账号管理员执行上述语句即可。

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