snowflake-policy-guardrails

Snowflake Policy & Guardrails

Overview

Automated policy enforcement and governance guardrails using Snowflake-native features: network rules, authentication policies, session policies, and object-level governance.

Instructions

Step 1: Network Rules and Policies

-- Network rules (more granular than legacy network policies)
CREATE OR REPLACE NETWORK RULE corp_vpn_rule
  TYPE = IPV4
  MODE = INGRESS
  VALUE_LIST = ('203.0.113.0/24', '198.51.100.0/24');

CREATE OR REPLACE NETWORK RULE cloud_services_rule
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('api.company.com:443', 'events.company.com:443');

-- Create network policy using rules
CREATE OR REPLACE NETWORK POLICY prod_network_policy
  ALLOWED_NETWORK_RULE_LIST = (corp_vpn_rule)
  BLOCKED_NETWORK_RULE_LIST = ();

-- Apply at account level
ALTER ACCOUNT SET NETWORK_POLICY = prod_network_policy;

-- Or per-user (service accounts can have different rules)
ALTER USER svc_etl SET NETWORK_POLICY = prod_network_policy;

Step 2: Authentication Policies

-- Require MFA for interactive users
CREATE OR REPLACE AUTHENTICATION POLICY interactive_auth
  MFA_AUTHENTICATION_METHODS = ('TOTP')
  CLIENT_TYPES = ('SNOWFLAKE_UI', 'SNOWSQL')
  SECURITY_INTEGRATIONS = ('saml_sso');

-- Service accounts: key pair only, no password
CREATE OR REPLACE AUTHENTICATION POLICY service_auth
  AUTHENTICATION_METHODS = ('KEYPAIR')
  CLIENT_TYPES = ('SNOWFLAKE_DRIVER')
  MFA_AUTHENTICATION_METHODS = ();

-- Apply policies
ALTER USER analyst_user SET AUTHENTICATION POLICY = interactive_auth;
ALTER USER svc_etl SET AUTHENTICATION POLICY = service_auth;

Step 3: Session Policies

-- Enforce session timeout and idle limits
CREATE OR REPLACE SESSION POLICY prod_session_policy
  SESSION_IDLE_TIMEOUT_MINS = 30
  SESSION_UI_IDLE_TIMEOUT_MINS = 15;

-- Apply to account
ALTER ACCOUNT SET SESSION POLICY = prod_session_policy;

Step 4: Statement-Level Guardrails

-- Prevent runaway queries
ALTER WAREHOUSE PROD_WH SET
  STATEMENT_TIMEOUT_IN_SECONDS = 3600,          -- 1 hour max
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600;     -- 10 min max queue

-- Prevent accidental full table operations
-- Use row access policies + stored procedures instead of raw access

-- Example: Safe delete procedure with audit
CREATE OR REPLACE PROCEDURE safe_delete(
  table_name VARCHAR, where_clause VARCHAR, max_rows INTEGER DEFAULT 10000
)
  RETURNS VARCHAR
  LANGUAGE SQL
AS
$$
BEGIN
  -- Count affected rows first
  LET count_sql VARCHAR := 'SELECT COUNT(*) FROM ' || :table_name || ' WHERE ' || :where_clause;
  LET affected_rows INTEGER;
  EXECUTE IMMEDIATE :count_sql INTO :affected_rows;

  IF (:affected_rows > :max_rows) THEN
    RETURN 'BLOCKED: Would delete ' || :affected_rows || ' rows (max: ' || :max_rows || ')';
  END IF;

  -- Audit log
  INSERT INTO audit.delete_log (table_name, where_clause, row_count, executed_by, executed_at)
  VALUES (:table_name, :where_clause, :affected_rows, CURRENT_USER(), CURRENT_TIMESTAMP());

  -- Execute delete
  EXECUTE IMMEDIATE 'DELETE FROM ' || :table_name || ' WHERE ' || :where_clause;
  RETURN 'Deleted ' || :affected_rows || ' rows from ' || :table_name;
END;
$$;

-- Usage: CALL safe_delete('orders', 'order_date < ''2024-01-01''', 50000);

Step 5: Data Governance Tags and Policies

-- Create governance taxonomy
CREATE TAG IF NOT EXISTS data_domain ALLOWED_VALUES 'finance', 'marketing', 'engineering', 'hr';
CREATE TAG IF NOT EXISTS data_owner;
CREATE TAG IF NOT EXISTS retention_days;

-- Apply tags to databases/schemas
ALTER DATABASE PROD_DW SET TAG data_domain = 'finance';
ALTER SCHEMA PROD_DW.GOLD SET TAG data_owner = 'analytics-team@company.com';
ALTER TABLE PROD_DW.GOLD.REVENUE SET TAG retention_days = '2555';  -- 7 years

