Frosty Trails: Threat-Hunting for Identity Threats in Snowflake

The Snowflake platform has revolutionized how organizations store, process, and analyze large volumes of data. It offers a fully-managed data warehouse-as-a-service solution, providing a scalable and flexible architecture allowing seamless data integration from multiple sources. 

As Snowflake rises in popularity as one of the top ten cloud vendors in the world, its attractiveness to organizations also draws the attention of malicious attackers. The platform’s widespread adoption and extensive use in storing valuable data make it a lucrative target for cyber threats. As a result, you must implement security measures, stay vigilant against emerging threats, and continuously update your defense mechanisms to safeguard Snowflake data sharing and infrastructure from potential attackers.

This post will help you grasp how to use Snowflake’s built-in logging features for your security operation routine. We will explore the relevant data Snowflake exposes for hunting and describe ten threat scenarios and how to detect them. We will also share a script to execute them and perform quick threat-hunting operations in your environment to stay secure and audit-ready.

What is Snowflake?

Snowflake is a cloud-based data platform that provides a fully managed and scalable solution for storing, processing, and analyzing large volumes of data. It is designed to work on top of popular cloud providers like Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). Snowflake is built with a unique architecture that separates storage and computing, allowing identities to scale resources independently based on their needs. 

This architecture, combined with its ability to process semi-structured and structured data, enables Snowflake to deliver high performance and cost-efficiency for data workloads of any size. As we’ll see in the next section, a Snowflake account can hold multiple databases without taking care of the infrastructure.

Key Snowflake Logging Features

Each Snowflake account has a default database called “SNOWFLAKE”. It is a shared read-only database that holds metadata and historical usage data related to the objects within your organization and accounts.  

The SNOWFLAKE database has a built-in schema called “ACCOUNT_USAGE“, which is a system-defined schema containing a set of views providing access to comprehensive and granular usage information for the Snowflake account. It is a valuable tool for monitoring and understanding how resources are utilized within your Snowflake environment. 

The schema includes views that cover 

  • user activity
  •  query history
  •  warehouse usage
  •  login history
  •  data transfer details, and more.  

There are more logging mechanisms in Snowflake, such as INFORMATION_SCHEMA and READER_ACCOUNT_USAGE. During this post, we will rely on the following ACCOUNT_USAGE views:

Exploring ACCOUNT_USAGE

By default, each Snowflake account has a database called SNOWFLAKE that is accessible to the ACCOUNTADMIN role. You can grant additional roles and have access through the following command:

GRANT imported privileges on database snowflake to role rezonate_integration; 

You can explore the available views by logging in as an ACCOUNTADMIN to your Snowflake account and performing the following steps:

  1. From the left pane, choose Data and then Databases.
  2. Select the SNOWFLAKE database and expand it.

Snowflake's ACCOUNT_USAGE schema

3. Expand ACCOUNT_USAGE and select any of the views within it.

Each available view in the schema has its own column structure. You can see the available columns for each view by clicking on the view name, choosing the Columns tab, and selecting “Explore available columns”.

Comprehensive documentation per view is available, including the retention period and logging latency.

Snowflake Data Governance – How to Access Snowflake Audit Logs

The Snowflake logs are accessible through a few methods, as you’ll see below.

1. Snowflake Console 

The most straightforward method of accessing the logs is logging in to a Snowflake account with a user that has read permissions to the  ACCOUNT_USAGE schema. Then, choose “Worksheets” from the left pane and ensure the worksheet is querying the correct data source. You should see something like the query browser below.

2. SnowSQL

SnowSQL is a command-line tool provided by Snowflake designed to interact with Snowflake’s data warehouse and execute SQL queries, manage data, and perform various administrative tasks. It acts as the official command-line client for Snowflake, allowing users to connect to their Snowflake accounts and work with data using SQL commands. Information about installing, configuring, and using it is available in Snowflake’s documentation.

3. Exporting to external storage

Snowflake facilitates data export to contemporary storage services like AWS S3 through  “Stage” functionality. The data exported from Snowflake can be saved in various file formats. You can find detailed information about Stages on Snowflake’s official documentation page

Once the Stage setup is complete and data is exported, you have the flexibility to utilize your preferred analysis tool to centralize the data in a location of your choice.

4. Snowflake SDKs

As well as the structured methods mentioned earlier, Snowflake supports native REST API accessible through different SDKs. It can be used by any script or tool for purposes like exporting data. An example is the Rezonate Threat-Hunting Tool, which takes advantage of Snowflake Python SDK to execute threat-hunting queries. We’ll find out more later in the blog. 

Besides the Python SDK, the Snowflake team has developed drivers for many popular languages, including .NET, NodeJS, and Go. The full list is available here.

10 Snowflake Threat-Hunting Techniques to Implement Now 

