π Data Encryption in Snowflake: Ensuring Privacy & Granular Access
Encryption is a fundamental building block of data privacy and security. In Snowflake, data encryption ensures that sensitive data is protected at rest and in transit, while enabling granular access controls to balance security with usability.
π What is Encryption?β
Encryption is the process of converting data into an unreadable format (ciphertext) using algorithms and keys. Only users with the right keys can decrypt and read the original data (plaintext). This ensures that even if data is accessed by unauthorized parties, it remains unreadable.
π Key Encryption Concepts in Snowflakeβ
- β
At-Rest Encryption
Snowflake automatically encrypts all data at rest (data stored in tables, databases, or stage files).
- Uses AES-256 strong encryption.
- Transparent to the user β no extra configuration needed.
- β
In-Transit Encryption
All data moving in and out of Snowflake (queries, data loads/unloads) is encrypted using TLS (Transport Layer Security).
- Prevents eavesdropping or tampering during transit.
- β
Key Hierarchy
Snowflake uses a hierarchical key management system:
- Root keys
- Account-level keys
- Table-level keys
Keys are automatically rotated for enhanced security.
βοΈ Granular Access Control & Encryptionβ
While encryption ensures data is secure at the storage layer, granular access control ensures only the right users can access decrypted data.
πΉ Role-Based Access Control (RBAC) β Roles determine who can see decrypted data.
πΉ Column-level security & masking β Works alongside encryption for even more granular protection.
πΉ Network policies & MFA β Add extra layers of security.
π‘ Encryption Use Cases in Snowflakeβ
- β Regulatory compliance β e.g., GDPR, HIPAA, PCI DSS.
- β Securing sensitive workloads β like financial data, healthcare data, or PII.
- β Cross-cloud data sharing β Snowflakeβs secure sharing works with encrypted data.
π Best Practices for Encryption in Snowflakeβ
- β Use built-in Snowflake encryption β No extra setup needed for AES-256 encryption at rest.
- β Layer security controls β Combine encryption with access controls, masking, and MFA.
- β Leverage customer-managed keys (if needed) β For even more control, Snowflake offers Tri-Secret Secure (where you bring your own key to work alongside Snowflakeβs).
- β Monitor and audit β Use Snowflakeβs Account Usage views to track key usage and access.
π οΈ Example Use Case: Protecting Financial Data with Encryption & Access Controlsβ
Letβs say you have a financial transactions table that stores sensitive data, like credit card numbers.
1οΈβ£ Create a Table to Store Sensitive Dataβ
CREATE TABLE financial_transactions (
transaction_id INT,
customer_name STRING,
credit_card_number STRING,
transaction_amount FLOAT
);
- β Note: Snowflake automatically encrypts this data at rest with AES-256 encryption.
2οΈβ£ Insert Some Sample Dataβ
INSERT INTO financial_transactions VALUES
(1, 'Ananya', '4111111111111111', 250.00),
(2, 'Rohan', '5500000000000004', 400.50);
3οΈβ£ Create a Masking Policy for the Credit Card Columnβ
Even though encryption at rest is already active, we want to add an extra layer:
- β Dynamic masking to restrict who can see the full card number.
CREATE MASKING POLICY mask_credit_card AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('FINANCE_ADMIN') THEN val
ELSE 'XXXX-XXXX-XXXX-' || RIGHT(val, 4)
END;
4οΈβ£ Apply the Masking Policy to the Columnβ
ALTER TABLE financial_transactions
MODIFY COLUMN credit_card_number
SET MASKING POLICY mask_credit_card;
5οΈβ£ Test Queries with Different Rolesβ
π As a Regular User (no finance admin privileges):β
-- Assume CURRENT_ROLE() is 'ANALYST'
SELECT transaction_id, customer_name, credit_card_number
FROM financial_transactions;
Output:
| transaction_id | customer_name | credit_card_number |
|---|---|---|
| 1 | Ananya | XXXX-XXXX-XXXX-1111 |
| 2 | Rohan | XXXX-XXXX-XXXX-0004 |
π As a Finance Admin User (has FINANCE_ADMIN role):β
-- Assume CURRENT_ROLE() is 'FINANCE_ADMIN'
SELECT transaction_id, customer_name, credit_card_number
FROM financial_transactions;
Output:
| transaction_id | customer_name | credit_card_number |
|---|---|---|
| 1 | Ananya | 4111111111111111 |
| 2 | Rohan | 5500000000000004 |
π Putting it All Togetherβ
- β Encryption at rest: Snowflake encrypts all stored data automatically.
- β Granular access: Masking policies hide sensitive parts of the data dynamically based on roles.
- β
No changes to queries: Users write the same
SELECTstatement, but see only what theyβre allowed to see!
π οΈ Manual Column-Level AES-256 Encryption (Client-Side)β
Snowflakeβs built-in AES-256 encryption cannot be manually enabled/disabled at column-levelβitβs always on. However, you can encrypt data before inserting it into Snowflake (client-side encryption).
πΉ Encryption Example with Pythonβ
from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
import os
from cryptography.hazmat.primitives import padding
plaintext = b'4111111111111111' # Example credit card number
key = os.urandom(32) # Securely store this key
iv = os.urandom(16)
cipher = Cipher(algorithms.AES(key), modes.CBC(iv))
encryptor = cipher.encryptor()
padder = padding.PKCS7(128).padder()
padded_data = padder.update(plaintext) + padder.finalize()
ciphertext = encryptor.update(padded_data) + encryptor.finalize()
print(ciphertext.hex()) # Store in Snowflake
πΉ Storing in Snowflakeβ
CREATE TABLE encrypted_data (
id INT,
encrypted_card_number STRING, -- ciphertext (hex)
iv STRING -- IV (hex)
);
INSERT INTO encrypted_data VALUES
(1, 'f2c33c4e2a64c3f...', 'ecff1a334a332bc5...');
πΉ Decryption (Client-Side)β
cipher = Cipher(algorithms.AES(key), modes.CBC(iv))
decryptor = cipher.decryptor()
padded_plaintext = decryptor.update(ciphertext) + decryptor.finalize()
unpadder = padding.PKCS7(128).unpadder()
plaintext = unpadder.update(padded_plaintext) + unpadder.finalize()
print(plaintext.decode())
π‘ Key Takeawaysβ
βοΈ Automatic AES-256 encryption in Snowflake β always on, no user config needed. βοΈ Client-side encryption can add column-level protection if needed (no built-in SQL decryption). βοΈ Masking policies + RBAC roles = granular, dynamic access to decrypted data. βοΈ Perfect for PCI DSS, GDPR, and secure data-sharing scenarios.