Managing Snowflake Roles, Grants, and Privileges with the Snowflake Grant Report Tool
Overview
Snowflake Role-based Access Control (RBAC) offers customers powerful tools to configure authorization to secure their systems, including the ability to build a hierarchy of roles and assign a mix of granular permissions for combined effective permissions.
Snowflake Grant Report extracts Roles and Grants data from Snowflake and provides tabular and visual reports on the Role hierarchy and Grant assignments, reducing the cognitive effort necessary for a full understanding of customer’s security posture.
RBAC Design
Snowflake’s RBAC features define who can access and perform operations on specific objects (tables, views, schemas, etc.) within an account.
Snowflake Professional Services QuickStart, Best Practices, and Security Consultation offerings help customers plan their Snowflake deployment, including help with appropriate RBAC architecture and Role structure.
On other hand, many customers experience RBAC configuration in a more organic way. A customer can choose a very flat Role or an exceedingly deep hierarchy. Some customers even take on a heavy administrative burden of managing thousands of individual Roles for each user, including dealing with tens of thousands of unique grant permutations.
As a result of organic or unplanned growth, customers can have unintended holes in their Snowflake security, lock users out of essential resources, and have an unnecessarily high level of administrative burden, potentially leading to dissatisfaction and poor business outcomes.
Functional and Access Roles
One of the core recommendations customers hear from Snowflake Professional Services consultations focused on RBAC design is use of Functional and Access Roles. The fundamental idea is that Access Roles can only contain Privileges, while Functional Roles can only contain other Roles.
In this example, there are 3 Functional Roles (Data Scientist, Data Analyst, and ELT Role) that represent personas doing specific job function. Access to databases is granted to those personas by including Access Roles (Working CRUD, Working Read Only, Main CRUD, Main Read Only) in the Functional Roles, and assigning specific permissions to those Access Roles.
When Snowflake Roles include other Roles, they can form POLYarchy instead of straightforward hierarchy, which means that there can be multiple “roots” that are not related to each other.
For more about Functional and Access roles, take a look at “A Functional Approach For Snowflake’s Role-Based Access Controls” article.
Reviewing Grants and Role Privileges
With any deployment of meaningful size, customers begin asking questions like this:
- How can I review and manage all privileges assigned to my database objects and compute resources?
- How can I document access levels for regular audits?
- Who has access to what and why?
- How can I understand which Roles are included in other Roles?
Snowflake provides data dictionary object metadata, as well as historical usage data via a shared database named SNOWFLAKE. Specifically, the GRANTS_TO_ROLES and GRANTS_TO_USERS views provide all the data necessary to answer the questions mentioned above.
However, depending on the number of Roles and Grants in play, the dataset in GRANTS_TO_ROLES can be quite large and can require significant cognitive effort to interpret. Additionally, the Role structure visualization is not yet available in the Snowflake console.
Snowflake Grant Report
Hoping to address the aforementioned challenges of discovery and documentation of current privileges and understanding of Role polyarchy, I built Snowflake Grant Report, a tool that offers a way of visualizing role structure and helps with rapid diagnosis of as-is permissions.
Snowflake Grant Report retrieves list of Roles, Grants and Users from Snowflake and creates tabular reports in universally accessible Excel and CSV formats with tables and pivots. Role polyarchy is visualized in SVG, PNG and PDF formats, easily displayed in your web browsers.
Here is an example of visual representation of Role structure and Databases used by those Roles, color-coded to Role Functional/Access type:
Many customers have hundreds or even thousands of Roles. Building a single graph with all of them present can result in overwhelming number of objects that can be unreadable. That is a problem common to many complex graph visualizations. Here is a visualization of close to a thousand roles of RBAC hierarchies where no control or planning was done whatsoever:
While admittingly neat looking, above visualization is not helpful for understanding of what relates to what. Snowflake Grant Report solves this by preparing a graph for every Role that includes only that Role’s direct relations, and by prerendering SVG, PNG and PDF files for quick display. Drilling deeper, we can see a Role structure for one of the areas of the organizational structure that is easily understood:
The visualizations are made using GraphViz, a popular open source graph visualization software package (https://graphviz.org/). Customers wanting to make changes to the existing Role polyarchy can take a Role graph and modify it in an online editor without any additional programming to model desired results and see if they fit the business needs.
One of the useful tabular reports is list of all Grants for the TABLE Object Type. Similar tables exist for all other object types:
Focusing on auditing of specific Database, here is a view of all Grants for Schema, Table and View object in a Database, referenced for each Role that is involved. Similar reports are provided for every Database in your Snowflake deployment:
The tabular reports format data in a more user-friendly way that can be useful in discovering what permissions are present and offer a more portable way of exchanging data with your auditing team than just a big CSV file.
Conclusion
Snowflake Grant Report helps customers understand and document Snowflake RBAC settings. The Role hierarchy graphs are a powerful aid to structuring your Roles, whether you choose to implement Functional and Access Roles concepts or design your own structure.
Snowflake Grant Report can operate in both online mode where it utilized cloud layer services to retrieve lists of Roles and Grants, as well as in offline mode, using data exported from ACCOUNT_USAGE views. Offline mode can be useful where direct access to Snowflake is not feasible or customer has multiple Snowflake deployments.
Snowflake Grant Report runs on any OS and is available from Snowflake Labs open source portal at https://github.com/Snowflake-Labs/sfgrantreport.