Now we’ve learned about Snowflake’s structure, basic permissions, and integrations, we can start threat-hunting. In this section, we will guide you through some critical threat-hunting scenarios to look out for and explain each. We will also mark the relevant Snowflake views, align them to the specific MITRE ATT&CK technique, and include our own query in Snowflake query syntax. Remember, you can copy and paste them directly to your worksheet.

It is important to highlight that some hunting queries may have false positives, depending on the environment and may need adjustments to reduce noisy results.

Scenario 1 – Brute Force on a Snowflake User

A brute force attack on a Snowflake user happens when an attacker uses trial-and-error to repeatedly submit different combinations of usernames and passwords and eventually gain unauthorized access. To hunt for this type of attack, you can search for an attacker that performed more than X failed login attempts on at least Y target users, failing or ending up with a successful login. In failure cases, the activity may result in a user’s lockout.

Relevant Snowflake View

Query

-- Get users who failed to login from the same IP address at least 5 times

select CLIENT_IP, USER_NAME, REPORTED_CLIENT_TYPE, count(*) as FAILED_ATTEMPTS, min(EVENT_TIMESTAMP) as FIRST_EVENT, max(EVENT_TIMESTAMP) as LAST_EVENT

from SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

where IS_SUCCESS = 'NO' and ERROR_MESSAGE in ('INCORRECT_USERNAME_PASSWORD', 'USER_LOCKED_TEMP') and FIRST_AUTHENTICATION_FACTOR='PASSWORD' and

      EVENT_TIMESTAMP >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

group by 1,2,3

having FAILED_ATTEMPTS >= 5

order by 4 desc;

-- For Each result, check if the source IP address managed to login to the target user AFTER the "lastEvent" time

MITRE Technique

Scenario 2 – Password Spray on a Snowflake Account

A brute force attack on a Snowflake account involves an attacker repeatedly submitting different combinations of usernames and passwords to eventually manage to log in and gain unauthorized access. To hunt for any occurrence of this scenario, you can search for an attacker that performed more than 1 failed login attempt on at least Y unique target users, from the same IP address.

Relevant Snowflake View

Query

-- Get users who failed to login from the same IP address at least 5 times

select CLIENT_IP, REPORTED_CLIENT_TYPE, count(distinct USER_NAME) as UNIQUE_USER_COUNT, min(EVENT_TIMESTAMP) as FIRST_EVENT, max(EVENT_TIMESTAMP) as LAST_EVENT

from SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

where IS_SUCCESS = 'NO' and ERROR_MESSAGE in ('INCORRECT_USERNAME_PASSWORD', 'USER_LOCKED_TEMP') and FIRST_AUTHENTICATION_FACTOR='PASSWORD' and

      EVENT_TIMESTAMP >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

group by 1,2

having UNIQUE_USER_COUNT >= 1

order by 3 desc;

-- For Each result, check if the source IP address managed to login to the target user AFTER the "lastEvent" time

MITRE Technique

Scenario 3 – Unauthorized Login Attempt to a Disabled/Inactive User

 In some cases, Snowflake user accounts might have been disabled due to security concerns or maybe even as part of employee off-boarding. Monitoring login attempts to disabled users can help you detect unauthorized activities.

Relevant Snowflake View

Query

-- Search for login attempts to disabled users

select *

from SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

where IS_SUCCESS = 'NO' and  ERROR_MESSAGE  = 'USER_ACCESS_DISABLED'

MITRE Technique

Scenario 4 – Login Attempt Blocked by Network Policy

Snowflake network policies are a set of rules that govern network communication and access control within the Snowflake data platform. A network policy can deny a connection based on the client’s characteristics, such as IP address, to enforce organization policy and reduce the chances of a compromised account in case of leaked credentials.
By searching for these failed logins we can identify violations of the organizational policies that may suggest compromised credentials.

Relevant Snowflake View

Query

-- Search for network policies blocked IP addresses

select *

from SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

where IS_SUCCESS = 'NO' and  ERROR_MESSAGE  = 'INCOMING_IP_BLOCKED' and EVENT_TIMESTAMP >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

Scenario 5 – Exfiltration Through Snowflake Data Sharing

Snowflake administrators can share data stored in their accounts with other Snowflake accounts. An attacker might use shares to exfiltrate data from Snowflake resources stored on compromised accounts to external locations. Any unauthorized event of this nature is a big red flag.

Relevant Snowflake View

Query

-- Search for new data shares

select * 

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 

where REGEXP_LIKE(QUERY_TEXT, 'create\\s+share\\s.*','i') or REGEXP_LIKE(QUERY_TEXT, '\\s+to\\s+share\\s.*','i')

and START_TIME>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

  • Exfiltration | Transfer Data to Cloud Account| ATT&CK T1537 

