Snowflake Security Best Practices

Snowflake provides a multi-layered security architecture that protects data at rest, in transit, and during processing.

Authentication Methods

Password Authentication

-- Create user with password
CREATE USER analyst_user
  PASSWORD = 'SecureP@ssw0rd!'
  LOGIN_NAME = 'analyst_user'
  DEFAULT_ROLE = 'ANALYST'
  MUST_CHANGE_PASSWORD = TRUE;

-- Force password change
ALTER USER analyst_user SET PASSWORD = 'NewP@ssw0rd!';

Key Pair Authentication

-- Create user with RSA key pair
CREATE USER service_user
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhk...',
  DEFAULT_ROLE = 'SERVICE_ROLE'
  LOGIN_NAME = 'service_user';

-- Rotate key pair
ALTER USER service_user SET RSA_PUBLIC_KEY = 'new_public_key...';

OAuth Authentication

-- Create OAuth integration
CREATE SECURITY INTEGRATION oauth_integration
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://myapp.com/callback';

-- Create user for OAuth
CREATE USER oauth_user
  LOGIN_NAME = 'oauth_user'
  DEFAULT_ROLE = 'ANALYST'
  TYPE = PERSON;

Role-Based Access Control (RBAC)

Creating a Role Hierarchy

-- Create role hierarchy
CREATE ROLE admin_role;
CREATE ROLE manager_role;
CREATE ROLE analyst_role;
CREATE ROLE viewer_role;

-- Set up inheritance
GRANT ROLE manager_role TO ROLE admin_role;
GRANT ROLE analyst_role TO ROLE manager_role;
GRANT ROLE viewer_role TO ROLE analyst_role;

-- Assign roles to users
GRANT ROLE admin_role TO USER admin_user;
GRANT ROLE analyst_role TO USER analyst_user;

Granting Privileges

-- Grant database privileges
GRANT USAGE ON DATABASE analytics_db TO ROLE analyst_role;
GRANT CREATE SCHEMA ON DATABASE analytics_db TO ROLE manager_role;

-- Grant schema privileges
GRANT USAGE ON SCHEMA analytics_db.production TO ROLE analyst_role;
GRANT CREATE TABLE ON SCHEMA analytics_db.production TO ROLE manager_role;

-- Grant table privileges
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.production TO ROLE analyst_role;
GRANT INSERT, UPDATE, DELETE ON TABLE analytics_db.production.orders TO ROLE manager_role;

Column-Level Security

Creating Masking Policies

-- Create masking policy for sensitive data
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ADMIN', 'PRIVACY_OFFICER') THEN val
    WHEN CURRENT_ROLE() = 'ANALYST' THEN REGEXP_REPLACE(val, '.+@', '****@')
    ELSE '***MASKED***'
  END;

-- Apply masking policy to column
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

-- Create masking policy for numeric data
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ADMIN', 'HR') THEN val
    ELSE 'XXX-XX-' || RIGHT(val, 4)
  END;

Row Access Policies

-- Create row access policy
CREATE OR REPLACE ROW ACCESS POLICY region_policy AS (region_col VARCHAR)
RETURNS BOOLEAN ->
  CASE
    WHEN CURRENT_ROLE() = 'ADMIN' THEN TRUE
    WHEN CURRENT_ROLE() = 'REGION_MANAGER' THEN
      region_col = CURRENT_REGION()
    ELSE FALSE
  END;

-- Apply row access policy
ALTER TABLE sales ADD ROW ACCESS POLICY region_policy ON (region);

Encryption

At-Rest Encryption

-- Snowflake automatically encrypts all data at rest
-- No additional configuration needed

-- Check encryption status
SELECT
  table_name,
  encryption_type,
  encryption_key_id
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_name = 'CUSTOMERS';

In-Transit Encryption

-- All data in transit is encrypted via TLS 1.2+
-- Enforce TLS version
ALTER ACCOUNT SET NETWORK_POLICY = 'STRICT_TLS_POLICY';

-- Create network policy
CREATE NETWORK POLICY strict_tls_policy
  ALLOWED_IP_LIST = ('10.0.0.0/8', '192.168.0.0/16')
  BLOCKED_IP_LIST = ('0.0.0.0/0')
  COMMENT = 'Strict TLS enforcement policy';

Data Classification

Automatic Classification

-- Enable automatic classification
ALTER ACCOUNT SET ENABLE_AUTOMATIC_CLASSIFICATION = TRUE;

-- View classification results
SELECT
  table_name,
  column_name,
  data_type,
  semantic_category,
  sensitivity_level
FROM INFORMATION_SCHEMA.CLASSIFICATION
WHERE sensitivity_level IN ('HIGH', 'CRITICAL');

-- Manual classification
ALTER TABLE customers MODIFY COLUMN ssn
  SET TAG 'PII' = 'SSN', 'SENSITIVITY' = 'CRITICAL';

Tag-Based Security

-- Create security tags
CREATE TAG security.pii_tag;
CREATE TAG security.confidentiality_tag;

-- Apply tags to columns
ALTER TABLE customers MODIFY COLUMN email
  SET TAG security.pii_tag = 'EMAIL';

ALTER TABLE customers MODIFY COLUMN ssn
  SET TAG security.pii_tag = 'SSN';

-- Query with tag-based policies
SELECT * FROM customers
  WHERE TAG security.pii_tag = 'EMAIL';

Network Security

Private Connectivity

-- Enable AWS PrivateLink
ALTER ACCOUNT SET ENABLE_PRIVATELINK = TRUE;

-- Configure network rules
CREATE NETWORK RULE private_network
  MODE = INGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('10.0.0.0/8');

-- Create access integration
CREATE NETWORK INTEGRATION private_access
  ENABLED = TRUE
  ALLOWED_IP_LIST = ('10.0.0.0/8')
  BLOCKED_IP_LIST = ('0.0.0.0/0');

IP Whitelisting

-- Create IP whitelist policy
CREATE NETWORK POLICY ip_whitelist
  ALLOWED_IP_LIST = (
    '203.0.113.0/24',    -- Corporate network
    '198.51.100.0/24'    -- VPN range
  )
  COMMENT = 'IP whitelist for corporate access';

-- Apply to account
ALTER ACCOUNT SET NETWORK_POLICY = 'IP_WHITELIST';

Monitoring and Auditing

-- Enable audit logging
ALTER ACCOUNT SET ENABLE_AUDIT_LOGGING = TRUE;

-- Query audit logs
SELECT
  event_id,
  event_type,
  user_name,
  client_ip,
  event_timestamp
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

-- Monitor privileged actions
SELECT
  query_id,
  query_text,
  user_name,
  role_name,
  start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE role_name = 'ACCOUNTADMIN'
  AND start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP());

Security Best Practices Summary