As a data engineer who’s spent the last few years implementing enterprise-scale security in Snowflake, I’ve witnessed firsthand how challenging it can be to architect a robust security framework that balances protection with accessibility. Whether you’re dealing with regulatory compliance requirements, implementing multi-tenant data solutions, or simply trying to ensure proper data access controls, the journey can seem daunting at first.

I remember sitting in a meeting where our CISO (Chief Information Security Officer) asked for row-level security implementation across our data warehouse, while simultaneously requiring dynamic data masking for PII, and maintaining high-performance query execution. The wealth of Snowflake security features available was both a blessing and a challenge — having so many options meant we could solve any security requirement, but it also meant we needed to carefully architect our solution.

In this practical guide, we’ll explore techniques to help you secure your use of Snowflake:

I’ll walk you through battle-tested approaches to implementing Snowflake security features, sharing real implementation patterns that worked (and some that didn’t) in production environments. We’ll focus on concrete examples and scenarios you’re likely to encounter in your day-to-day work as a data engineer.

Foundational Security Setup: Setting up access to your Snowflake database

Before diving into advanced security features, let’s establish a solid foundation. Proper user and role management is crucial for maintaining security at scale.

In Snowflake, all access control is managed through roles, not direct user permissions. Users cannot log in directly to database objects – they must be assigned to roles that have been granted the necessary privileges. Here’s how to structure your roles effectively:

Step 1, Create standard roles:

Step 2, Give standard rights to your roles:

All database object access should be granted to these functional roles, which can then be assigned to users. This approach allows you to version control your security model and maintain consistency across environments. The user-to-role assignments can be managed separately for each environment:

Step 3, Put users into these roles:

Pro Tip: When testing security implementations, always use roles instead of specific users. This allows your security patterns to remain consistent across environments. For example, instead of testing with “bob@company.com“, use a role like “ACCOUNTING_ANALYST” that can be assigned to different users in different environments.

Secure Views: Your First Line of Defense

Before we dive into specific security implementations, let’s understand Secure Views – a useful, fundamental building block of building a secure Snowflake environment. Secure views differ from regular views in several crucial ways:

Example of a basic secure view

This view will give the user access to only these columns of the customer_data table. And you can specify a WHERE clause to partition the data to only a subset of the rows too. Most any SQL Query can be used in a view.

For a normal view, users that can query the view, can also use the DESCRIBE view to see the underlying query. For a secure view, they cannot get the definition and will get this error when trying to view the definition:

Executing this statement will result in an error such as:

Error: Insufficient privileges to operate on view 'VW_CUSTOMER_BASIC'

The SECURE keyword is crucial here when you wish to keep the implementation details hidden from the typical user.

Row-Level Security (RLS): Multiple Approaches

Row-Level Security allows you to control which rows each user can see in a table based on their role or other attributes. This is crucial for implementing data segregation in multi-tenant environments or enforcing data access boundaries between departments.

Approach 1: View-Based RLS

View-based RLS is a security pattern that restricts which rows a user can see in a table based on their assigned role. Let’s break down the implementation step by step with examples and explain why each component is important.

This is the traditional approach using secure views and mapping tables. First, let’s create sample data. This table contains sales data from different regions. Without RLS, any user with SELECT permissions could see all records.

Next, I will create a role-based mapping table. This table will map roles that can see each of the different region’s data.

Why role-based instead of user-based?

While it is better to user roles than users to make management easier, but the technique can be used with many attributes of a user if necessary.

Next I will create the view for the users to use instead of the sales_data table.

Finally, you need to grant the users that will view the data, rights to use the vw_sales_data view, but not the sales_data table. This leaves their only view of the data to be what they can see in the view.

Important considerations for CURRENT_ROLE():

Let’s break down how this works:

Approach 2: Row Access Policy

Row Access Policy is Snowflake’s native method for implementing row-level security directly at the table level, without requiring secure views. It’s more maintainable and can be reused across multiple tables.

Note: It does tend to be so hidden that it can be a bit confusing to users who think they are querying a full table but are being filtered in a hidden policy.

Here is the code you could use to duplicate the sort of filtering that we implemented in the View Based RLS section that preceded this one:

