Snowflake Access History and Auditing

Snowflake provides detailed access history tracking for compliance, security auditing, and data governance requirements.

What is Access History?

• Tracks all query access at table and column levels
• Supports compliance standards: SOC 2, HIPAA, GDPR, PCI DSS
• Retains data for 90–365 days depending on edition

Architecture Overview

The access history and auditing architecture includes five components:

1. Access Tracking Layer — Direct access, view access, share access, export access, masked access
2. Audit Metadata Captured — User identity, object access, query details, data movement, security
3. Compliance Standards — SOC 2 Type II, HIPAA, GDPR, PCI DSS
4. Use Cases — Data privacy auditing, security incident response, access review workflows, regulatory reporting
5. Retention Periods

Access History Tables

Query Access History

-- All queries accessing a specific table
SELECT
  query_id,
  query_text,
  user_name,
  role_name,
  start_time,
  direct_objects_accessed,
  objects_modified
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
WHERE direct_objects_accessed[0]:objectDomain::STRING = 'TABLE'
  AND direct_objects_accessed[0]:refs[0]:objectName::STRING = 'SENSITIVE_DATA'
ORDER BY start_time DESC;

Column-Level Access

-- Track column-level access
SELECT
  query_id,
  user_name,
  columns_accessed
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE columns_accessed IS NOT NULL
ORDER BY start_time DESC;

Compliance Queries

Sensitive Data Access Report

-- Find access to sensitive columns
WITH sensitive_columns AS (
  SELECT 'EMAIL' as column_name UNION ALL
  SELECT 'PHONE' UNION ALL
  SELECT 'SSN' UNION ALL
  SELECT 'CREDIT_CARD' UNION ALL
  SELECT 'BIRTH_DATE'
)
SELECT
  ah.query_id,
  ah.user_name,
  ah.start_time,
  ah.query_text,
  ca.value:objectName::STRING as table_name,
  ca.value:columnName::STRING as column_name
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
)) ah,
LATERAL FLATTEN(input => ah.columns_accessed) ca
WHERE ca.value:columnName::STRING IN (SELECT column_name FROM sensitive_columns)
ORDER BY ah.start_time DESC;

User Access Summary

-- User access patterns
SELECT
  user_name,
  COUNT(*) as total_queries,
  COUNT(DISTINCT objects_accessed) as unique_tables,
  SUM(CASE WHEN columns_accessed IS NOT NULL THEN 1 ELSE 0 END) as column_level_queries,
  MIN(start_time) as first_access,
  MAX(start_time) as last_access
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY user_name
ORDER BY total_queries DESC;

Data Export Monitoring

-- Track data exports
SELECT
  query_id,
  user_name,
  query_text,
  start_time,
  bytes_scanned
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE query_text LIKE '%COPY INTO%'
  OR query_text LIKE '%GET%'
  OR query_text LIKE '%LIST%'
ORDER BY start_time DESC;

Access Control Review

-- Privilege usage analysis
SELECT
  grantee_name,
  privilege,
  grant_type,
  created_on,
  table_name
FROM TABLE(INFORMATION_SCHEMA.GRANTS_TO_USERS(
  START_TIME => DATEADD(day, -90, CURRENT_TIMESTAMP())
))
WHERE privilege IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE')
ORDER BY created_on DESC;

-- Role hierarchy analysis
SELECT
  grantee_name as role_name,
  granted_to,
  granted_role
FROM TABLE(INFORMATION_SCHEMA.GRANTS_TO_ROLES())
WHERE granted_to = 'ROLE'
ORDER BY grantee_name;