无Account Admin权限下Snowflake标签、关联对象及掩码策略查询咨询

1. 列出所有表、视图对象 #

直接查询当前数据库内所有schema的表和视图：

-- 查询当前数据库内所有表
SELECT 
    table_catalog AS database_name,
    table_schema AS schema_name,
    table_name,
    'TABLE' AS object_type
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

UNION ALL

-- 查询当前数据库内所有视图
SELECT 
    table_catalog AS database_name,
    table_schema AS schema_name,
    table_name,
    'VIEW' AS object_type
FROM information_schema.views
ORDER BY database_name, schema_name, object_type, table_name;

2. 批量列出所有带标签的对象（表、视图、列）及对应标签 #

利用Snowflake SQL Scripting实现批量遍历查询，自动收集所有对象的标签信息：

DECLARE
    cur CURSOR FOR 
        SELECT table_catalog, table_schema, table_name, 'TABLE' AS obj_type
        FROM information_schema.tables
        WHERE table_type = 'BASE TABLE'
        UNION ALL
        SELECT table_catalog, table_schema, table_name, 'VIEW' AS obj_type
        FROM information_schema.views;
    v_db STRING;
    v_schema STRING;
    v_obj STRING;
    v_type STRING;
    v_sql STRING;
    result RESULTSET;
BEGIN
    -- 创建临时表存储标签查询结果
    CREATE OR REPLACE TEMPORARY TABLE tag_results (
        database_name STRING,
        schema_name STRING,
        object_name STRING,
        object_type STRING,
        column_name STRING,
        tag_database STRING,
        tag_schema STRING,
        tag_name STRING,
        tag_value STRING
    );

    FOR record IN cur DO
        v_db := record.table_catalog;
        v_schema := record.table_schema;
        v_obj := record.table_name;
        v_type := record.obj_type;
        
        -- 构造动态SQL查询当前对象的所有标签
        v_sql := 'INSERT INTO tag_results
                  SELECT ''' || v_db || ''', ''' || v_schema || ''', ''' || v_obj || ''', ''' || v_type || ''',
                         column_name, tag_database, tag_schema, tag_name, tag_value
                  FROM table(information_schema.tag_references_all_columns(''' || v_db || '.' || v_schema || '.' || v_obj || ''', ''' || v_type || '''))';
        
        result := (EXECUTE IMMEDIATE v_sql);
    END FOR;

    -- 返回最终标签结果
    SELECT * FROM tag_results ORDER BY database_name, schema_name, object_name, column_name;
END;

3. 列出对象、关联标签及绑定的掩码策略 #

结合标签查询结果，批量获取标签绑定的掩码策略并关联到对应对象：

DECLARE
    tag_cur CURSOR FOR 
        SELECT DISTINCT tag_database, tag_schema, tag_name
        FROM tag_results; -- 依赖上一步生成的临时表，若需独立执行可合并上一步逻辑
    v_tag_db STRING;
    v_tag_schema STRING;
    v_tag_name STRING;
    v_sql STRING;
    policy_result RESULTSET;
BEGIN
    -- 创建临时表存储标签与策略的关联关系
    CREATE OR REPLACE TEMPORARY TABLE tag_policy_results (
        tag_database STRING,
        tag_schema STRING,
        tag_name STRING,
        policy_database STRING,
        policy_schema STRING,
        policy_name STRING,
        policy_type STRING
    );

    FOR tag_record IN tag_cur DO
        v_tag_db := tag_record.tag_database;
        v_tag_schema := tag_record.tag_schema;
        v_tag_name := tag_record.tag_name;
        
        -- 构造动态SQL查询当前标签绑定的掩码策略
        v_sql := 'INSERT INTO tag_policy_results
                  SELECT ''' || v_tag_db || ''', ''' || v_tag_schema || ''', ''' || v_tag_name || ''',
                         policy_catalog, policy_schema, policy_name, policy_type
                  FROM table(information_schema.policy_references(
                      POLICY_NAME => ''' || v_tag_db || '.' || v_tag_schema || '.' || v_tag_name || ''',
                      POLICY_TYPE => ''MASKING''
                  ))';
        
        policy_result := (EXECUTE IMMEDIATE v_sql);
    END FOR;

    -- 关联对象、标签与策略信息，返回完整结果
    SELECT 
        tr.database_name,
        tr.schema_name,
        tr.object_name,
        tr.object_type,
        tr.column_name,
        tr.tag_name,
        tr.tag_value,
        tpr.policy_name,
        tpr.policy_type
    FROM tag_results tr
    LEFT JOIN tag_policy_results tpr 
        ON tr.tag_database = tpr.tag_database 
        AND tr.tag_schema = tpr.tag_schema 
        AND tr.tag_name = tpr.tag_name
    ORDER BY tr.database_name, tr.schema_name, tr.object_name;
END;

注意事项 #

• 上述脚本需在支持Snowflake SQL Scripting的环境执行（如Snowflake Web UI、SnowSQL）
• 执行用户需具备对应schema的USAGE权限，以及对象的SELECT权限
• 若需跨多个数据库查询，可修改第一部分的查询逻辑，遍历所有数据库（需对应权限）

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