0

I want to select a user only with a given role. I gone through the document but didnt get how to filter rows for a particular role.

DATABASE: SNOWFLAKE

Ashutosh gupta
  • 447
  • 4
  • 16
  • Does this answer your question? [Query to get list of all roles and their associated users in snowflake](https://stackoverflow.com/questions/60923894/query-to-get-list-of-all-roles-and-their-associated-users-in-snowflake) – Thomas G Jul 23 '20 at 19:11
  • No, my question is entirely different from that. – Ashutosh gupta Jul 23 '20 at 19:16
  • I am a little confused because grants are not applied on users. Grants are applied to roles. So typically, you see what roles your user has (show grants to user) and then see the grants on that role (show grants to role). Is this not working for you and may we have an example of what you are looking for? – Suzy Lockwood Jul 23 '20 at 20:23

1 Answers1

3

You should use the "SNOWFLAKE" database to get what you're looking for. If you have access to the database there is a view called "GRANTS_TO_USERS" which, assuming I understand your question, should have what you need:

For example

select * 
from "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_USERS" 
where role = 'SYSADMIN'
and grantee_name = 'SIMON'
Simon D
  • 5,730
  • 2
  • 17
  • 31