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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
In this practical guide, we’ll explore techniques to help you secure your use of Snowflake:<\/p>\n\n\n
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.<\/p>\n\n\n\n
Before diving into advanced security features, let’s establish a solid foundation. Proper user and role management is crucial for maintaining security at scale.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
Step 1, Create standard roles: <\/strong><\/p>\n\n\n\n Step 2, Give standard rights to your roles:<\/strong><\/p>\n\n\n\n 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:<\/p>\n\n\n\n Step 3, Put users into these roles:<\/strong><\/p>\n\n\n\n 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 “ Before we dive into specific security implementations, let’s understand Secure Views<\/a> – a useful, fundamental building block of building a secure Snowflake environment. Secure views differ from regular views<\/a> in several crucial ways:<\/p>\n\n\n Example of a basic secure view<\/p>\n\n\n\n This view will give the user access to only these columns of the For a normal view, users that can query the view, can also use the Executing this statement will result in an error such as:<\/p>\n\n\n\n The 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Next, I will create a role-based mapping table. This table will map roles that can see each of the different region\u2019s data.<\/p>\n\n\n\n Why role-based instead of user-based?<\/p>\n\n\n 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.<\/p>\n\n\n\n Next I will create the view for the users to use instead of the Finally, you need to grant the users that will view the data, rights to use the Important considerations for Let’s break down how this works:<\/p>\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Create a row access policy<\/p>\n\n\n\n Let’s break down this policy:<\/p>\n\n\n Key differences between approaches:<\/p>\n\n\n This powerful Snowflake security feature is essential for implementing data privacy controls \u2013 imagine you’re working with sensitive customer data, and different teams need varying levels of access to the same information.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Important considerations for masking policies:<\/p>\n\n\n 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\u2019s essential to understand some of the limitations listed here<\/a> to design robust masking policies.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Data in Transit Security:<\/strong><\/p>\n\n\n\n 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:<\/p>\n\n\n This policy ensures that:<\/p>\n\n\n 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:<\/p>\n\n\n\n This setup provides:<\/p>\n\n\n-- Create functional roles\nCREATE ROLE data_scientist;\n\nCREATE ROLE analyst;\n\nCREATE ROLE data_engineer;<\/pre>\n\n\n\n
-- Grant access to specific schemas\nGRANT USAGE ON SCHEMA analytics.public TO ROLE analyst;\n\nGRANT SELECT ON ALL TABLES IN SCHEMA analytics.public TO ROLE analyst;<\/pre>\n\n\n\n
-- Environment-specific user management \n--(separate script per environment)\nCREATE USER bob_smith;\n\nGRANT ROLE analyst TO USER bob_smith;<\/pre>\n\n\n\n
bob@company.com<\/code>“, use a role like “ACCOUNTING_ANALYST<\/code>” that can be assigned to different users in different environments.<\/p>\n\n\n\nSecure Views: Your First Line of Defense<\/h2>\n\n\n\n
\n
CREATE OR REPLACE SECURE VIEW vw_customer_basic \nAS \nSELECT \n customer_id, \n customer_name, \n city, \n country \nFROM customer_data; <\/pre>\n\n\n\n
customer_data<\/code> table. And you can specify a WHERE<\/code> clause to partition the data to only a subset of the rows too. Most any SQL Query can be used in a view.<\/p>\n\n\n\nDESCRIBE<\/code> 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:<\/p>\n\n\n\nDESCRIBE VIEW vw_customer_basic; <\/code><\/pre>\n\n\n\nError: Insufficient privileges to operate on view 'VW_CUSTOMER_BASIC'<\/code><\/p>\n\n\n\nSECURE<\/code> keyword is crucial here when you wish to keep the implementation details hidden from the typical user.<\/p>\n\n\n\nRow-Level Security (RLS): Multiple Approaches<\/h2>\n\n\n\n
Approach 1: View-Based RLS<\/h3>\n\n\n\n
CREATE TABLE sales_data (\n sale_id INTEGER,\n region STRING,\n amount FLOAT,\n customer_email STRING\n);<\/pre>\n\n\n\n
CREATE TABLE role_region_mapping (\n role_name STRING,\n region STRING\n);\n\n-- Define access rules\nINSERT INTO role_region_mapping VALUES\n ('NORTH_AMERICA_SALES', 'NA'),\n ('EUROPE_SALES', 'EU');<\/pre>\n\n\n\n\n
sales_data<\/code> table.<\/p>\n\n\n\n-- Create the secure view with RLS\nCREATE OR REPLACE SECURE VIEW vw_sales_data \nAS\nSELECT s.*\nFROM sales_data s\nWHERE s.region IN (\n SELECT region \n FROM role_region_mapping \n WHERE role_name = CURRENT_ROLE()\n);<\/pre>\n\n\n\n
vw_sales_data <\/code>view, but not the sales_data<\/code> table. This leaves their only view of the data to be what they can see in the view.<\/p>\n\n\n\nCURRENT_ROLE()<\/code>:<\/p>\n\n\n\n
USE ROLE role_name<\/code><\/li>\n\n\n\nSYSADMIN<\/code> role) typically have access to all data unless explicitly restricted. However, it is something you need to be careful with, because if their default ROLE<\/code> is SYSADMIN<\/code>, no data would be returned from the SECURE<\/code> VIEW<\/code> as written.<\/li>\n<\/ul>\n<\/div>\n\n\n\n
SECURE<\/code> keyword prevents users from seeing the view definition<\/li>\n\n\n\nCURRENT_ROLE()<\/code> returns the current user’s active role<\/li>\n\n\n\nSELECT region FROM role_region_mapping WHERE role_name = CURRENT_ROLE()<\/code> gets the allowed regions for the current role<\/li>\n\n\n\nWHERE s.region IN (...)<\/code> clause filters the data to only show rows from allowed regions<\/li>\n<\/ul>\n<\/div>\n\n\nApproach 2: Row Access Policy<\/h3>\n\n\n\n
CREATE OR REPLACE ROW ACCESS POLICY sales_region_policy \nAS (region STRING) \n RETURNS BOOLEAN ->\n EXISTS (\n SELECT 1 \n FROM role_region_mapping \n WHERE role_name = CURRENT_ROLE()\n AND region = sales_data.region\n );\n\n-- Apply the policy to the table\nALTER TABLE sales_data \n ADD ROW ACCESS POLICY sales_region_policy ON (region);<\/pre>\n\n\n\n
\n
AS (region STRING)<\/code> – Specifies the input parameter and its type. This must match the column type in your table<\/li>\n\n\n\nRETURNS BOOLEAN<\/code> – The policy must return true\/false to determine if a row should be visible<\/li>\n\n\n\nEXISTS (...)<\/code> – Checks if there’s a mapping between the current role and the region<\/li>\n\n\n\nsales_data.region<\/code> – References the region column from the table where the policy is applied<\/li>\n<\/ul>\n<\/div>\n\n\n\n
Dynamic Data Masking: Advanced Data Protection for Sensitive Information<\/h2>\n\n\n\n
-- Create a sophisticated masking policy\nCREATE OR REPLACE MASKING POLICY credit_card_mask \nAS (val STRING) RETURNS STRING ->\n CASE\n WHEN CURRENT_ROLE() = 'FINANCIAL_ADMIN' THEN val\n WHEN CURRENT_ROLE() = 'CUSTOMER_SERVICE' \n THEN CONCAT('XXXX-XXXX-XXXX-', RIGHT(val, 4))\n ELSE 'XXXX-XXXX-XXXX-XXXX'\n END;\n\n-- Apply it to your table\nALTER TABLE customer_data MODIFY COLUMN credit_card_number \n SET MASKING POLICY credit_card_mask;<\/pre>\n\n\n\n\n
WHERE credit_card = '1111-1111-1111-1111'<\/code>) will still match masked records if the user has appropriate permissions to query the underlying data<\/li>\n<\/ul>\n<\/div>\n\n\nEncryption: The Foundation of Cloud Data Security<\/h2>\n\n\n\n
\n
-- Create a network access policy\nCREATE OR REPLACE NETWORK POLICY restricted_access\n -- Internal networks\n ALLOWED_IP_LIST = ('10.0.0.0\/8', '192.168.1.0\/24') \n BLOCKED_IP_LIST = ('172.16.0.0\/12') -- Blocked ranges\n COMMENT = 'Network policy for production environment';\n\n-- Apply to specific account objects\nALTER ACCOUNT SET NETWORK_POLICY = restricted_access;\n\n-- Monitor network usage and policy violations\nSELECT * \nFROM snowflake.account_usage.network_policy_events\nWHERE event_timestamp >= dateadd(day, -7, current_timestamp())\nORDER BY event_timestamp DESC;<\/pre>\n\n\n\n\n
Data at Rest Protection:<\/h3>\n\n\n\n
-- Step 1: Create the key\nCREATE OR REPLACE MASTER KEY encryption_key\n TYPE = 'AWS_KMS'\n CREDENTIAL = (AWS_KEY_ARN = 'arn:aws:kms:region:account:key\/key-id')\n COMMENT = 'Master key for PII data encryption';\n\n-- Step 2: Apply to specific objects\nALTER DATABASE sensitive_data \n SET MASTER KEY = encryption_key;\n\n-- Step 3: Monitor key usage\nSELECT * \nFROM snowflake.account_usage.key_usage_history\nWHERE key_name = 'encryption_key'\nAND usage_date >= dateadd(day, -30, current_date)\nORDER BY usage_date DESC;<\/pre>\n\n\n\n
\n
Real-world Security Best Practices<\/h2>\n\n\n\n