Scenario 6 – Exfiltration Through Snowflake Stage

A Snowflake stage is an external storage location that serves as an intermediary for loading or unloading data into or from Snowflake, providing seamless integration with various cloud-based storage services. For example, an AWS S3 bucket can serve as a stage. You can use the following queries to search potential data exfiltration using this feature. 

Relevant Snowflake View

Query

-- Search for stage-related statements

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

where QUERY_TEXT ilike '%COPY INTO%'

and QUERY_TEXT ilike '%@%';

-- The following query will show the stages that were created in the last 24 hours
select * from SNOWFLAKE.ACCOUNT_USAGE.STAGES

where CREATED>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

  • Exfiltration | Transfer Data to Cloud Account| ATT&CK T1537 

Scenario 7 – Persistency Through Snowflake Procedures & Tasks

Procedures and tasks are Snowflake features that automate and manage workflows.

  1. Snowflake Procedures: Procedures in Snowflake are user-defined scripts written in SQL or JavaScript that allow you to encapsulate a series of SQL or JavaScript statements as a reusable unit.
  2. Snowflake Tasks: Tasks are scheduled operations that automate repetitive tasks or workflows. They are defined using SQL or JavaScript and can include SQL queries, DML statements, or calls to procedures. Tasks are scheduled to run at specific intervals, such as hourly, daily, or weekly, making them ideal for automating data pipelines and regular data processing.

An attacker might utilize procedures and tasks to maintain persistently in the organization or exfiltrate data over time.

Relevant Snowflake View

Query

-- Search for new tasks

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 

where REGEXP_LIKE(QUERY_TEXT, '.*CREATE\\s+(OR\\s+REPLACE\\s+)?TASK.*', 'i')

and START_TIME >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

-- Search for new procedures

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 

where REGEXP_LIKE(QUERY_TEXT, '.*CREATE\\s+(OR\\s+REPLACE\\s+)?PROCEDURE.*', 'i')

and START_TIME >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

Scenario 8 – Defense Evasion Through Unset Masking Policy

A Snowflake masking policy is a security mechanism that protects sensitive data within a database. It allows you to define rules for obscuring or redacting specific data elements, such as Social Security Numbers (SSNs) or credit card numbers, to limit their visibility to unauthorized users. Attackers might bypass masking policies by unsetting them, given the right permission, and then exfiltrating sensitive information.

Relevant Snowflake View

Query

-- Search for unsetting of a masking policy

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

where QUERY_TEXT ilike '%UNSET MASKING POLICY%'

and START_TIME >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

  • Data Manipulation| Stored Data Manipulation | ATT&CK T1565 

Scenario 9 – Data Exfiltration: Spikes in User Queries Volume

If an attacker manages to infiltrate a Snowflake account, they may attempt to extract data from the databases hosted in the compromised account. To detect this type of activity, you can identify users who exhibit significantly higher data querying rates than their typical usage patterns. The subsequent query lets us pinpoint users who have executed queries resulting in larger data volumes than their average daily activity over the previous week. 

Triage tip: The suspicion level increases as the difference between the calculated standard deviation of “total_bytes_written” and the sum of “stddev_daily_bytes” and “avg_daily_bytes” grows larger.

Relevant Snowflake View

Query

-- Spikes in user queries

WITH user_daily_bytes AS (

  SELECT

    USER_NAME AS user_name,

    DATE_TRUNC('DAY', END_TIME) AS query_date,

    SUM(BYTES_WRITTEN_TO_RESULT) AS total_bytes_written

  FROM ACCOUNT_USAGE.QUERY_HISTORY

  WHERE END_TIME >= CURRENT_TIMESTAMP() - INTERVAL '7 DAY'

  GROUP BY user_name, query_date

),

user_daily_average AS (

  SELECT

    user_name,

    AVG(total_bytes_written) AS avg_bytes_written,

    STDDEV_SAMP(total_bytes_written) AS stddev_bytes_written

  FROM user_daily_bytes

  GROUP BY user_name

)

SELECT

  u.user_name,

  ROUND(u.total_bytes_written, 2) AS today_bytes_written,

  ROUND(a.avg_bytes_written, 2) AS avg_daily_bytes,

  ROUND(a.stddev_bytes_written, 2) AS stddev_daily_bytes

FROM user_daily_bytes u

JOIN user_daily_average a 

  ON u.user_name = a.user_name

WHERE query_date = CURRENT_DATE()

  AND u.total_bytes_written > a.avg_bytes_written

  AND u.total_bytes_written > stddev_daily_bytes + avg_daily_bytes

ORDER BY u.user_name;

MITRE Technique

Scenario 10 – Anomaly in Client Application For User

