23 October 2018        Add to Favorites   Report

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 |
+---------------------------------+---------+------------+--------------+---------------+


Copied