How Do I View Privileges Granted On an Object in Snowflake? Pt. 4

Next up in our series on access controls in the Snowflake Data Cloud, we’re going to explore how to view privileges granted on an object in Snowflake. If you’re new to this series or missed our last post on how to grant privileges to a role in Snowflake, be sure to check it out.

Getting Started

To view privileges granted on an object in Snowflake, use the SHOW GRANTS statement:

				
					SHOW GRANTS ON <object type> <object name>;
				
			

The SHOW GRANTS statement can be used on any Snowflake securable-object (<object type>) like databases, warehouses, and schemas.

The following examples will use an example object (the ‘rocketship’ database):

				
					USE ROLE sysadmin;
CREATE DATABASE rocketship;
				
			

After running the SHOW GRANTS statement on the database, we can examine the output, and see the privileges granted on the object:

				
					SHOW GRANTS ON DATABASE rocketship;
				
			

No explicit grants have been run on the database yet, but there’s still a result showing the OWNERSHIP privilege on the role that created the database (‘grantee_name’). In this case it is the SYSADMIN that was implicitly granted the privilege on creation:

Row

1

created_on

2021-02-17 14:04:20.427 -0800

privilege

OWNERSHIP

granted_on

DATABASE

name

ROCKETSHIP

granted_to

ROLE

grantee_name

SYSADMIN

grant_option

true

granted_by

SYSADMIN

The columns in the result are as follows:

  • created_on: when this privilege was granted.
  • privilege: the name of the privilege that was granted.
  • granted_on: the type of object (database) the privilege was granted on.
  • granted_to: the type of object the privilege was granted to. Using this statement, this will always be ‘ROLE’. It can be ‘USER’ when we are granting roles to users.
  • grantee_name: the name of the role we are granting the privilege to.
  • grant_option: whether the role can also re-grant this privilege to other roles.
  • granted_by: the role that ran the grant statement (in this case just a ‘CREATE DATABASE’ statement).

This diagram shows the ownership privilege visually, SYSADMIN has OWNERSHIP on the database rocketship:

A basic model showing how a role administrator can grant a role in Snowflake.

Image created by Traverse

To show this also works for other types of grants, we can create a role administrator and grant it all privileges on the database:

				
					USE ROLE securityadmin;
CREATE ROLE administrator;
GRANT ALL ON DATABASE rocketship TO ROLE administrator;
				
			

Now when the SHOW GRANTS statement again:

				
					SHOW GRANTS ON DATABASE rocketship;
				
			

The new privileges can be seen:

Row

1

2

3

4

5

6

created_on

2021-07-23 10:09

2021-07-23 10:11

2021-07-23 10:23

2021-07-23 10:25

2021-07-23 10:35

2021-07-23 10:45

privilege

CREATE SCHEMA

MODIFY

MONITOR

REFERENCE_USAGE

USAGE

OWNERSHIP

granted_on

DATABASE

DATABASE

DATABASE

DATABASE

DATABASE

DATABASE

name

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

granted_to

ROLE

ROLE

ROLE

ROLE

ROLE

ROLE

granted_name

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

SYSADMIN

grant_option

false

false

false

false

false

true

granted_by

SYSADMIN

SYSADMIN

SYSADMIN

SYSADMIN

SYSADMIN

SYSADMIN

Notice that, as we mentioned above, ALL isn’t a ‘real’ privilege, it was expanded out by Snowflake to all of the individual privileges available to the DATABASE type object.

a Triangle shaped chart created in Traverse used to show role privileges in Snowflake.

Image created by Traverse

In this post we showed how to find out which privileges have been granted to Snowflake securable objects. Check back for the next blog in the series where we describe how to view privileges granted to roles.

Next up in Series

Share on linkedin
Share on twitter
Share on facebook
Share on email

Table of Contents

More to explore

Dependable data products, delivered faster.

Snowflake Onboarding Accelerator

Infrastructure-as-code Accelerator

Snowflake Account Visualization and Auditing

Operational Monitoring and Observability Accelerator

SaaS SQL Translator

Introducing
Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.