If a user’s credentials are compromised or there is an insider threat, the attacker may attempt to use enumeration tools or client apps to perform massive data exfiltration. It’s likely that these tools haven’t been used by the legitimate user in the past. For this case, detecting any new client app used by the user could be a red flag that is worth investigating.

Relevant Snowflake View

Query

-- User uses a new client application

WITH user_previous_applications AS (

  SELECT

    USER_NAME AS user_name,

    ARRAY_AGG(DISTINCT CLIENT_APPLICATION_ID) AS previous_applications

  FROM ACCOUNT_USAGE.SESSIONS

  WHERE DATE_TRUNC('DAY', CREATED_ON) < CURRENT_DATE()

  GROUP BY user_name

),

latest_login_ips  AS (

  SELECT

    USER_NAME,

    EVENT_ID,

    CLIENT_IP

  FROM ACCOUNT_USAGE.LOGIN_HISTORY

) 

SELECT

  s.USER_NAME AS user_name,

  ARRAY_AGG(DISTINCT s.SESSION_ID),

  ARRAY_AGG(DISTINCT s.CLIENT_APPLICATION_ID) AS new_application_id,

  lh.CLIENT_IP as ip_address

FROM ACCOUNT_USAGE.SESSIONS s

JOIN user_previous_applications u

  ON s.USER_NAME = u.user_name

JOIN latest_login_ips lli

  ON s.USER_NAME = lli.USER_NAME

JOIN ACCOUNT_USAGE.LOGIN_HISTORY lh

  ON s.LOGIN_EVENT_ID = lli.EVENT_ID

WHERE DATE_TRUNC('DAY', s.CREATED_ON) = CURRENT_DATE()

  AND NOT ARRAY_CONTAINS(s.CLIENT_APPLICATION_ID::variant, u.previous_applications)

group by s.USER_NAME,lh.CLIENT_IP;

MITRE Technique

4 Additional Queries to Identify Snowflake Threats

On top of the scenarios mentioned above, there are more relevant queries you can use to hunt for threats in a Snowflake environment. However, the results of these queries are harder to rely on since they require a deeper context of the regular activities in the organization to differentiate the legitimate operations from those that may be part of a threat.

For example, imagine that MFA has been disabled for an administrator. This activity could be either part of a malicious operation or just an operational benign activity. To answer this question, you would need additional context:

  • Who disabled the MFA device?
  • Is it part of any task associated with an active project or duty? And If not,
    was it really the user, or is it a persistent action caused by an attacker?

Query 1 – New Administrative Role Assignment 

In the post-exploitation phase of a Snowflake attack, the attacker might create a new administrative user as a persistence mechanism.

Relevant Snowflake View

Query

-- Search for new admin role assignments

select *

from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS

where ROLE in ('ORGADMIN', 'ACCOUNTADMIN') 

and CREATED_ON>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

Query 2 – New Permissions Assigned to a Role

In the post-exploitation phase of a Snowflake attack, an attacker may add permissions to a non-privileged role in an effort to achieve persistence using a low-privileged user.

Relevant Snowflake View

Query

-- Search for new admin role assignments

select *

from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES

where ROLE NOT in ('ORGADMIN', 'ACCOUNTADMIN') 

and CREATED_ON>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

Query 3 – Changes to Users Security Settings

An attacker might change user security settings like passwords, MFA settings, or other authentication methods to ensure persistence, or as a post-exploitation step. 

Relevant Snowflake View

Query

-- Search for user security settings changes

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

where QUERY_TEXT ilike '%ALTER%USER%' 

      and QUERY_TYPE = 'ALTER_USER' 

      and REGEXP_LIKE(QUERY_TEXT, '.*(PASSWORD|ROLE|DISABLED|EXPIRY|UNLOCK|MFA|RSA|POLICY).*', 'i')

      and START_TIME>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

Query 4 – Changes to Network Policies

An attacker might alter network policies to allow traffic from a specific IP address.   

Relevant Snowflake View

Query

-- Search for network policies settings changes

select *

from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

where (QUERY_TYPE in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY') or

       QUERY_TEXT ilike any ('% set network_policy%', '% unset network_policy%') )

      and START_TIME>= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());

MITRE Technique

Choose a Reliable, Fast, and Simple Snowflake Threat-Hunting Tool

In our pursuit to empower organizations with proactive cybersecurity measures, Rezonate is excited to introduce our open-source tool designed specifically for threat-hunting in Snowflake.

This tool leverages Snowflake SDK to run the different threat models mentioned in this post and allows you to easily start your own hunting journey in your own environment. Feel free to suggest expansions and improvements – we’d love to hear from you 🙂

You can find a link to our repository here.

Rezonate was recognized as a 2023 Gartner® Cool Vendor™ in Identity-First Security.  Learn More.