October 4, 2021

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

By Tony Foerster

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 ACCOUNTADMIN (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;
				
			

Which doesn’t return anything. There are grants for SECURITYADMIN.

				
					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 SECURITYADMIN.

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.

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