Snowflake元数据表查询多角色授权及角色层级问题

I get it—dealing with Snowflake's role-based access control can feel fragmented when you're trying to piece together full user permissions and role hierarchies without jumping between multiple commands or views. Let's break down how to solve both your needs efficiently.

1. Query a User's Full Roles (Direct + Indirect) and Associated Permissions #

To get all roles a user has access to (including those inherited via role grants) along with their permissions, we can combine recursive CTEs with Snowflake's ACCOUNT_USAGE views. Here's a step-by-step approach:

Step 1: Get all roles assigned to the user (direct + indirect) #

First, recursively traverse the role grant chain starting from the user's direct roles:

WITH RECURSIVE user_role_hierarchy AS (
    -- Base case: Direct roles granted to the user
    SELECT 
        grantee_name AS user_name,
        granted_role_name AS role_name,
        1 AS hierarchy_level
    FROM snowflake.account_usage.grants_to_users
    WHERE grantee_name = 'YOUR_USER_NAME' -- Replace with target username
        AND grant_type = 'ROLE'
    
    UNION ALL
    
    -- Recursive case: Roles granted to the roles we've already found
    SELECT 
        urh.user_name,
        gtr.granted_role_name,
        urh.hierarchy_level + 1
    FROM user_role_hierarchy urh
    JOIN snowflake.account_usage.grants_to_roles gtr
        ON urh.role_name = gtr.grantee_name
    WHERE gtr.grant_type = 'ROLE'
)
SELECT DISTINCT user_name, role_name, hierarchy_level
FROM user_role_hierarchy
ORDER BY hierarchy_level;

Step 2: Add permissions for each role #

Join the above CTE with grants_to_roles to get all permissions associated with each role the user can access:

WITH RECURSIVE user_role_hierarchy AS (
    SELECT 
        grantee_name AS user_name,
        granted_role_name AS role_name,
        1 AS hierarchy_level
    FROM snowflake.account_usage.grants_to_users
    WHERE grantee_name = 'YOUR_USER_NAME'
        AND grant_type = 'ROLE'
    
    UNION ALL
    
    SELECT 
        urh.user_name,
        gtr.granted_role_name,
        urh.hierarchy_level + 1
    FROM user_role_hierarchy urh
    JOIN snowflake.account_usage.grants_to_roles gtr
        ON urh.role_name = gtr.grantee_name
    WHERE gtr.grant_type = 'ROLE'
)
SELECT 
    urh.user_name,
    urh.role_name,
    urh.hierarchy_level,
    gtr.privilege_type,
    gtr.object_type,
    gtr.object_name,
    gtr.grant_option
FROM user_role_hierarchy urh
JOIN snowflake.account_usage.grants_to_roles gtr
    ON urh.role_name = gtr.grantee_name
WHERE gtr.grant_type = 'PRIVILEGE' -- Filter to actual permissions (not role grants)
ORDER BY urh.hierarchy_level, gtr.object_type, gtr.privilege_type;

This gives you a complete view of every permission the user has, broken down by the role that provides it (including inherited roles).

2. Build Hierarchy for Specific Roles (XXX, YYY, ZZZ) #

If you need to map the grant hierarchy for a set of target roles, here are two ways to visualize their relationships:

Option A: Show roles granted TO your target roles (upward hierarchy) #

This shows which higher-level roles your target roles inherit from:

WITH RECURSIVE role_grant_upward AS (
    -- Base case: Target roles
    SELECT 
        role_name AS child_role,
        CAST(NULL AS VARCHAR) AS parent_role,
        0 AS hierarchy_level
    FROM (VALUES ('XXX'), ('YYY'), ('ZZZ')) AS target_roles(role_name)
    
    UNION ALL
    
    -- Recursive case: Roles granted to the child role
    SELECT 
        rgu.child_role,
        gtr.grantee_name AS parent_role,
        rgu.hierarchy_level + 1
    FROM role_grant_upward rgu
    JOIN snowflake.account_usage.grants_to_roles gtr
        ON rgu.child_role = gtr.granted_role_name
    WHERE gtr.grant_type = 'ROLE'
)
SELECT DISTINCT child_role, parent_role, hierarchy_level
FROM role_grant_upward
WHERE parent_role IS NOT NULL -- Exclude base target roles without parents
ORDER BY child_role, hierarchy_level;

Option B: Show roles granted BY your target roles (downward hierarchy) #

This shows which lower-level roles inherit permissions from your target roles:

WITH RECURSIVE role_grant_downward AS (
    -- Base case: Target roles
    SELECT 
        role_name AS parent_role,
        CAST(NULL AS VARCHAR) AS child_role,
        0 AS hierarchy_level
    FROM (VALUES ('XXX'), ('YYY'), ('ZZZ')) AS target_roles(role_name)
    
    UNION ALL
    
    -- Recursive case: Roles granted by the parent role
    SELECT 
        rgd.parent_role,
        gtr.granted_role_name AS child_role,
        rgd.hierarchy_level + 1
    FROM role_grant_downward rgd
    JOIN snowflake.account_usage.grants_to_roles gtr
        ON rgd.parent_role = gtr.grantee_name
    WHERE gtr.grant_type = 'ROLE'
)
SELECT DISTINCT parent_role, child_role, hierarchy_level
FROM role_grant_downward
WHERE child_role IS NOT NULL -- Exclude base target roles without children
ORDER BY parent_role, hierarchy_level;

Key Notes #

• ACCOUNT_USAGE views have a latency of ~1 hour. If you need real-time data, replace them with corresponding INFORMATION_SCHEMA views or use SHOW GRANTS commands in a script.
• For real-time role hierarchies, you could use a stored procedure to loop through roles and collect grants, but the recursive CTE approach with ACCOUNT_USAGE is more efficient for bulk queries.

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