Create a row access policy

Let’s break down this policy:

Key differences between approaches:

Dynamic Data Masking: Advanced Data Protection for Sensitive Information

This powerful Snowflake security feature is essential for implementing data privacy controls – imagine you’re working with sensitive customer data, and different teams need varying levels of access to the same information.

Some need to see full credit card numbers, others just the last four digits, and some shouldn’t see them at all. Here’s a real example from when I implemented PII protection for our customer service application:

Important considerations for masking policies:

A lesson learned in data privacy: Always test your masking policies with different roles before pushing to production. I once accidentally masked data for our fraud detection team, and well… let’s just say it was an interesting day in our data security journey. Apart from testing the masking policies, it’s essential to understand some of the limitations listed here to design robust masking policies.

Encryption: The Foundation of Cloud Data Security

While Snowflake handles most encryption automatically, there are still some crucial aspects of data protection you need to know. Here’s what I wish someone had told me about implementing encryption in Snowflake:

Data in Transit Security:

Data in Transit Security protects information as it moves between systems. While Snowflake automatically encrypts network traffic using TLS 1.2, you can add additional security through Network Policies. Here’s how to implement comprehensive network security for:

This policy ensures that:

Data at Rest Protection:

Data at Rest Protection ensures your stored data remains secure through encryption. While Snowflake provides default encryption, you can implement Customer Managed Keys (CMK) for additional control:

This setup provides:

Real-world Security Best Practices

In this section I will cover some security best practices you should understand.Start with Security Fundamentals

Begin with these essential security measures before implementing more advanced features:

Without setting up proper access to objects and data, understanding the data you have that should not be seen by just anyone, you get most of what you need to lock down your access. But making sure to keep an eye on users’ activity is also crucial, just in case someone is doing something they shouldn’t be. (Which is often because they don’t realize the boundaries that have been configured, but is sometime for nefarious reasons.)

Document Your Security Implementation:

Of course, no one likes to do documentation. But whether we like it or not, documentation is crucial for so many things:

And as much as we complain about writing it, we also tend to complain about NOT having it when we need it even more.

In-Database Documentation:

A nice place to have some documentation is stored right with the objects. When you need to know details quickly, having them where you can use the COMMENT ON command. For example:

You can then view the comments when you use the DESC command, such as:

And you will see details about that view, including the comment that would be added by the previous command. You can also see the values when using the SHOW command. For more examples see this documentation on the Snowflake site.

General Documentation

Of course, you need more documentation than just what you can apply to your objects. A few types of documentation might include the following:

Change Management

Perhaps the most important documentation is capturing what security should look like, and then keeping up with the difference as time changes so you know what changes were made within policy, and which are made outside of policy.

Implement Security Monitoring and Auditing:

Once you have security configures and working, it is important to audit your server regular for changes and proper utilization of resources. In this section, I will present queries that have has saved me many times in security audits

Monitor failed access attempts:

Track role membership changes:

These queries are valuable for Security Monitoring by identifying failed and unauthorized access attempts, permission issues, and potential breaches. It’s also useful for Troubleshooting by showing which queries are failing, which users are affected and providing error messages. Additionally, it serves as an Audit Trail by creating a record of failed attempts, which is helpful during security audits and for tracking suspicious activity.

Another query that can help you monitor sensitive data access:

Common Data Security Pitfalls to Avoid

As I wrap up this article, here are some brief tips to guide you along the way as you set up and maintain your Snowflake security:

Role Complexity

Security Function Usage

Monitoring Gaps

Documentation Deficits

Conclusion

Implementing enterprise-grade security in Snowflake is a journey, not a destination. Start with the fundamentals of data protection, test your security controls thoroughly, and gradually add more sophisticated measures as needed. Remember, the goal is to protect sensitive data while ensuring people can still do their jobs effectively.

I’m constantly learning new things about Snowflake security features and compliance requirements, and I update my implementations accordingly. The most important thing is to stay curious and keep testing your assumptions about how your security measures work.

Keep an eye on Snowflake’s security documentation and release notes – they’re constantly adding new data governance features that might enhance your security architecture!