snowflake-cloud-data-platform

How to see list of users in a role snowflake


We can see what roles are assigned to a user but how do I see the list of users in a role snowflake?

For example I have a role svn_dev_admin , I need to see all users under this role Thanks, Xi


Solution

  • The following query should give you users list for the role specified and the role(s) under that.

    -- since role_name used in the query twice, set it to a parameter
    set role_name = 'svn_dev_admin';  
    
    select GRANTEE_NAME
     from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
    where ROLE = $role_name
      and DELETED_ON is null
    union 
    select GRANTEE_NAME
      from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
     where ROLE IN (select GRANTEE_NAME
                      from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
                     where privilege = 'USAGE'
                     and GRANTED_ON = 'ROLE'
                     and NAME = $role_name)
       and DELETED_ON is null;
    

    If you want to see the users got the access with a lower level role to the given role you can add the ROLE column to the query like the following, but it might create duplicate user names

    select ROLE, GRANTEE_NAME