Leveraging Claude 3.7 Sonnet to Build a Comprehensive Snowflake User Role Access Query

※This is an English translation of this original Japanese article Hello, this is Sagara. As the title suggests, I asked Claude 3.7 Sonnet to create a query that shows which roles each Snowflake user can access, and I'd like to share the results. Query to Check Which Roles Each Snowflake User Can Access Here's the query that allows you to check which roles each Snowflake user can access: Note: This is quite a heavy query that takes around 40 seconds to execute on an XS warehouse in our test environment. select user_name, granted_role_name, grant_type, created_on from ( -- Directly granted roles select grantee_name as user_name, role as granted_role_name, 'DIRECT' as grant_type, created_on from snowflake.account_usage.grants_to_users where deleted_on is null union all -- Indirectly granted roles select u.grantee_name as user_name, rh.granted_role_name, 'INDIRECT (via ' || listagg(distinct u.role, ' > ') within group (order by u.role) || ')' as grant_type, min(u.created_on) as created_on from snowflake.account_usage.grants_to_users u join ( -- Recursive retrieval of role hierarchy with recursive role_path as ( select name as granted_role_name, grantee_name as parent_role_name, name || ' > ' || grantee_name as path, 1 as level from snowflake.account_usage.grants_to_roles where granted_on = 'ROLE' and privilege = 'USAGE' and deleted_on is null union all select rp.granted_role_name, g.grantee_name as parent_role_name, rp.path || ' > ' || g.grantee_name as path, rp.level + 1 as level from role_path rp join snowflake.account_usage.grants_to_roles g on rp.parent_role_name = g.name where g.granted_on = 'ROLE' and g.privilege = 'USAGE' and g.deleted_on is null and rp.level

Apr 2, 2025 - 00:58

Leveraging Claude 3.7 Sonnet to Build a Comprehensive Snowflake User Role Access Query

※This is an English translation of this original Japanese article

Hello, this is Sagara.

As the title suggests, I asked Claude 3.7 Sonnet to create a query that shows which roles each Snowflake user can access, and I'd like to share the results.

Query to Check Which Roles Each Snowflake User Can Access

Here's the query that allows you to check which roles each Snowflake user can access:

Note: This is quite a heavy query that takes around 40 seconds to execute on an XS warehouse in our test environment.

select 
    user_name,
    granted_role_name,
    grant_type,
    created_on
from (
    -- Directly granted roles
    select 
        grantee_name as user_name,
        role as granted_role_name,
        'DIRECT' as grant_type,
        created_on
    from 
        snowflake.account_usage.grants_to_users
    where 
        deleted_on is null

    union all

    -- Indirectly granted roles
    select 
        u.grantee_name as user_name,
        rh.granted_role_name,
        'INDIRECT (via ' || listagg(distinct u.role, ' > ') within group (order by u.role) || ')' as grant_type,
        min(u.created_on) as created_on
    from 
        snowflake.account_usage.grants_to_users u
    join (
        -- Recursive retrieval of role hierarchy
        with recursive role_path as (
            select 
                name as granted_role_name,
                grantee_name as parent_role_name,
                name || ' > ' || grantee_name as path,
                1 as level
            from 
                snowflake.account_usage.grants_to_roles
            where 
                granted_on = 'ROLE'
                and privilege = 'USAGE'
                and deleted_on is null

            union all

            select 
                rp.granted_role_name,
                g.grantee_name as parent_role_name,
                rp.path || ' > ' || g.grantee_name as path,
                rp.level + 1 as level
            from 
                role_path rp
            join 
                snowflake.account_usage.grants_to_roles g
                on rp.parent_role_name = g.name
            where 
                g.granted_on = 'ROLE'
                and g.privilege = 'USAGE'
                and g.deleted_on is null
                and rp.level < 10
        )
        select * from role_path
    ) rh on u.role = rh.parent_role_name
    where 
        u.deleted_on is null
    group by 
        u.grantee_name, rh.granted_role_name
)
order by 
    user_name, grant_type, granted_role_name;

The execution results display each user's available roles in the GRANTED_ROLE_NAME column. The GRANT_TYPE column indicates whether the role was directly granted (DIRECT) or available because it's a child role of another role (e.g., INDIRECT (via ACCOUNTADMIN)).

Note that for the CREATED_ON column, when the GRANT_TYPE is INDIRECT (via ACCOUNTADMIN), it shows the date when the ACCOUNTADMIN role was granted.

If you're using Functional Roles and Access Roles for role management, this query will display a list of Access Roles granted to users, allowing you to check what access permissions each user has.

Reference: The Process to Reach This Result

I used AI-Starter, our company's generative AI service, to query Claude 3.7 Sonnet.

Initially, I asked casually, but the responses were either simple queries to various views or queries that couldn't be used as-is.

After several exchanges, Claude began not only providing queries but also showing queries to check data structures.

When these exchanges didn't lead to a satisfactory result, I shared a query from this article and asked Claude to consolidate it into a single SELECT statement.

The SELECT statement I received could be executed but didn't provide the results I needed. So I compiled the data I wanted to retrieve and sample results based on our previous exchanges, and asked Claude to JOIN them.

Thanks to our previous interactions, Claude immediately created a recursive query that could check roles that were available but not directly granted, resulting in the query shared in this article.