Snowflake技术问询：如何列出无Row Access Policy的共享表及已共享表

1. 列出所有正在被共享的表 #

通过Snowflake系统视图可直接查询所有被共享的表，以下SQL返回共享名称、所属库、模式、表名等核心信息：

SELECT
    s.SHARE_NAME,
    so.DATABASE_NAME,
    so.SCHEMA_NAME,
    so.OBJECT_NAME AS TABLE_NAME,
    so.OBJECT_TYPE
FROM
    INFORMATION_SCHEMA.SHARES s
JOIN
    INFORMATION_SCHEMA.SHARE_OBJECTS so
ON
    s.SHARE_NAME = so.SHARE_NAME
WHERE
    so.OBJECT_TYPE = 'TABLE';

关键视图说明： #

• SHARES：存储当前账户下所有已创建的共享配置
• SHARE_OBJECTS：存储每个共享包含的具体对象，通过OBJECT_TYPE = 'TABLE'过滤出表类型对象

2. 列出未分配Row Access Policy的共享表 #

结合共享表列表与已绑定行访问策略的表列表，通过左连接筛选出未配置策略的共享表：

WITH shared_tables AS (
    SELECT
        so.DATABASE_NAME || '.' || so.SCHEMA_NAME || '.' || so.OBJECT_NAME AS FULL_TABLE_NAME
    FROM
        INFORMATION_SCHEMA.SHARES s
    JOIN
        INFORMATION_SCHEMA.SHARE_OBJECTS so
    ON
        s.SHARE_NAME = so.SHARE_NAME
    WHERE
        so.OBJECT_TYPE = 'TABLE'
),
tables_with_row_access_policy AS (
    SELECT
        TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS FULL_TABLE_NAME
    FROM
        INFORMATION_SCHEMA.POLICY_REFERENCES
    WHERE
        POLICY_TYPE = 'ROW_ACCESS_POLICY'
)
SELECT st.FULL_TABLE_NAME
FROM shared_tables st
LEFT JOIN tables_with_row_access_policy twrap
ON st.FULL_TABLE_NAME = twrap.FULL_TABLE_NAME
WHERE twrap.FULL_TABLE_NAME IS NULL;

逻辑说明： #

1. shared_tables CTE生成所有共享表的完整标识符（库.模式.表）
2. tables_with_row_access_policy CTE生成所有已绑定行访问策略的表标识符
3. 左连接后筛选出仅存在于共享表列表、未出现在策略绑定列表中的表，即为目标结果

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