CW

Snowflake Access History and Auditing

Free Lesson

Advertisement

Snowflake Access History and Auditing

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

Architecture Overview

<svg width="800" height="450" viewBox="0 0 800 450" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="auditGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#E74C3C;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#EC7063;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Snowflake Access History & Auditing</text>
  <rect x="30" y="60" width="740" height="100" rx="10" fill="url(#auditGrad)" opacity="0.9"/>
  <text x="400" y="85" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Access Tracking Layer</text>

  <rect x="50" y="95" width="120" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="110" y="122" text-anchor="middle" font-size="10" fill="#333">Direct Access</text>
  <rect x="190" y="95" width="120" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="250" y="122" text-anchor="middle" font-size="10" fill="#333">View Access</text>
  <rect x="330" y="95" width="120" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="390" y="122" text-anchor="middle" font-size="10" fill="#333">Share Access</text>
  <rect x="470" y="95" width="120" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="530" y="122" text-anchor="middle" font-size="10" fill="#333">Export Access</text>
  <rect x="610" y="95" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="680" y="122" text-anchor="middle" font-size="10" fill="#333">Masked Access</text>
  <rect x="30" y="180" width="740" height="120" rx="10" fill="#3498DB" opacity="0.9"/>
  <text x="400" y="205" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Audit Metadata Captured</text>

  <rect x="50" y="220" width="130" height="65" rx="8" fill="white" opacity="0.9"/>
  <text x="115" y="245" text-anchor="middle" font-size="10" fill="#333">User Identity</text>
  <text x="115" y="260" text-anchor="middle" font-size="9" fill="#666">role, IP, time</text>
  <text x="115" y="275" text-anchor="middle" font-size="9" fill="#666">session info</text>

  <rect x="200" y="220" width="130" height="65" rx="8" fill="white" opacity="0.9"/>
  <text x="265" y="245" text-anchor="middle" font-size="10" fill="#333">Object Access</text>
  <text x="265" y="260" text-anchor="middle" font-size="9" fill="#666">tables, views</text>
  <text x="265" y="275" text-anchor="middle" font-size="9" fill="#666">columns, rows</text>

  <rect x="350" y="220" width="130" height="65" rx="8" fill="white" opacity="0.9"/>
  <text x="415" y="245" text-anchor="middle" font-size="10" fill="#333">Query Details</text>
  <text x="415" y="260" text-anchor="middle" font-size="9" fill="#666">text, filters</text>
  <text x="415" y="275" text-anchor="middle" font-size="9" fill="#666">joins, aggregations</text>

  <rect x="500" y="220" width="130" height="65" rx="8" fill="white" opacity="0.9"/>
  <text x="565" y="245" text-anchor="middle" font-size="10" fill="#333">Data Movement</text>
  <text x="565" y="260" text-anchor="middle" font-size="9" fill="#666">exports, copies</text>
  <text x="565" y="275" text-anchor="middle" font-size="9" fill="#666">downloads</text>

  <rect x="650" y="220" width="110" height="65" rx="8" fill="white" opacity="0.9"/>
  <text x="705" y="245" text-anchor="middle" font-size="10" fill="#333">Security</text>
  <text x="705" y="260" text-anchor="middle" font-size="9" fill="#666">privileges</text>
  <text x="705" y="275" text-anchor="middle" font-size="9" fill="#666">masking</text>
  <rect x="30" y="320" width="240" height="110" rx="10" fill="#27AE60" opacity="0.85"/>
  <text x="150" y="345" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Compliance Standards</text>
  <text x="150" y="365" text-anchor="middle" font-size="10" fill="white">SOC 2 Type II</text>
  <text x="150" y="380" text-anchor="middle" font-size="10" fill="white">HIPAA</text>
  <text x="150" y="395" text-anchor="middle" font-size="10" fill="white">GDPR</text>
  <text x="150" y="410" text-anchor="middle" font-size="10" fill="white">PCI DSS</text>

  <rect x="290" y="320" width="240" height="110" rx="10" fill="#9B59B6" opacity="0.85"/>
  <text x="410" y="345" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Use Cases</text>
  <text x="410" y="365" text-anchor="middle" font-size="10" fill="white">Data privacy auditing</text>
  <text x="410" y="380" text-anchor="middle" font-size="10" fill="white">Security incident response</text>
  <text x="410" y="395" text-anchor="middle" font-size="10" fill="white">Access review workflows</text>
  <text x="410" y="410" text-anchor="middle" font-size="10" fill="white">Regulatory reporting</text>

  <rect x="550" y="320" width="220" height="110" rx="10" fill="#F39C12" opacity="0.85"/>
  <text x="660" y="345" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Retention</text>
  <text x="660" y="365" text-anchor="middle" font-size="10" fill="white">Enterprise: 365 days</text>
  <text x="660" y="380" text-anchor="middle" font-size="10" fill="white">Business: 180 days</text>
  <text x="660" y="395" text-anchor="middle" font-size="10" fill="white">Standard: 90 days</text>
  <text x="660" y="410" text-anchor="middle" font-size="10" fill="white">Configurable retention</text>
</svg>

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 history is automatically enabled and cannot be disabled. For enhanced privacy, consider using anonymization functions to mask user identities in audit reports while maintaining audit trails for compliance.

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;
  • Access history tracks all query access at table and column levels
  • Data is retained for 90-365 days depending on edition
  • Compliance reports support SOC 2, HIPAA, GDPR, PCI DSS
  • Column-level access tracking enables sensitive data monitoring
  • Export monitoring prevents unauthorized data exfiltration

Advertisement

←41 Snowflake Query History43 Snowflake Data Lineage→

Need Expert Snowflake Help?

Get personalized warehouse optimization, data modeling, or Snowflake platform consulting.

Contact Us →View Services

Advertisement