如何在Snowflake中合并多个SHOW GRANTS OF ROLE语句的结果

要批量获取10个角色的权限详情并合并到同一结果表中，你可以利用Snowflake的RESULT_SCAN函数捕获SHOW GRANTS命令的输出，再通过临时表或直接UNION ALL合并结果，具体有两种常用方法：

方法一：使用临时表逐步合并 #

这种方式步骤清晰，适合需要对结果进行后续过滤、排序等操作的场景：

-- 初始化临时表，存储第一个角色的权限结果
SHOW GRANTS OF ROLE ROLENAME_1;
CREATE OR REPLACE TEMPORARY TABLE ALL_ROLE_GRANTS AS 
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- 依次查询剩余角色并将结果插入临时表
SHOW GRANTS OF ROLE ROLENAME_2;
INSERT INTO ALL_ROLE_GRANTS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

SHOW GRANTS OF ROLE ROLENAME_3;
INSERT INTO ALL_ROLE_GRANTS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- 重复上述INSERT步骤，直到处理完ROLENAME_10

-- 查询合并后的所有权限数据
SELECT * FROM ALL_ROLE_GRANTS;

方法二：直接用UNION ALL合并结果 #

如果不需要临时存储，可直接串联多个RESULT_SCAN的查询结果：

SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_1)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_2)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_3)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_4)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_5)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_6)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_7)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_8)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_9)))))
UNION ALL
SELECT * FROM TABLE(RESULT_SCAN((SELECT LAST_QUERY_ID() FROM (SHOW GRANTS OF ROLE ROLENAME_10)))))

关键说明 #

• RESULT_SCAN(LAST_QUERY_ID())用于获取上一条SHOW GRANTS命令的输出，因为SHOW类命令属于Snowflake扩展命令，无法直接用UNION ALL串联。
• 临时表ALL_ROLE_GRANTS会在会话结束后自动销毁，无需手动清理。

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