Both TicketMaster and Santander bank announced a massive data breach – Ticketmaster of 560 million user accounts at TicketMaster and 30 million at Santander. The ShinyHunters hacking group has offered the data of both companies on various dark web marketplaces for millions of dollars.
Another common denominator of both of these breaches? They both rely on Snowflake as a third-party provider. The threat activity was discovered as recently as April but is known to have compromised user credentials of as many as 165 Snowflake customers.
This post helps you investigate any potential threats within your Snowflake accounts and offer guidance with recommended actions.
What is the Snowflake Data Warehouse?
Snowflake is a cloud-based platform specializing in data warehousing and analytics and built to manage extensive data storage and processing needs. Its scalable architecture allows organizations to effortlessly handle and analyze large volumes of data. Snowflake is popular across numerous industries for its capacity to integrate data from diverse sources, facilitating powerful data-driven insights and advanced analytics.
Understanding Snowflake’s Security Features
Snowflake has a comprehensive security framework with a number of features that ensure your data is secure and meets compliance. ITs entire architecture, operations and implementation are intended to protect your data both in rest and in transit as cybersecurity threats continue to evolve.
These features include:
- Data encryption. This includes client-side end-to-end encryption of data both at-rest and in-transmit so that no one can see your data while it’s being moved to and from Snowflake.
- Network Security. Snowflake offers its customers IP whitelisting to block suspicious IPs, and its own virtual private network, in addition to its own dedicated environment within its own Virtual Private Cloud to deliver enhanced security for data and operations. It also supports AWS PrivateLink, a service that creates private Virtual Private Clouds and connects them between AWS VPC and Snowflake VPCs without using the public internet.
- User authentication and access control. Snowflake combines Discretionary Access Control (DAC), which allows the resource owner to decide who has access, with Role-Based Access Control (RBAC), which limits access to users based on their specific roles.
- Data masking and dynamic data masking. Data masking is a blanket approach that hides or anonymizes data to prevent accidental sharing or unauthorized use. Dynamic data masking, in contrast, enables selective hiding of sensitive data such as PII, PHI, PCI-DSS and determines which customers or users should have access to which type of data.
- Compliance certifications. These include certifications for compliance of SOC1, SOC2, PCI-DSS, HITRUST, ISO 27001 and many more.
Auditing Your Snowflake Deployment
Auditing user activity helps you identify any operation performed by a specific user and can be an essential step in responding to them in real-time, identifying unauthorized log-ins, and taking the necessary steps to mitigate against threats to your system and network.
The different types of audit logs include:
- Query history. Information related to user queries such as query text, query status and performance metrics.
- Login history. Information related to user login such as login timestamps, user identifiers, client IP addresses and authentication methods.
- Access logs. Information related to user queries such as query text, user roles, timestamps, and object details.
- Usage logs. Information related to virtual warehouse usage, credit consumption, and data storage.
- Security logs. Information related to security activities such as role changes, permission grants and revokes and security configurations.
- Event tables. Information related to specific events such as event types, timestamps, user identifiers, and affected objects.
Snowflake Audit Audience
Different user roles you should audit include:
- CIO
- CISO
- IT Security Teams
- System Administrators
- Snowflake Admin Users
- Compliance Officers
Snowflake Audit: Pre-Investigation Checklist
In order to start the investigation and perform the recommended actions, you will need the following as a baseline:
1. Have access to your Snowflake tenant.
2. Be familiar with query execution with its output.
3. Disable inactive/suspicious users.
4. Reset password for all active users – The same screen menu as above.
5. Enable MFA for all accounts.
Investigation/Remediation Steps
There are a few steps you can take to find the information you need to identify security threats or potential risks of security threats.
Identifying Access from Suspected IP Addresses (Method #1)
LOGIN_HISTORY Tracks all login attempts, facilitating the detection of irregular login activities, like repeated failed attempts or logins from unfamiliar locations. You can review the login history activity of an existing user, validating that the IP Address in the “CLIENT_IP” field is known. Note to update the marked field:
select * from LOGIN_HISTORY where USER_NAME like '%EXISTING_USER%' AND EVENT_TIMESTAMP LIKE '%2024-04%' order by EVENT_TIMESTAMP
Identifying Access from Suspected IP Addresses (Method #2)
Snowflake noted that the following IP addresses are under investigation for unauthorized activity.
The following query will return login events that originated from these suspected IP addresses:
SELECT
*
FROM
snowflake.account_usage.login_history
WHERE
client_ip IN (
'104.223.91.28',
'198.54.135.99',
'184.147.100.29',
'146.70.117.210',
'198.54.130.153',
'169.150.203.22',
'185.156.46.163',
'146.70.171.99',
'206.217.206.108',
'45.86.221.146',
'193.32.126.233',
'87.249.134.11',
'66.115.189.247',
'104.129.24.124',
'146.70.171.112',
'198.54.135.67',
'146.70.124.216',
'45.134.142.200',
'206.217.205.49',
'146.70.117.56',
'169.150.201.25',
'66.63.167.147',
'194.230.144.126',
'146.70.165.227',
'154.47.30.137',
'154.47.30.150',
'96.44.191.140',
'146.70.166.176',
'198.44.136.56',
'176.123.6.193',
'192.252.212.60',
'173.44.63.112',
'37.19.210.34',
'37.19.210.21',
'185.213.155.241',
'198.44.136.82',
'93.115.0.49',
'204.152.216.105',
'198.44.129.82',
'185.248.85.59',
'198.54.131.152',
'102.165.16.161',
'185.156.46.144',
'45.134.140.144',
'198.54.135.35',
'176.123.3.132',
'185.248.85.14',
'169.150.223.208',
'162.33.177.32',
'194.230.145.67',
'5.47.87.202',
'194.230.160.5',
'194.230.147.127',
'176.220.186.152',
'194.230.160.237',
'194.230.158.178',
'194.230.145.76',
'45.155.91.99',
'194.230.158.107',
'194.230.148.99',
'194.230.144.50',
'185.204.1.178',
'79.127.217.44',
'104.129.24.115',
'146.70.119.24',
'138.199.34.144',
'198.44.136.35',
'66.115.189.210',
'206.217.206.88',
'37.19.210.28',
'146.70.225.67',
'138.199.43.92',
'149.102.246.3',
'43.225.189.163',
'185.201.188.34',
'178.249.209.163',
'199.116.118.210',
'198.54.130.147',
'156.59.50.195',
'198.44.136.195',
'198.44.129.67',
'37.19.221.170',
'96.44.189.99',
'146.70.134.3',
'66.115.189.200',
'103.75.11.51',
'69.4.234.118',
'146.70.173.195',
'138.199.60.29',
'66.115.189.160',
'154.47.30.144',
'178.249.211.80',
'143.244.47.92',
'146.70.132.227',
'193.19.207.226',
'46.19.136.227',
'68.235.44.35',
'103.136.147.4',
'198.54.133.163',
'169.150.203.16',
'146.70.224.3',
'87.249.134.15',
'198.54.134.131',
'142.147.89.226',
'146.70.117.35',
'193.19.207.196',
'146.70.144.35',
'146.70.173.131',
'107.150.22.3',
'169.150.201.29',
'146.70.117.163',
'146.70.138.195',
'146.70.184.67',
'104.129.57.67',
'185.248.85.49',
'146.70.168.67',
'138.199.43.66',
'79.127.217.35',
'194.127.167.108',
'194.36.25.49',
'146.70.171.67',
'138.199.60.3',
'45.134.212.93',
'146.70.187.67',
'66.63.167.163',
'154.47.29.3',
'149.102.246.16',
'198.44.129.99',
'146.70.128.195',
'185.65.134.191',
'146.70.119.35',
'87.249.134.28',
'149.102.240.67',
'103.75.11.67',
'69.4.234.124',
'169.150.196.3',
'169.150.201.3',
'185.188.61.196',
'87.249.134.2',
'138.199.15.163',
'45.134.213.195',
'138.199.6.208',
'169.150.227.223',
'146.70.200.3',
'149.88.22.156',
'173.205.85.35',
'206.217.206.48',
'194.36.25.4',
'154.47.16.48',
'37.19.200.131',
'146.70.166.131',
'37.19.221.144',
'149.88.20.207',
'79.127.222.195',
'194.127.167.88',
'96.44.191.131',
'69.4.234.119',
'138.199.6.221',
'146.70.128.227',
'66.63.167.195',
'169.150.196.16',
'185.201.188.4',
'173.44.63.67',
'79.127.222.208',
'198.54.134.99',
'198.54.135.131',
'138.199.43.79',
'66.115.189.190',
'149.88.20.194',
'141.98.252.190',
'129.227.46.163',
'31.171.154.51',
'79.127.217.48',
'69.4.234.116',
'206.217.206.68',
'103.125.233.19',
'146.70.188.131',
'169.150.227.198',
'129.227.46.131',
'198.44.136.99',
'149.88.22.130',
'193.138.7.138',
'146.70.168.195',
'169.150.203.29',
'206.217.205.118',
'146.70.185.3',
'146.70.124.131',
'194.127.199.32',
'149.102.240.80',
'143.244.47.79',
'178.255.149.166',
'188.241.176.195',
'69.4.234.125',
'138.199.21.240',
'45.134.79.98',
'178.249.209.176',
'68.235.44.3',
'198.54.133.131',
'193.138.7.158',
'154.47.30.131',
'204.152.216.115',
'206.217.205.125',
'37.19.200.144',
'146.70.171.131',
'198.54.130.99',
'149.22.81.208',
'146.70.197.131',
'198.54.131.131',
'138.199.15.147',
'185.248.85.34',
'143.244.47.66',
'92.60.40.225',
'178.249.214.3',
'146.70.133.3',
'179.43.189.67',
'69.4.234.120',
'146.70.199.195',
'185.156.46.157',
'45.134.142.194',
'68.235.44.195',
'209.54.101.131',
'104.129.41.195',
'146.70.225.3',
'206.217.205.126',
'103.136.147.130',
'194.110.115.3',
'178.249.211.93',
'185.188.61.226',
'194.110.115.35',
'146.70.198.195',
'169.150.198.67',
'103.108.229.67',
'138.199.60.16',
'96.44.191.147',
'31.170.22.16',
'45.134.140.131',
'169.150.196.29',
'103.216.220.19',
'173.205.93.3',
'146.70.199.131',
'103.214.20.131',
'149.88.22.143',
'149.40.50.113',
'138.199.21.227',
'138.199.6.195',
'103.216.220.35',
'198.44.136.67',
'199.116.118.194',
'146.70.129.131',
'199.116.118.233',
'146.70.184.3',
'185.254.75.14',
'38.240.225.69',
'149.22.81.195',
'43.225.189.132',
'45.134.142.207',
'146.70.196.195',
'198.44.140.195',
'206.217.205.119',
'38.240.225.37',
'169.150.227.211',
'37.19.200.157',
'146.70.132.195',
'146.70.211.67',
'206.217.206.28',
'178.249.214.16',
'149.88.22.169',
'149.88.104.16',
'194.36.25.34',
'146.70.197.195',
'45.134.212.80',
'156.59.50.227',
'104.223.91.19',
'198.54.130.131',
'185.248.85.19',
'45.134.79.68',
'45.134.142.220',
'185.204.1.179',
'146.70.129.99',
'146.70.133.99',
'69.4.234.122',
'178.249.211.67',
'198.54.131.163',
'198.44.129.35',
'103.108.231.51',
'146.70.165.3',
'37.19.221.157',
'92.60.40.210',
'154.47.16.35',
'194.127.199.3',
'37.19.210.2',
'103.108.231.67',
'204.152.216.99',
'176.123.7.143',
'176.123.10.35',
'195.160.223.23'
)
ORDER BY
event_timestamp;
The output of the query should return “no results”.
Identifying Access from Suspected Clients
In addition to the above IP addresses, Snowflake observed malicious traffic from clients with the following characteristics:
- Connections from a client identifying itself as
rapeflake
. - Connections from a client identifying itself as
DBeaver_DBeaverUltimate
and running fromWindows Server 2022
The following query will return sessions belonging to the suspected clients:
SELECT
*
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;
The output of the query should return “no results”.
Review What Actions Were Taken by Identified Users
In a scenario where you identified a suspected user activity, you can review what actions were taken (Queries, etc.)
The below query will provide the SQL statements that were executed by the suspected user. There may be up to 45 minutes of latency before queries appear in this view. Note to update the marked fields:
set u_name = 'SUSPECTED_USER';
set s_time = '2024-04-01';
set e_time = CURRENT_TIMESTAMP;
SELECT
*
FROM
snowflake.account_usage.query_history
WHERE
user_name = $u_name
AND start_time BETWEEN $s_time
AND $e_time
ORDER BY
start_time;
Review Sessions for Unusual Applications
Snowflake’s session view provides information on the client environment that connects to the Snowflake account. Use this query to check for unusual client applications and conduct further analysis for unusual applications or operating systems:
SELECT
COUNT(*) AS client_app_count,
PARSE_JSON(client_environment) :APPLICATION :: STRING AS client_application,
PARSE_JSON(client_environment) :OS :: STRING AS client_os,
PARSE_JSON(client_environment) :OS_VERSION :: STRING AS client_os_version
FROM
snowflake.account_usage.sessions sessions
WHERE
1 = 1
AND sessions.created_on >= '2024-04-01'
GROUP BY
ALL
ORDER BY
1 ASC;
Securing Your Snowflake Deployment
You can ensure that your Snowflake deployment is secure by applying strategic access control that is not only role-based but also applies other access controls according to the needs of the user.
Setting up Network Policies
Set up account-level and user-level Network Policies for highly credentialed users/service accounts.
//Aggregate of client IP's leveraged at authentication for service discovery (client IP/ user), driver, and authentication mechanism (recommendation is to leverage SAML, Key Pair, or OAuth). *The customer will update the trusted CIDR ranges that will apply to the Network Policy enforced at the Account level.
select client_ip, user_name, reported_client_type, first_authentication_factor, count(*)
from login_history
group by client_ip, user_name, reported_client_type, first_authentication_factor
order by count(*) desc;
“Network Policies” screen:
Monitor Your Snowflake Accounts for Unauthorized Privilege Escalation or Configuration Changes
You’ll also want to monitor and review different user roles and permissions to delete those no longer in use so that only the minimum number of users required have permission.
Here’s an example of how you can do this with the query below:
//The query below will display potential indicators of privlidge escalation as users are granted the ACCOUNTADMIN role as the highest privlidge role in Snowflake. This does not include custom roles that may have been granted the ACCOUNTADMIN role.
select user_name || ' granted the ' || role_name || ' role on ' || end_time ||' [' || query_text ||']' as Grants
from query_history where execution_status = 'SUCCESS'
and query_type = 'GRANT' and
query_text ilike '%grant%accountadmin%to%'
order by end_time desc;
//Example query to detect unauthorized change management/ security anomalies
SELECT
query_text,
user_name,
role_name,
start_time,
end_time
FROM snowflake.account_usage.query_history
WHERE execution_status = 'SUCCESS'
AND query_type NOT in ('SELECT')
AND (query_text ILIKE '%create role%'
OR query_text ILIKE '%manage grants%'
OR query_text ILIKE '%create integration%'
OR query_text ILIKE '%alter integration%'
OR query_text ILIKE '%create share%'
OR query_text ILIKE '%create account%'
OR query_text ILIKE '%monitor usage%'
OR query_text ILIKE '%ownership%'
OR query_text ILIKE '%drop table%'
OR query_text ILIKE '%drop database%'
OR query_text ILIKE '%create stage%'
OR query_text ILIKE '%drop stage%'
OR query_text ILIKE '%alter stage%'
OR query_text ILIKE '%create user%'
OR query_text ILIKE '%alter user%'
OR query_text ILIKE '%drop user%'
OR query_text ILIKE '%create_network_policy%'
OR query_text ILIKE '%alter_network_policy%'
OR query_text ILIKE '%drop_network_policy%'
OR query_text ILIKE '%copy%'
)
ORDER BY end_time desc;
Securing Snowflake Deployment with Panorays
Gathering forensic data from SaaS platforms like Snowflake securely is crucial for preventing security breaches. Proactively engaging in continuous threat hunts and scrutinizing logs such as “Query_History” and “Login_History” allows organizations to effectively identify and address suspicious activities. Network Policy goes hand in hand with your security hardening.
These practices help detect anomalies like unusual data scans or uncommon IP addresses and facilitate thorough investigations into potential threats.The Snowflake campaign incident highlights the importance of vigilance and the use of advanced analytics to mitigate risks and safeguard critical data assets. Additionally, leveraging Third-Party Cyber Risk Management (TPCRM) frameworks, enhances an organization’s ability to prioritize and manage risks efficiently. Panorays will continue to update with any new information regarding this risk, ensuring our valued customers remain informed and capable in their cybersecurity efforts.
Want to learn more about how Panorays can help you manage third-party risk? Book a demo today!
Snowflake Security FAQs
-
Snowflake is a cloud-based platform specializing in data warehousing and analytics and built to manage extensive data storage and processing needs. Its scalable architecture allows organizations to effortlessly handle and analyze large volumes of data.
-
Snowflake’s audit table includes key views rather than a predetermined table. The views include login history, query history, access history, credit usage and warehouse metering usage.
-
Snowflake ensures the highest level of security for its users through a combination of network security, data encryption, data masking and dynamic masking, user access control and comprehensive compliance certifications.