'Snowflake :: Role to Privilege mapping information
I need information within Snowflake which captures "Role to Privilege mapping". Example :: If I am an user "USER_01" & I have the role as "ANALYST", then this "ANALYST" role has access to which all the objects (database/schema/table/etc..) in Snowflake account I would need that information.
Any pointers like queries/metadata tables/etc.. would help as there has to be some metadata tables which would keep this role to privilege mapping.
For e.g., queries like :
show grants to user USER_01
--> This gives what role has been assigned to this user. But this do not convey any information regarding what all privileges has been given to that role. Like if this role has got below access:
grant usage on database DB_01 to role analyst;
grant all privileges on schema schema_01 to role analyst;
grant all privileges on all tables in schema schema_01 to role analyst;
Solution 1:[1]
SHOW GRANTS TO ROLE <role_name>
Key point: Access Control Framework
Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users.
Access priviliges are not assigned directly to users.
Solution 2:[2]
If you want to list all the privileges given to a user, you may use the stored procedure described in this KB article:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Lukasz Szozda |
| Solution 2 | Sheng |