Snowflake & data warehouses #

UNC5537 Snowflake campaign (Apr-Jun 2024) hit 165+ orgs (Ticketmaster, Santander, LendingTree, Advance Auto Parts, AT&T etc.). TTPs:

  • Credentials sourced from infostealer logs (Lumma, RedLine, Vidar, Raccoon) going back years
  • Targets lacked MFA on Snowflake accounts (service accounts especially)
  • No network policy restricting IP; attacker connected from anywhere
  • Tool: rapeflake / FROSTBITE for recon

Key tables / views

Snowflake timestamps 101: ACCOUNT_USAGE views are TIMESTAMP_LTZ columns (session local time zone) - your query's TZ context defines the displayed value, but underlying storage is UTC. Latency into ACCOUNT_USAGE is officially up to 45 min, up to 180 min for a few views - do NOT trust ACCOUNT_USAGE for last-45-min incident timelines; use INFORMATION_SCHEMA table functions (near-real-time, 7-day retention) for the hot window and ACCOUNT_USAGE for historical. Retention: ACCOUNT_USAGE = 365 days for most views; LOGIN_HISTORY = 365 d; QUERY_HISTORY = 365 d.

  • SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY - 365d retention; CLIENT_IP, REPORTED_CLIENT_TYPE, FIRST_AUTHENTICATION_FACTOR, SECOND_AUTHENTICATION_FACTOR (null = no MFA). ts: EVENT_TIMESTAMP (TIMESTAMP_LTZ). Up to ~2 h ingestion lag.
  • …ACCOUNT_USAGE.SESSIONS - session metadata, client env. ts: CREATED_ON session start.
  • …ACCOUNT_USAGE.QUERY_HISTORY - every SQL run with USER_NAME, ROLE_NAME, EXECUTION_STATUS, WAREHOUSE_SIZE, BYTES_SCANNED, ROWS_PRODUCED, text. ts: START_TIME, END_TIME, QUERY_LOAD_PERCENT, TOTAL_ELAPSED_TIME (ms). START_TIME is query queue time; use EXECUTION_TIME for actual run.
  • …ACCOUNT_USAGE.ACCESS_HISTORY - object-level access + direct/base object lineage; find bulk SELECTs on PII tables. ts: QUERY_START_TIME; up to 180 min ingestion lag.
  • …ACCOUNT_USAGE.GRANTS_TO_ROLES, GRANTS_TO_USERS - privilege timeline. ts: CREATED_ON, DELETED_ON.
  • …ACCOUNT_USAGE.STAGES - external stages used for exfil (S3/GCS/Azure); check for new stages to attacker-owned buckets, COPY INTO @stage FROM table. ts: CREATED, LAST_ALTERED, DELETED.

What to lock down

  • Enforce MFA (per-user and policy-level); network policies restricting IP; disable password-auth where possible (keypair/OAuth)
  • Rotate all SCIM tokens and PATs
  • Review EXTERNAL_FUNCTIONS, EXTERNAL_STAGES, EXTERNAL_ACCESS_INTEGRATIONS for egress
  • Audit CREATE SHARE and reader accounts - cross-tenant data sharing

Also for other data platforms

  • Databricks - audit logs delivery to cloud bucket; service principal abuse; cluster init scripts persistence
  • BigQuery - Cloud Audit Logs; dataset ACL changes; export jobs
  • Redshift - stl_connection_log, stl_userlog, CloudTrail data events; UNLOAD to S3
Snowflake IR tooling: snowsql (CLI; query ACCOUNT_USAGE views directly), Snowsight worksheets, Permiso Security P0 (Snowflake-focused IR SaaS), Grimoire Snowflake playbooks, sfQuery (community), SnowAlert (legacy Snowflake-native alerting), Mandiant's published Snowflake hunt queries (M-Trends 2024 appendix), Hunting-Queries-Detection-Rules (Microsoft repo) has a Snowflake pack. For Databricks: Databricks CLI, system.access.audit table. BigQuery: bq CLI, Cloud Logging cloudaudit.googleapis.com, Chronicle.