如何在Snowflake中查询特定角色拥有的表和视图及所需权限？

查询方法 #

1. 账号级全量查询（覆盖所有数据库/模式） #

通过ACCOUNT_USAGE视图集合可以一次性获取整个账号内目标角色拥有的所有表和视图，适合跨库查询场景：

-- 单独查询表
SELECT 
    TABLE_CATALOG AS DATABASE_NAME,
    TABLE_SCHEMA AS SCHEMA_NAME,
    TABLE_NAME,
    OWNER
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE 
    OWNER = 'TARGET_ROLE_NAME' -- 替换为目标角色名
    AND TABLE_TYPE = 'BASE TABLE';

-- 单独查询视图
SELECT 
    TABLE_CATALOG AS DATABASE_NAME,
    TABLE_SCHEMA AS SCHEMA_NAME,
    TABLE_NAME,
    OWNER
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.VIEWS
WHERE 
    OWNER = 'TARGET_ROLE_NAME'; -- 替换为目标角色名

也可以合并表和视图的查询结果：

SELECT 
    OBJECT_CATALOG AS DATABASE_NAME,
    OBJECT_SCHEMA AS SCHEMA_NAME,
    OBJECT_NAME,
    OBJECT_TYPE,
    OWNER
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.OBJECT_PRIVILEGES
WHERE 
    OWNER = 'TARGET_ROLE_NAME'
    AND OBJECT_TYPE IN ('TABLE', 'VIEW')
    AND PRIVILEGE = 'OWNERSHIP'
GROUP BY 
    OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, OWNER;

2. 单数据库范围内查询 #

如果只需查询某个特定数据库下的对象，使用该库的INFORMATION_SCHEMA视图（数据实时，无延迟）：

-- 先切换到目标数据库
USE DATABASE YOUR_TARGET_DB;

-- 查询该库内目标角色拥有的表和视图
SELECT 
    TABLE_SCHEMA AS SCHEMA_NAME,
    TABLE_NAME,
    TABLE_TYPE,
    OWNER
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    OWNER = 'TARGET_ROLE_NAME'
    AND TABLE_TYPE IN ('BASE TABLE', 'VIEW');

所需权限 #

1. 账号级查询权限 #

• 直接使用ACCOUNTADMIN角色：默认拥有所有ACCOUNT_USAGE视图的访问权限。
• 非ACCOUNTADMIN角色需获取以下授权：

  -- 授予SNOWFLAKE数据库访问权
  GRANT USAGE ON DATABASE SNOWFLAKE TO ROLE YOUR_CURRENT_ROLE;
  -- 授予ACCOUNT_USAGE模式访问权
  GRANT USAGE ON SCHEMA SNOWFLAKE.ACCOUNT_USAGE TO ROLE YOUR_CURRENT_ROLE;
  -- 授予对应视图的查询权
  GRANT SELECT ON VIEW SNOWFLAKE.ACCOUNT_USAGE.TABLES TO ROLE YOUR_CURRENT_ROLE;
  GRANT SELECT ON VIEW SNOWFLAKE.ACCOUNT_USAGE.VIEWS TO ROLE YOUR_CURRENT_ROLE;
  

2. 单数据库查询权限 #

需对目标数据库及相关对象拥有以下权限：

-- 授予目标数据库访问权
GRANT USAGE ON DATABASE YOUR_TARGET_DB TO ROLE YOUR_CURRENT_ROLE;
-- 授予数据库内所有模式的访问权（按需调整为特定模式）
GRANT USAGE ON ALL SCHEMAS IN DATABASE YOUR_TARGET_DB TO ROLE YOUR_CURRENT_ROLE;
-- 授予INFORMATION_SCHEMA.TABLES视图的查询权
GRANT SELECT ON VIEW YOUR_TARGET_DB.INFORMATION_SCHEMA.TABLES TO ROLE YOUR_CURRENT_ROLE;

注意：ACCOUNT_USAGE视图的数据存在约2小时延迟，若需实时数据，优先使用INFORMATION_SCHEMA查询方式。

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