Snowflake Security Auditing

Snowflake is a cloud-based data warehousing and analytics platform built for large-scale data storage and processing. Its highly scalable architecture enables organizations to manage and analyze vast amounts of data with ease. Snowflake is widely used across industries for its ability to integrate data from multiple sources, supporting data-driven decision-making and advanced analytics.

With 9,437 global customers and a substantial 21.51% market share in the data warehousing sector, Snowflake’s broad adoption highlights its powerful capabilities and efficiency in managing large-scale data operations.

In June 2024, a targeted threat campaign by a financially motivated threat actor, identified as “UNC5537,” was discovered to have impacted Snowflake customers. Investigations by Mandiant and Snowflake revealed that multiple customers were affected by an info-stealer malware, which resulted in credential theft prior to unauthorized access to their organizational Snowflake accounts. The threat actor exploited these credentials due to the absence of MFA on the affected accounts.

In Snowflake, reviewing security often involves queries to monitor permissions, user activity, roles, and data access patterns. Here are key queries that help provide insights into Snowflake security configurations and user behavior:

Key Strategies for Snowflake Security Controls

• Make use of Snowflake’s built-in security features like role-based access control, MFA, network policies and encryption.
• Regularly review Snowflake policies and ensure they up to date and match your organization goals.
• Educate Snowflake users and developers on security best practices.
• Snowflake has built in scanner packages that can detect vulnerabilities.
• Monitor Snowflake Audit Logs for Suspicious Activity. If possible ingest those logs into a SIEM for automated monitoring.

Security Audit Queries

Inactive Accounts

The following query shows accounts that haven’t been active in the last 3 months.

SELECT Name, LOGIN_NAME, LAST_SUCCESS_LOGIN, HAS_PASSWORD 
FROM ACCOUNT_USAGE.USERS 
WHERE LAST_SUCCESS_LOGIN < DATEADD(DAY, -90, CURRENT_DATE()) 
and DELETED_ON is null;

Privileged Accounts

The following query shows accounts that are assigned an admin role.

SELECT LOGIN_NAME, DEFAULT_ROLE 
FROM ACCOUNT_USAGE.USERS 
WHERE DEFAULT_ROLE IN ('ACCOUNTADMIN', 'SECURITYADMIN', 'SYSADMIN') and DELETED_ON is null; 

Check for Failed Logins

The following query shows failed login attempts. Review for brute force attempts or attempts to access disabled accounts.

SELECT USER_NAME, EVENT_TIMESTAMP, CLIENT_IP, ERROR_MESSAGE, SECOND_AUTHENTICATION_FACTOR
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY 
where IS_SUCCESS = FALSE 
ORDER BY EVENT_TIMESTAMP DESC; 

Query Logs by User, Role, and IP

This query groups logins by client IP and username. This is helpful for finding login activity from abnormal IP addresses.

SELECT USER_NAME, CLIENT_IP, COUNT(CLIENT_IP) 
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY 
GROUP BY CLIENT_IP, USER_NAME 
ORDER BU CLIENT_IP;

Audit for Non-SSO accounts without MFA

Shows accounts with local passwords and no MFA configured.

SELECT name, login_name, has_mfa,has_password, disabled, last_success_login  
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS  
WHERE has_mfa = false and has_password = true and deleted_on is null;

Granting Admin Roles

Shows users that granted other accounts an administrator role.

SELECT START_TIME, QUERY_TEXT, DATABASE_NAME, USER_NAME 
FROM snowflake.account_usage.query_history 
WHERE QUERY_TYPE = 'GRANT'
AND START_TIME > DATEADD(DAY, -60, CURRENT_DATE())  
AND ( 
  QUERY_TEXT ILIKE '%grant%role%accountadmin%to%' 
  OR QUERY_TEXT ILIKE '%grant%role%securityadmin%to%' 
  OR QUERY_TEXT ILIKE '%grant%role%sysadmin%to%') 

Suspicious Application Query

This query looks for suspicious activity known malicious clients.

SELECT USER_NAME, AUTHENTICATION_METHOD, CLIENT_ENVIRONMENT, CREATED_ON 
FROM SNOWFLAKE.account_usage.sessions 
WHERE (PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'rapeflake') 
  OR 
  (PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'DBeaver_DBeaverUltimate' 
  AND PARSE_JSON(CLIENT_ENVIRONMENT):OS = 'Windows Server 2022') 
ORDER BY CREATED_ON ASC;

Abnormal Login Activity

This query shows login outliers in IP addresses.

WITH ip_percentage AS ( 
  SELECT CLIENT_IP, (COUNT(*) / SUM(COUNT(*)) OVER () * 100) AS usage_percentage 
  FROM snowflake.account_usage.login_history
  GROUP BY CLIENT_IP ) 
SELECT lh.CLIENT_IP,  
ARRAY_AGG(DISTINCT DATE(lh.EVENT_TIMESTAMP)) AS dates_used, 
ARRAY_AGG(DISTINCT lh.USER_NAME) AS users,  
ip_percentage.usage_percentage 
FROM ip_percentage 
JOIN table(information_schema.login_history()) lh ON ip_percentage.CLIENT_IP = lh.CLIENT_IP
WHERE ip_percentage.usage_percentage < 5 
GROUP BY lh.CLIENT_IP, ip_percentage.usage_percentage; 

Show External Shares

User the following query to show inbound and outbound data shares. Outbound are more important to review.

SHOW SHARES;

Securing Snowflake

In today’s complex threat landscape, collecting forensic data from SaaS environments like Snowflake is vital to defending against potential security breaches. Conducting continuous threat hunts, reviewing policy configurations, and analyzing logs enables organizations to detect and respond effectively to suspicious activity. These actions help uncover anomalies, such as unusual data scanning or rare IP addresses, and support in-depth threat investigations.

Beyond threat hunting and investigations, taking proactive steps to secure your Snowflake accounts is essential. By applying these queries and best practices, you can effectively monitor and strengthen the security of your Snowflake environment.