Auditing Changes in Security Configuration with the Snowflake Grant Report Tool
Overview
Snowflake Role-based Access Control (RBAC) offers customers powerful tools to configure authorization to secure their systems. As the configuration of the system change over time, many security-conscious customers have a need to audit the changes.
To help with an audit, Snowflake Grant Report now provides comparison of the security configuration between two points in time or between environments, helping customer observe the configuration changes in a single dashboard.
Producing Grant Comparison Report
As my previous article Managing Snowflake Roles, Grants, and Privileges with the Snowflake Grant Report Tool describes, Snowflake Grant Report retrieves list of Roles and object Grants and creates tabular reports in universally accessible Excel and CSV formats.
Even simple file comparison of the Grant files produced by Snowflake Grant Report can provide some insights into which Grants have been added and which ones were removed:
However, text or CSV record comparison isn’t all that fun to do at scale. Snowflake Grant Report can do much better.
I have access to a pretty busy sandbox-style Snowflake deployments that many of my colleagues use. Back in late 2020 I created a Grant Report (in C:\snowflake\SnowflakeGrantReport\Reports\aws_cas1.12012020). Now in mid August 2021 I made another one (in C:\snowflake\SnowflakeGrantReport\Reports\aws_cas1.offline.08192021).
I run comparison specifying these folders in --left-folder-compare and --right-folder-compare options. It takes just a few seconds to generate the report of the differences:
Types of Grant Configuration Differences
For purposes of comparison, the unique Grant item that is being compared is defined as “Grant of Privilege on Object to Role”. For example, “USAGE privilege on DATABASE object named Foo is granted to ROLE Bar”.
Resulting report is a table showing color-coded DIFFERENT, MISSING and EXTRA Grant assignments:
If a Grant didn’t exist in the report on the left but now exists in report on the right, it is classified as “EXTRA (only R)” in yellow.
If a Grant exist in report on the left but doesn’t exist on report on the right, it is classified as “MISSING (only L)” in blue.
If a Grant exists in both left and right reports, and has any differences in the WithGrantOption (whether grant is re-grantable further), GrantedBy (which role made this assignment) and CreatedOn (when this assignment was made) properties, it is classified as “DIFFERENT” in pink.
Identical items are ignored.
Grant Configuration Differences
For easier auditing, the differences report also provides a single-page matrix output where objects are listed in the rows and roles in the columns, with types of differences color coded in the intersecting cells.
“EXTRA (only R)” grants are prefixed with “>>” and are yellow.
“MISSING (only L)” grants are prefixed with “<<” and are blue.
“DIFFERENT” grants are prefixed with “~~” and are pink.
Any combination of those above for single object/role combo is colored purple.
This matrix is created for each type of the object, allowing you to focus on just that area:
Sometimes it is fun to zoom way out for those abstract patterns. In here, I am looking at ongoing reconfiguration of the role polyarchy with objects of ROLE type:
Comparing Different Environments
Perhaps you have a main environment and replica in another region that should be identical?
Or you are one of the customers who decided to separate production and preproduction environments and want to see the differences?
Because the format of the Snowflake Grant Reports isn’t tied to any environment, it is possible to compare any environment to any other.
Conclusion
Snowflake Grant Report helps customers understand and document Snowflake RBAC settings. With the introduction of Grant differences report, customers can now audit security changes of Snowflake configuration over time and manage differences between environments.
Snowflake Grant Report runs on any OS and is available from Snowflake Labs open source portal at https://github.com/Snowflake-Labs/sfgrantreport.