-- Automated compliance report
SELECT
  tag_name, tag_value, object_database, object_schema, object_name, column_name
FROM TABLE(INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS(
  'PROD_DW.GOLD.REVENUE', 'TABLE'
));

-- Find untagged tables (governance gap)
SELECT t.table_catalog, t.table_schema, t.table_name, t.row_count
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN TABLE(INFORMATION_SCHEMA.TAG_REFERENCES(
  t.table_catalog || '.' || t.table_schema || '.' || t.table_name, 'TABLE'
)) tr ON TRUE
WHERE tr.tag_name IS NULL
  AND t.table_schema NOT IN ('INFORMATION_SCHEMA')
ORDER BY t.row_count DESC NULLS LAST;

Step 6: CI/CD Policy Checks

# .github/workflows/snowflake-governance.yml
name: Snowflake Governance Check

on:
  pull_request:
    paths: ['sql/**', 'migrations/**']

jobs:
  policy-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Check for dangerous SQL patterns
        run: |
          # No DROP DATABASE/SCHEMA without IF EXISTS
          if grep -rn "DROP DATABASE\|DROP SCHEMA" sql/ | grep -v "IF EXISTS"; then
            echo "ERROR: DROP without IF EXISTS detected"
            exit 1
          fi

          # No GRANT ... TO PUBLIC
          if grep -rn "TO ROLE PUBLIC\|TO PUBLIC" sql/; then
            echo "ERROR: Granting to PUBLIC role is not allowed"
            exit 1
          fi

          # No hardcoded passwords
          if grep -rn "PASSWORD = " sql/ | grep -v "PASSWORD = \$"; then
            echo "ERROR: Hardcoded password detected"
            exit 1
          fi

          # All tables must have DATA_RETENTION_TIME_IN_DAYS
          for f in $(grep -rl "CREATE TABLE\|CREATE OR REPLACE TABLE" sql/); do
            if ! grep -q "DATA_RETENTION_TIME_IN_DAYS" "$f"; then
              echo "WARNING: $f missing explicit retention policy"
            fi
          done

          echo "All governance checks passed"

      - name: Validate SchemaChange naming
        run: |
          # Ensure migration files follow V{version}__{description}.sql
          for f in migrations/V*.sql; do
            if ! echo "$f" | grep -qE 'V[0-9]+\.[0-9]+\.[0-9]+__[a-z_]+\.sql'; then
              echo "ERROR: Invalid migration filename: $f"
              echo "Expected: V{major}.{minor}.{patch}__{description}.sql"
              exit 1
            fi
          done

Step 7: Automated Compliance Audit

-- Weekly compliance audit stored procedure
CREATE OR REPLACE PROCEDURE run_compliance_audit()
  RETURNS TABLE (check_name VARCHAR, status VARCHAR, details VARCHAR)
  LANGUAGE SQL
AS
$$
  -- Check 1: No users with ACCOUNTADMIN default role
  SELECT 'accountadmin_check' AS check_name,
    CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
    COUNT(*) || ' users with ACCOUNTADMIN default' AS details
  FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
  WHERE default_role = 'ACCOUNTADMIN' AND disabled = 'false'

  UNION ALL

  -- Check 2: Network policy active
  SELECT 'network_policy_check',
    CASE WHEN value != '' THEN 'PASS' ELSE 'FAIL' END,
    'Account network policy: ' || COALESCE(value, 'NONE')
  FROM TABLE(FLATTEN(INPUT => PARSE_JSON(
    SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO()
  )))
  WHERE key = 'network_policy'

  UNION ALL

  -- Check 3: MFA adoption
  SELECT 'mfa_check',
    CASE WHEN COUNT_IF(has_mfa = 'true') * 100 / COUNT(*) >= 90 THEN 'PASS' ELSE 'WARN' END,
    COUNT_IF(has_mfa = 'true') || '/' || COUNT(*) || ' users have MFA'
  FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
  WHERE disabled = 'false';
$$;

Error Handling

Issue	Cause	Solution
Network policy blocks legitimate user	IP not in allowlist	Add IP range to network rule
Auth policy prevents login	Wrong client type in policy	Adjust CLIENT_TYPES
Session timeout too aggressive	Short idle timeout	Increase SESSION_IDLE_TIMEOUT_MINS
CI check false positive	SQL pattern too broad	Refine regex pattern

Resources

• Network Policies
• Authentication Policies
• Data Governance
• Object Tagging

Next Steps

For architecture blueprints, see snowflake-architecture-variants.