Redshift中如何遍历svv_role_grants获取角色全部继承角色？递归CTE报错

Redshift的递归CTE不支持在递归分支（UNION ALL后的查询部分）直接引用系统视图svv_role_grants，这就是你遇到SQL Error [0A000]的核心原因。

解决方法：借助临时表实现递归查询 #

由于系统视图无法直接用于递归CTE的递归分支，我们可以先把角色授权数据导出到临时表，再基于临时表执行递归逻辑。

1. 创建临时表存储角色授权数据 #

执行以下SQL将svv_role_grants的内容复制到临时表：

CREATE TEMP TABLE temp_role_grants AS
SELECT role_name, granted_role_name
FROM svv_role_grants;

临时表是会话级别的，会话结束后会自动销毁，无需手动清理。

2. 基于临时表执行递归CTE #

使用临时表替代原系统视图，修改后的递归查询如下：

WITH RECURSIVE subordinates(role_name, granted_role_name) AS (
    SELECT role_name, granted_role_name
    FROM temp_role_grants
    WHERE role_name IN ('role_a','role_b')
    UNION ALL
    SELECT rg.role_name, rg.granted_role_name
    FROM temp_role_grants rg
    INNER JOIN subordinates s ON s.granted_role_name = rg.role_name
)
SELECT * FROM subordinates;

可选：添加递归深度限制（避免循环） #

如果角色授权存在循环引用（比如角色A授予角色B，角色B又授予角色A），递归会进入死循环。可以添加深度字段限制递归次数：

WITH RECURSIVE subordinates(role_name, granted_role_name, depth) AS (
    SELECT role_name, granted_role_name, 1 AS depth
    FROM temp_role_grants
    WHERE role_name IN ('role_a','role_b')
    UNION ALL
    SELECT rg.role_name, rg.granted_role_name, s.depth + 1
    FROM temp_role_grants rg
    INNER JOIN subordinates s ON s.granted_role_name = rg.role_name
    WHERE s.depth < 10 -- 根据实际情况调整最大深度
)
SELECT * FROM subordinates;

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