[小ネタ]Snowflakeの各ユーザーがどのロールを利用できるか確認できるクエリをClaude 3.7 Sonnetに作成してもらいました

select 
    user_name,
    granted_role_name,
    grant_type,
    created_on
from (
    -- 直接付与されたロール
    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

    -- 間接的に付与されたロール
    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 (
        -- ロール階層の再帰的な取得
        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;