How Do I Understand a Role Hierarchy in Snowflake? Pt. 6

Thanks for visiting our series on access controls in the Snowflake Data Cloud. If you missed our previous blog on viewing privileges granted to a role in Snowflake, be sure to check it out. In this post, we’ll show you how to understand an existing role hierarchy in Snowflake.

Snowflake has a powerful access control system that allows for role inheritance. Roles can be granted to other roles, inheriting their privileges.

For demonstration purposes, we’ll use the default Snowflake role hierarchy, which looks like:

A graphic with 4 circles used to demonstrate role hierarchy in Snowflake

In this role hierarchy, ACCOUNTADMIN is the most powerful role. It inherits the SYSADMIN and SECURITYADMIN roles. SECURITYADMIN inherits the USERADMIN role.

How would we go about deconstructing this role hierarchy if we didn’t know the role relationships? It’s easy enough to find this role hierarchy in the Snowflake documentation, so this post can be used as an example of how you would go about deconstructing and understanding any custom role hierarchy.

Snowflake provides two statements to understand role hierarchies.

				
					SHOW GRANTS TO ROLE <role>;
				
			

The TO ROLE statement will show both roles and privileges granted to a role.

The statement:

				
					SHOW GRANTS TO ROLE ACCOUNTADMIN;
				
			

Will show:

Row

1

2

created_on

2021-07-23 10:09

2021-07-23 10:11

privilege

USAGE

USAGE

granted_on

ROLE

ROLE

name

SECURITYADMIN

SYSADMIN

granted_to

ROLE

ROLE

granted_name

ACCOUNTADMIN

ACCOUNTADMIN

grant_option

True

True

Now we have part of the role hierarchy, we know ACCOUNT ADMIN (from the ‘grantee_name’ column) has been granted the USAGE privilege (from the ‘privilege’ column) on the roles SYSADMIN and SECURITYADMIN (from the ‘name’ column):

3 circles to demonstrate role hierarchy in Snowflake

To finish filling out the role hierarchy, we run the SHOW GRANTS statement on each of the roles ACCOUNTADMIN inherits.

				
					SHOW GRANTS TO ROLE SYSADMIN;
				
			

Doesn’t return anything.

				
					SHOW GRANTS TO ROLE SECURITYADMIN;
				
			

Row

1

created_on

2021-07-23 10:09

privilege

USAGE

granted_on

ROLE

name

USERADMIN

granted_to

ROLE

granted_name

SECURITYADMIN

grant_option

True

The final piece of the role hierarchy is that USERADMIN is granted to SYSADMIN.

This completes the Snowflake standard role hierarchy, and we now have enough information to show the complete hierarchy:

4 circles showing role hierarchy in Snowflake

Meet Traverse, a Free Snowflake Role Hierarchy Tool

Traverse is a free application from phData that helps organizations unlock a drillable view that clearly shows users, roles, databases, schemas, warehouses, and their relationships.

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