Snowflake Show Role Members If you have access to snowflake account usage this query solves your problem with granted as select from snowflake account usage grants to users where role in ROLE1 ROLE2 ROLE3 qualify row number over partition by role grantee name order by created on desc 1 select distinct grantee name as name role from granted where deleted on is null order by name
Usage notes Any user can execute the SHOW USERS command Snowflake filters the output based upon the privileges granted to the active role that the user uses to run the command The user that runs the command can always see the username in the results which is recorded in the name column To see the full output the active role must have either However this does not necessarily mean the role allows users to use the database roles to perform SQL actions To use a database role it must first be granted to an account role that users can activate in a user session or to an account role lower in a hierarchy This is a part of Discretionary Access Control and Role Based Access Control
Snowflake Show Role Members
Snowflake Show Role Members
https://i.ytimg.com/vi/jC6dJxM0ZYc/maxresdefault.jpg
Violet Snowflake On Gold Free Stock Photo Public Domain Pictures
https://www.publicdomainpictures.net/pictures/150000/velka/violet-snowflake-on-gold.jpg
Snowflake YouTube
https://i.ytimg.com/vi/pdnIOQVTXXs/maxresdefault.jpg
Snowflake Users and Roles via SQL You can also retrieve users and roles directly from SQL one of the huge advantages of Snowflake it s SQL via the SHOW command For example to get the list of users you would issue the following commands USE ROLE SECURITYADMIN SHOW USERS SELECT FROM TABLE RESULT SCAN LAST QUERY ID Information of the roles and privilege can be obtained via Show Grants Show Roles As mentioned things have changed in the Snowflake world since I last talked about capturing users roles and grants into a table in a previous post Snowflake now provides views which deliver these functions Views presenting relations between roles grants
Use role accountadmin with cte as select from snowflake account usage grants to roles where grantee name ilike role name replace role name with the primary role name of the role hierarchy and granted on ROLE and privilege USAGE and deleted on is null UNION ALL select g from snowflake account usage grants to roles g join cte on g grantee name cte name where g granted Show all Inherited Roles with a Recursive CTE You would need a recursive query to run through all inherited roles level by level We ll not try to show them hierarchically like a tree
More picture related to Snowflake Show Role Members
Ms K J Le Jeudi 15 D cembre 2016
https://openclipart.org/image/2400px/svg_to_png/173658/Snowflake-1-Remix-by-Merlin2525.png
Snowflake PoliticalCompass
https://i.redd.it/qzwm98sl0yj71.png
Alphabet Snowflake Green Free Stock Photo Public Domain Pictures
https://www.publicdomainpictures.net/pictures/380000/velka/image-1606469779VLz.jpg
Is it possible to list all of the roles that a user has access to in Snowflake DB I seem to be able to show the users with access to a role but I can t figure out how to list the roles a user is granted Of course I can crawl the roles and work at it from that angle but it would be very beneficial to do the opposite Members Online HAKOC534 ADMIN MOD How to show roles for a user I need to see what roles a specific user has I can look at all users and see their default roles but not other roles select from SNOWFLAKE ACCOUNT USAGE GRANTS TO USERS where grantee name username
[desc-10] [desc-11]
Green Snowflake II Free Stock Photo Public Domain Pictures
https://www.publicdomainpictures.net/pictures/140000/velka/green-snowflake-ii.jpg
ArtStation Snowflake
https://cdnb.artstation.com/p/assets/images/images/008/474/493/large/maggie-parvanova-snowflake5.jpg?1513022757
Snowflake Show Role Members - Snowflake Users and Roles via SQL You can also retrieve users and roles directly from SQL one of the huge advantages of Snowflake it s SQL via the SHOW command For example to get the list of users you would issue the following commands USE ROLE SECURITYADMIN SHOW USERS SELECT FROM TABLE RESULT SCAN LAST QUERY ID