Quickly Visualize Snowflake’s Roles, Grants, and Privileges
How I Used Dagre-d3 to Go Beyond Table Views
by Venkatesh Sekar
A few months ago, I published a blog post as a guide to capturing Snowflake users, roles, and grants into a table. Things have changed, however, and Snowflake now provides views that deliver better functionality. In this post, I’m going to walk you through a new and improved approach and demonstrate a visualization prototype code sample for understanding role hierarchy and relationships between roles and grants.
Snowflake’s Control Framework
Snowflake provides granular control over access to objects: who can access what objects, what operations can be performed on those objects, and who can create or alter access control policies.
Reference: Snowflake Access Control Framework
All objects in Snowflake are secured; in order to access these objects the user needs to have:
- The required Role
- And appropriate action privilege (e.g. Usage, Modify, Monitor Usage, etc.)
By default Snowflake offers the following default roles:
- AccountAdmin
- SecurityAdmin
- SysAdmin
- Public
These are a good starting point, but won’t be sufficient for most implementations, so we end up defining custom roles. Snowflake’s recommendation is to create a hierarchy of custom roles with the top-most custom role assigned to the system role SYSADMIN. That way the system administrators will be able to manage all warehouses and databases while maintaining management of user and roles restricted to users granted the SECURITYADMIN or ACCOUNTADMIN roles.
Role Hierarchy
A role hierarchy is created via the Grant statement, for example:
Information of the roles and privilege can be obtained via:
As mentioned, things have changed in the Snowflake world since I last talked about capturing users, roles, and grants into a table in a previous post; Snowflake now provides views which deliver these functions:
Views presenting relations between roles, grants, and users:
The data is now readily queryable and results are available as table results:
Visualization of the Role Hierarchy
There is a natural hierarchy to the data; a quick way of understanding the data is to present it in the form of a Graph Network.
I am not a UX/UI expert, but I would prefer the graph network to clearly lay out the roles and objects as nodes and the privileges for those objects as lineage.
After working my way through Sanskey diagrams as well as Chord diagrams, I finally landed on using Dagre-d3 which is a D3-based renderer for Dagre. Dagre is a JavaScript library that makes it easy to layout directed graphs on the client-side.
My Sample Scenario
In the scenario below, I have a single Snowflake account with multiple environment configurations. Each environment (DEV, SBX, PRD) is prefixed in the name of the objects: role DEV_DBA, database SBX_RAW.
I defined the following custom role hierarchy:
Other objects like database, warehouse, etc. also get defined. Roles are assigned to appropriate privileges.
Data extraction
As mentioned, the roles and grants are available via the GRANTS_TO_ROLE view. While there are many ways of extracting and presenting the data, I adopted a simple approach. For now, I’ve used the query below to get the roles as JSON documents and store them locally in a file: data/grants_to_roles.json.
I quickly did a manual edit to set the rows into a JSON array:
Graphing with Dagre-d3
This is a prototype so I’ve used the default settings and configuration of Dagre-d3 to layout the graph. The code is available at Github: SnowflakeGuiHacks.
The prototype achieves the following:
- Lays out the roles and their hierarchy in a tree.
- Maps privileges as edges so you can see how the role is connected to an object (warehouse, databases, schema).
- Filters out the ownership privilege because it caused too much noise in the graph.
- Groups the objects into a bounding box.
Going Beyond the Prototype
This demo is a simple prototype. Should you want to go beyond zoom-in and zoom-out responsiveness in the graph, you could enhance the code to adopt the following functions:
- When a specific object is chosen, highlight all the neighboring nodes
- Filter
- Tooltips
- Append with GRANTS_TO_USERS view dataset
And, of course, format the graph in an organized way. Formatting the graph is a complex science, as explained in this Blog: Lessons Learned from Creating a Custom Graph Visualization in React. It’s up to you as the designer and implementer to take it the direction you want based on your audience and consumption requirements.
Final Thoughts
I hope this new approach has helped you and provided further insight into understanding role hierarchy and relationships between roles and grants with Snowflake. It would be great to hear your thoughts and what your next set of moves is with your data and analytics program and Snowflake!
Need Help with Your Cloud Initiatives?
If you are considering moving data and analytics products and applications to the cloud or if you would like help and guidance and a few best practices in delivering higher value outcomes in your existing cloud program, then please contact us.
Hashmap offers a range of enablement workshops and assessment services, cloud modernization and migration services, and consulting service packages as part of our Cloud (and Snowflake) service offerings.
How does Snowflake compare to other data platforms? Our technical experts have implemented over 250 cloud/data projects in the last 3 years and conducted unbiased, detailed analyses across 34 business and technical dimensions, ranking each cloud data platform.
Other Tools and Content You Might Like
Feel free to share on other channels and be sure and keep up with all new content from Hashmap here.
Venkat Sekar is Regional Director for Hashmap Canada and is an architect and consultant providing Data, Cloud, IoT, and AI/ML solutions and expertise across industries with a group of innovative technologists and domain experts accelerating high-value business outcomes for our customers.
