Mastering the Basics with Snowflake RBAC, Part IV: Architecture for Scale

True to its promise, the Snowflake Data Cloud makes database administration simpler, less time-intensive and reduces the number of tasks required to maintain a productive environment. Features like Time Travel solve for backup management. Managing indexes is a thing of the past. You can clone an entire database in less than the time it takes to read this paragraph.

Where things get trickier is configuring access control.  

In a couple of minutes you can understand enough about granting roles to create your first role relationship, but it isn’t long before the relationships between administrative, functional and data access roles gets unwieldy quickly.  

The combination of permutations is staggering. Where a new Snowflake account may have only a handful of users that can be mapped to a small number of roles, with a growing system users start to number in the hundreds, then thousands, and exploding numbers of schema objects proliferate. If you didn’t get the security foundation right initially, it is going to be an enormous challenge to scale, audit, administer (and repair). 

Here are five foundational Snowflake RBAC best practices that will partition RBAC roles appropriately, enable scaling, allow for extending functionality, make auditing easier and allow for one-click environment creation–all while pushing the burden of user administration back onto the business managers themselves.  

Administrative Roles 

Keeping the administrative built-in roles clean is step one.  

If not closely watched, built-in administrative roles start to acquire privileges they shouldn’t. ACCOUNTADMINs start owning databases and users, SYSADMINs granting roles, SECURITYADMINs owning objects and usage on roles. Perhaps an environment started as a POC, but is morphing into quasi-production. In these situations, administrative permission issues may not have been cleaned up properly. Because working with privileges in Snowflake is challenging, early users tend to have elevated permissions that they shouldn’t – because it is the fastest way to step around the array of challenges and blocks that Snowflake throws up working with schema objects. 

1. Keep the ACCOUNTADMIN role clean–minimum users–no more than 2 ideally. 
2. Make accessing the ACCOUNTADMIN role a deliberate choice through a standalone login. 
3. Partition the ownership of roles from the ownership of objects. 
4. Eliminate role ownership for all roles but SECURITYADMIN and USERADMIN. 
5. Eliminate object ownership from SECURITYADMIN and USERADMIN.
6. Don’t use the ACCOUNTADMIN where a lower-level role can fulfill the same function out-of-the-box. 

Systems grow in complexity, but the more the administrative roles can preserve their out-of-the-box grants, the better. 

Specialty Administrative Roles 

When a database administrator becomes a bottleneck for business processes like monitoring resources, creating shares, and configuring warehouses, then create a standalone administrative role for those purposes. ACCOUNTADMIN should own these roles and their privilege scope should be limited to these tasks alone, inheriting no other roles. 

This will create faster response times with the business, lower costs, and allow the Snowflake database administrator time to focus on other tasks. 

This approach is in keeping with the “use the ACCOUNTADMIN role as minimally as possible” theme.

Functional Roles 

The core principle of RBAC is to grant permissions on what people do not who they are. You are essentially mapping to a job description, not a person.   

Defining roles and creating hierarchies of how they relate to each other is easy to over-engineer and over-complicate. Accept trade-offs or pay the price in confusion. The RBAC design shouldn’t become an org-chart of business relationships.  

But where should these roles be nested? And how does one manage the tradeoffs between “the minimal permissions needed” and “role manageability? This is not to mention working effectively with identity providers like Okta or Active Directory. 

Here are some critical Snowflake RBAC best practices: 

1. Functional roles should be created to which business groups are mapped.  
2. Design the functional roles directly with the business’s input.  
3. Expect the business to struggle with some of these questions as well. They aren’t straightforward and the overlap between roles may be blurry to begin with.  
4. Most importantly, don’t guess at the data access needs for the roles. For both practical and political reasons as well as to sanity check decisions, these decisions must be made at the business level.  
5. Prioritize keeping functional role nesting minimal. Flatten. If Snowflake RBAC best practices are observed with the data access roles, the need to nest functional roles decreases substantially.  
6. Do not map functional roles directly to schema objects. They will map to data access roles that manage all data access (and can be managed in one place) 

