Snowflake Syntax: show grants
The SHOW GRANTS statement lists the PRIVILEGES or ROLES granted to a given USER or ROLE which is part of the RBAC (Role Based Access Control) toolset used to secure access to data. Be aware, Snowflake Customers often create an unnecessarily complex hierarchy of ROLES which makes navigating the Role hierarchy really challenging. Ideally you should follow the predefined Data Security Framework designed by John Ryan at Snowflake which is described as part of the Snowflake QuickStart, the Snowflake Architecture Instructor Led Training Sessions and summarised as a series of articles (see below).
show grants
show grants to user <USER>
The SHOW GRANTS command lists all the roles available to the current user. It is exactly the same as executing a SHOW GRANTS TO USER xxxx statement.
-- -- Lists all GRANTS -- show grants;
show grants on table <Table>
Lists which ROLES can access the given object. Where this command includes TABLE we could include any other object type. EG.
show grants on DATABASE <Database Name>
show grants on VIEW <View Name>
In the example below we create a new ROLE (guest_role) and grant select on the table. We then see two grants.
OWNERSHIP - granted to TRAINEE_ROLE (the role used to create the table)
SELECT - granted to GUEST_ROLE (as per the grant below)
-- Create a table MY_TABLE and GUEST role -- Grant select on the table to GUEST role use role trainee_role; create table my_table (x number); create role guest_role; grant select on all tables in schema public to role guest_role; -- Show grants on table show grants on table my_table;
show grants to role <Role>
Lists the ROLES and PRIVILEGES granted to a given ROLE. Note: This shows the privileges (or roles) granted TO a given role. In this example below we grant SELECT on tables to GUEST_ROLE and then confirm this is the case.
use role trainee_role; create table if not exists my_table (x number); create role if not exists guest_role; grant select on all tables in schema public to role guest_role; show grants to role guest_role;
show grants of role <Role>
Lists the USERS or ROLES that a given <Role> has been granted to. Can be used to identify the list of USERS who have been granted a given role. Be aware, if a ROLE is granted to another ROLE you need to follow the chain to see which users have in turn been granted access.
In the example below we create a role GUEST_ROLE which is in turn granted to PUBLIC and TRAINEE roles.
use role trainee_role; create role if not exists guest_role; grant role guest_role to role public; grant role guest_role to user trainee; show grants of role guest_role;
show grants on account
Lists the ACCOUNT level privileges and the roles they have been granted to. Typically this is the System Defined Roles although these ACCOUNT level grants (eg. CREATE DATABASE, CREATE WAREHOUSE), can also be granted to user defined roles.
show grants on account;
show grants on account (Excluding System Defined Roles)
This query lists the ACCOUNT level grants excluding the System Defined Roles. This enables us to focus on the roles granted these powerful ACCOUNT level privileges which should ideally be restricted to a few System Administrators.
show grants on account;
select "privilege" as privilege
, "granted_to" as granted_to
, "grantee_name" as grantee_name
, "granted_by" as granted_by
, "created_on" as created_on
from table(result_scan(last_query_id()))
where "grantee_name" not in ('ACCOUNTADMIN', 'APPADMIN', 'ORGADMIN', 'SECURITYADMIN', 'USERADMIN','SYSADMIN');