September 17, 2021

How Do I View Privileges Granted to a Role in Snowflake? Pt. 5

By Tony Foerster

In previous posts, we’ve talked about creating roles and granting privileges to those roles in the Snowflake Data Cloud. Here we will answer the question, “how do I know what privileges my role already has?” 

This can be answered by using the `SHOW GRANTS’ statement.

				
					SHOW GRANTS TO ROLE <role>;
				
			

We’ll use the data from the previous post (Pt. 4), so be sure to read that first if you want to see how these objects were built.

In that post, we created a database and granted ALL privileges to the ‘administrator’ role. We then showed all the privileges, from the perspective of the database itself.

To view all privileges granted to a role, we can use the SHOW GRANTS TO ROLE statement:

				
					SHOW GRANTS TO ROLE <role>;
				
			

Show grants to the administrator role with the statement:

				
					SHOW GRANTS TO ROLE administrator;
				
			

The result will be:

Row

1

2

3

4

5

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

privilege

CREATE SCHEMA

MODIFY

MONITOR

REFERENCE_USAGE

USAGE

granted_on

DATABASE

DATABASE

DATABASE

DATABASE

DATABASE

name

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

granted_to

ROLE

ROLE

ROLE

ROLE

ROLE

granted_name

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

ADMINISTRATOR

grant_option

false

false

false

false

false

granted_by

SYSADMIN

SYSADMIN

SYSADMIN

SYSADMIN

SYSADMIN

This result table has the same fields as if you had run: 

				
					SHOW GRANTS ON OBJECT ...
				
			

The fields in the result are:

  • 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 is a simple role that doesn’t inherit from any other role. If it did inherit another role’s privileges, that role would also be shown in the results. To see that, we can create another role and grant it to the administrator role:

				
					USE ROLE securityadmin;
CREATE ROLE engineer;
GRANT ROLE engineer TO ROLE administrator;
USE ROLE SYSADMIN;
CREATE SCHEMA rocketship.telemetry;
USE ROLE SECURITYADMIN;
GRANT MODIFY ON SCHEMA rocketship.telemetry TO ROLE engineer;
				
			

Now we can run our show role grants statement again:

				
					SHOW GRANTS TO ROLE administrator;
				
			

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

USAGE

granted_on

DATABASE

DATABASE

DATABASE

DATABASE

DATABASE

ROLE

name

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ROCKETSHIP

ENGINEER

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 the new row for the role ‘engineer.’  The name field is ENGINEER and the ‘granted_on’ field is set to ‘ROLE.’

In Closing

This post showed how to query the privileges granted to a role in Snowflake, and understand the resulting output. The next post will dig deeper into role grants, and show how to view and understand a Snowflake role hierarchy, with many nested role grants.

Next up in Series

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

Accelerate and automate your data projects with the phData Toolkit