SHOW GRANTS on a Table / Role / User in Snowflake
Snowflake uses ROLES to provision access rules. The SHOW GRANTS Command lists all access control privileges that have been granted to roles, users, and shares.
Table level grants:
SHOW GRANTS ON TABLE schema.table;
Database level grants:
show grants on database sales;
+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by |
|---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------|
| Thu, 07 Jul 2016 05:22:29 -0700 | OWNERSHIP | DATABASE | REALESTATE | ROLE | ACCOUNTADMIN | true | ACCOUNTADMIN |
| Thu, 07 Jul 2016 12:14:12 -0700 | USAGE | DATABASE | REALESTATE | ROLE | PUBLIC | false | ACCOUNTADMIN |
+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
Role level grants:
show grants to role analyst;
+---------------------------------+------------------+------------+------------+------------+--------------+------------+
| created_on | privilege | granted_on | name | granted_to | grant_option | granted_by |
|---------------------------------+------------------+------------+------------+------------+--------------+------------+
| Wed, 17 Dec 2014 18:19:37 -0800 | CREATE WAREHOUSE | ACCOUNT | DEMOENV | ANALYST | false | SYSADMIN |
+---------------------------------+------------------+------------+------------+------------+--------------+------------+
User level grants:
show grants to user demo;
To see all the list of users belonging to a role:
show grants of role analyst;
+---------------------------------+---------+------------+--------------+---------------+
| created_on | role | granted_to | grantee_name | granted_by |
|---------------------------------+---------+------------+--------------+---------------|
| Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | ANALYST_US | SECURITYADMIN |
| Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | DBA | SECURITYADMIN |
| Fri, 08 Jul 2016 10:21:30 -0700 | ANALYST | USER | JOESM | SECURITYADMIN |
+---------------------------------+---------+------------+--------------+---------------+
David-Spring
posted on 23 Oct 18Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment