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:
- From the left pane, choose Data and then Databases.
- Select the SNOWFLAKE database and expand it.
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" timeMITRE Technique
- Credential Access | Brute Force | ATT&CK T1110
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" timeMITRE Technique
- Credential Access | Brute Force | ATT&CK T1110
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
- Credential Access | Brute Force | ATT&CK T1110
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.
- 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.
- 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
- Execution | Scheduled Task/Job | ATT&CK T1053
- Execution | Automated Exfiltration | ATT&CK T1020
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
- Exfiltration | ATT&CK TA0010
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
- Credential Access | ATT&CK TA0006
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.