There are tradeoffs between highly granular role and data access design and maintainability. If schemas are used to partition sensitive data, for example, then Snowflake features like ON FUTURE become powerful tools. Good RBAC design will always find a balance. If the security gets too cumbersome, then it will open up a new set of vulnerabilities and privilege review challenges. 

Data Access Roles 

A fundamental tenet of RBAC design: 

In a well-designed RBAC implementation all data access is made through roles dedicated to that purpose. Users are never mapped to these directly. Just as functional roles are never mapped to data objects, users are never mapped to data access roles. A system will not scale if the data access roles are complex–or even hierarchical.  

A powerful design pattern is that every schema has four data access roles–and no more. One reads, one writes, one creates objects and one that has full permission to perform any action on a schema object.  

If sensitive data is properly partitioned or managed through row and column security controls, then no additional roles should be required–and new tables, views, stored procedures, and so on can be added through future grants without tailoring them for specific roles.  

Environment Administrative Roles 

As systems grow more complex, it becomes necessary to create new environments, often on the fly. Perhaps a new collection of databases and warehouses are needed for a development environment or system integration testing.  

Environment Administrative roles take the burden of a single SYSADMIN and SECURITYADMIN having to administer every one of these instances. Instead, it is possible to create an environment administrator role.  

This role has the permissions required to operate on all the objects within the environment that it has usage of (but nowhere else). By creating a role that is all-powerful within a scoped environment, the SYSADMIN and SECURITYADMIN can delegate their work down. These environment administrative roles, of course, are inherited by the actual SYSADMIN and SECURITYADMIN. 

For example, imagine a DEV_MKTG_SYSADMIN or a SIT_SECURITYADMIN. These can be created from the very beginning of a Snowflake environment and the higher-level SYSADMIN and SECURITYADMIN users can be assigned these Environment Administrative roles. Later, this role can be delegated down to other users. 

Other Snowflake RBAC Best Practices 

1. Always use managed access schemas–unmanaged schemas introduce the possibility of object owners making grants with their objects that can’t be approved or monitored and may violate the security design. A managed access schema concentrates those privileges within the schema owner only. 
2. Use ON FUTURE grants. A thoughtful ON FUTURE strategy–along with well designed data access roles will reduce complexity and second-guessing when creating new objects. 
3. Create standalone roles for cross database connections.
4. Use a strict prefix naming convention on roles, warehouses, databases, and schemas. Leverage Snowflake’s tag features to identify logical sets of coupled elements that comprise an “environment.” In a thoughtfully named and tagged environment, an administrator can create a new security environment at the click of a button. 
5. Remove permissions from the PUBLIC role. Permissions to PUBLIC subtly are granted to all other roles and may introduce a stealth permissions violation that can’t be seen with the show grants to role command.

Conclusion 

A well architected RBAC design will partition roles into a minimum of five logical categories. Each of the categories ensures that the system will be secure, scale, auditable and reproducible through automation.  

No role will have ownership privileges it should not. Secure data will be protected. The effort required of administrators can be distributed. Finally, the business owners and the database administrators will have a shared understanding of where privileges should be assigned. This will lead to a system that can be readily audited and drive a shared understanding of who needs what permissions and why. 

While the initial effort of working to define the functional roles with the business may be challenging and require significant time investment, the results will impact the business on cost, security, and scalability. 

Want to see how you can take your Snowflake migration to the next level? Learn more about how our experts can help your business today.

Click here to read Part III: Padlocking the Schema.

About the Author

Adam Nathan is the Director of Sales Engineering at CoEnterprise and leads our consultancy’s Snowflake practice. With over two decades in data warehousing, business intelligence, and advanced analytics, he’s passionate about supporting clients in the adoption and growth of their Snowflake implementations. Outreach for Snowflake consulting support, thoughtful suggestions, and irate critiques on these posts are all welcome, in that order. You can reach him at adam.nathan@coenterprise.com.