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:
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):
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:
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.
