CODEWITHSUNDEEP
snowflake-cloud-data-platform
Xi12
Xi12

Reputation: 1223

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

Upvotes: 5

Views: 17579

Answers (3)

demircioglu
demircioglu

Reputation: 3465

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 NAME
                  from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
                 where GRANTEE_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

Upvotes: 3

Daniel Odievich
Daniel Odievich

Reputation: 161

https://docs.snowflake.com/en/sql-reference/sql/show-grants.html will do what you want with:

SHOW GRANTS OF ROLE svn_dev_admin;

created_on  role    granted_to  grantee_name    granted_by
2018-11-12 15:18:07.580 -0800   SYSADMIN    ROLE    ACCOUNTADMIN    
2019-10-02 09:23:26.688 -0700   SYSADMIN    USER    XYZ ACCOUNTADMIN
2020-03-02 12:56:01.386 -0800   SYSADMIN    USER    ZYX ACCOUNTADMIN

Upvotes: 11

NickW
NickW

Reputation: 9788

You can just query the GRANTS_TO_USERS view

Upvotes: 1

Related Questions