What you will be able to do
- Configure a Snowflake alert for a security condition, resume it, and test that it reaches the on-call channel
- Choose a login-history source and a collector design that detect incidents quickly with least privilege
- Contain a compromised identity by disabling the user, aborting sessions, revoking keys and applying a user-level network policy
- Find and remove attacker persistence, then restore tampered or dropped data to its pre-incident state
Key concept
Contain, eradicate, then recover — An attacker who holds a Snowflake identity can come back in through several paths at once: a login, a session that is already open, a key pair or token, or an object they created. Close every one of those paths and remove what the attacker left behind before you restore any data. Otherwise the restored data is exposed to the same attacker.
1.Building an alert for a security condition
No incident response can start until someone notices the incident, and in Snowflake that is usually an alert. A Snowflake alert is a schema-level object made of three parts: a condition (a SQL query), an action that runs when the condition is true, and a schedule or trigger that decides when the condition is checked. For security work the condition usually asks whether something suspicious happened recently, such as a burst of failed logins, and the action sends that to a person or a system.
There are two kinds of alert, and they behave differently. An alert on a schedule runs every n minutes or on a cron expression, and it checks the condition against all the data each time. An alert on new data runs only when rows arrive in one table or view, and it checks only those new rows. The second kind suits a table that your own pipeline fills with security events. It comes with restrictions, though. The FROM clause can name only one regular table, view or event table, and change tracking must be enabled on it. CTEs, joins, DML and stored-procedure calls are not allowed in the condition.
Creating an alert takes a specific set of privileges. The role needs EXECUTE ALERT on the account, and only ACCOUNTADMIN can grant it. It also needs either EXECUTE MANAGED ALERT (for a serverless alert) or USAGE on the warehouse the alert uses, plus USAGE and CREATE ALERT on the schema and USAGE on the database. An alert is not a kind of task, so no task object or task privilege is part of this list. The choice between serverless and a warehouse changes only the compute and how it is billed. A serverless run scales up to the equivalent of an XXLARGE warehouse.
Responders who get paged at 3 a.m. need to know what to do next. The RUNBOOK parameter attaches a free-text reference of up to 2048 characters, usually a URL, to the alert. That value appears wherever responders look for the alert.
CREATE OR REPLACE ALERT my_alert
WAREHOUSE = my_alert_wh
SCHEDULE = '5 MINUTE'
RUNBOOK = 'https://www.snowflake.com/alerts/my-alert-runbook'
IF( EXISTS(
SELECT 1 FROM my_table WHERE error_count > 100
))
THEN
CALL SYSTEM$SEND_EMAIL(...);Checkpoint 1 of 9· Fill the gap
You are building a scheduled alert for a security condition. Responders who get paged when it fires need a link to the response procedure, and that link should appear wherever they look up the alert. Which parameter completes this sample?
CREATE OR REPLACE ALERT my_alert
WAREHOUSE = my_alert_wh
SCHEDULE = '5 MINUTE'
? = 'https://www.snowflake.com/alerts/my-alert-runbook'
IF( EXISTS(
SELECT 1 FROM my_table WHERE error_count > 100
))
THEN
CALL SYSTEM$SEND_EMAIL(...);RUNBOOK attaches a free-text reference to the alert, usually a URL of up to 2048 characters. Snowflake shows it in SHOW ALERTS, DESCRIBE ALERT and ALERT_HISTORY, so responders know where to find the instructions. CONFIG holds JSON values that the alert's own SQL reads while it runs. It is not meant as guidance for responders.
Source: docs.snowflake.comSources1
2.Testing that an alert fires fast enough and reaches someone
A newly created alert does nothing. Snowflake creates every alert in a suspended state, and it does not evaluate its condition or write any history until someone resumes it. An alert that never emails anyone and has an empty history, even though its role has the right privileges, has almost always just never been resumed.
ALTER ALERT my_alert RESUME;Test the whole path, not just the SQL. The action is where an alert connects to your SIEM or on-call tool. For example, SYSTEM$SEND_SNOWFLAKE_NOTIFICATION can post to a Slack channel through a webhook notification integration. Setting up those integrations is covered in Snowflake alerts and notification integrations. A practical test looks like this:
- Resume the alert and check
SHOW ALERTSto confirm it is running and has the runbook you expect. - Cause a harmless instance of the condition, such as failed logins from a test user.
- Check
ALERT_HISTORYto confirm the alert evaluated the condition and ran its action. - Confirm the message arrived in the SIEM or on-call channel, with the runbook link a responder needs.
An alert on new data only runs when rows arrive, because the EXECUTE ALERT command cannot run that kind of alert. Step 2 therefore has to insert real rows into the watched table.
How fast an alert can detect something depends on where its condition reads from. The ACCOUNT_USAGE views use the same structure as the views and table functions in the Information Schema, but they differ in three ways: they include dropped objects, they keep data longer, and their data arrives later. LOGIN_HISTORY in ACCOUNT_USAGE lags by about two hours. An alert that checks it every minute just reads the same old data sixty times an hour, and serverless compute doesn't help because it changes only how the query runs, not when the view gets its data. For detection that needs to happen within minutes, query the INFORMATION_SCHEMA.LOGIN_HISTORY table function. Use ACCOUNT_USAGE when you need a year of history for an investigation.
| Property | ACCOUNT_USAGE view | INFORMATION_SCHEMA login history table function |
|---|---|---|
| Data latency | About 2 hours | Not subject to the ACCOUNT_USAGE ingestion lag |
| History kept | 365 days | Shorter |
| Dropped objects | Included | Not included |
| Best use | Investigation and trend reports | Detection that has to fire within minutes |
Checkpoint 2 of 9· Check yourself
A security engineer runs CREATE OR REPLACE ALERT for a failed-login condition, and the statement succeeds. The role has every privilege it needs. A day later, a test burst of failed logins has paged nobody, and ALERT_HISTORY has no rows for the alert. What is the most likely cause?
Snowflake creates every alert suspended. Until it is resumed, it never checks its condition and writes no history. ACCOUNT_USAGE latency would only delay detection. The alert would still run and leave history rows, and a day is far longer than the roughly two-hour lag.
“When you create an alert, the alert is suspended by default. You must resume the newly created alert in order for the alert to execute.”Source: docs.snowflake.com
Checkpoint 3 of 9· Exam question
A SOC engineer builds a Snowflake alert that should email the team within minutes when one user records five failed logins in ten minutes. The condition queries SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY, but in testing the alert only fires hours after the failed attempts. What change fixes the detection delay?
Correct answer: C — Query the INFORMATION_SCHEMA.LOGIN_HISTORY table function in the condition, because ACCOUNT_USAGE views can lag real events by up to two hours.
- A. Incorrect. QUERY_HISTORY records executed SQL statements, not authentication attempts. Failed logins never reach statement execution, so they are not visible there.
- B. Incorrect. ACCOUNT_USAGE latency is a property of how the shared SNOWFLAKE database is populated, not of the alert schedule. Evaluating every minute just re-reads the same stale data.
- C. Correct. The INFORMATION_SCHEMA table function reads recent login events without the ingestion delay of the ACCOUNT_USAGE view (up to about 120 minutes), so the alert condition sees failures promptly.
- D. Incorrect. The compute model of an alert only changes who pays for evaluation. The view contents and their latency are identical on serverless and warehouse-backed alerts.
Checkpoint 4 of 9· Exam question
An engineer ran CREATE ALERT with a one-minute schedule, an IF (EXISTS (...)) condition that detects logins from a blocked country, and a THEN CALL SYSTEM$SEND_EMAIL(...) action. The role holds the needed privileges, yet no email ever arrives and the alert history is empty. What explains this?
Correct answer: D — The alert was created in a suspended state, so it will not evaluate its condition until someone runs ALTER ALERT ... RESUME on it.
- A. Incorrect. An alert is a standalone schedulable object with its own schedule, condition and action. No wrapper task is needed or used.
- B. Incorrect. The action clause is where the notification belongs; it can be a SQL statement such as a call to SYSTEM$SEND_EMAIL. Putting the call in the condition would not be valid.
- C. Incorrect. Alerts use their own privilege, EXECUTE ALERT, rather than EXECUTE TASK, and the stem says the role already holds the necessary privileges.
- D. Correct. Newly created alerts start suspended. Resuming the alert with ALTER ALERT ... RESUME is required before the schedule runs and the condition is evaluated.
3.Giving a SIEM collector least-privilege access to audit data
Many teams pull Snowflake audit data into their SIEM with a collector that polls every few minutes. That collector holds a credential that never expires on its own, so treat its identity as a likely target for attackers. A good design gets three things right.
The first is least privilege. The SNOWFLAKE database comes with built-in database roles for ACCOUNT_USAGE, and SECURITY_VIEWER is the one meant for security information. Grant SECURITY_VIEWER to a custom role for the collector. If you grant ACCOUNTADMIN or SECURITYADMIN instead, a stolen collector key can change the account's security settings, not just read them.
The second is having no stored password. Make the collector a service user that authenticates with a key pair, as described in Key-pair authentication, programmatic access tokens and service users. Rotating a password every week still means a password sits somewhere for an attacker to steal.
The third is that a stolen credential should be useless anywhere else. Attach a network policy to the collector user that allows only the collector's IP addresses. A policy on a user overrides the account-level policy, so this rule applies even if the account policy is broad. The general rules for network policies are in Network policies: allowed/blocked lists and where they apply. Snowflake recommends scoping network policies to specific users or integrations rather than relying on one account-wide policy.
Checkpoint 5 of 9· Check yourself
Your SIEM collector's private key has been stolen. The account-level network policy allows the whole corporate IP range. The collector's service user has its own network policy that allows only the collector's two IP addresses. The attacker tries to log in as the collector from a corporate laptop whose IP is inside the corporate range but is not one of the collector's addresses. What happens?
For this user, the user-level policy replaces the account-level policy rather than adding to it. Only the collector's own IP addresses are allowed, so the stolen key is useless from anywhere else, even inside the broad corporate range.
“Network policies applied to a user override network policies applied to the account”Source: docs.snowflake.com
Checkpoint 6 of 9· Exam question
A company wants its SIEM collector to poll Snowflake audit data every five minutes. Security requires least privilege, no stored passwords, and no access for anyone who steals the collector's credentials from outside the collector network. Which design meets all three requirements?
Correct answer: B — Create a service user with key-pair authentication, a network policy limited to the collector's IPs, and a role granted SNOWFLAKE.SECURITY_VIEWER.
- A. Incorrect. ACCOUNTADMIN is vastly over-privileged for read-only log collection, and an MFA-protected person user cannot run unattended polling. Client certificates are not how Snowflake authenticates SQL clients.
- B. Correct. Key-pair authentication removes stored passwords, the user-level network policy confines use to the collector network, and the SECURITY_VIEWER database role gives read access to security-related ACCOUNT_USAGE views only.
- C. Incorrect. SECURITYADMIN manages grants and network policies rather than reading logs, and it is far more privileged than needed. An account-wide policy does not restrict this one user to the collector's IPs.
- D. Incorrect. A password, even rotated, is still a stored secret. Broad IMPORTED PRIVILEGES plus OWNERSHIP of schemas grants far more than reading audit views and ignores the network restriction.
4.Triaging a login alert and containing the identity
Triage decides two things: whether the alert is an incident, and which identities to contain. Start from the login records behind the alert, as described in LOGIN_HISTORY and authentication anomalies. Successful logins from an unfamiliar IP, a credential type the user never uses, or a service user logging in interactively are all signs of an incident. A run of failed logins alone may not be. After five failed attempts in a row, Snowflake locks the user out for about 15 minutes and then clears the lock automatically. A lockout is therefore a temporary pause, not containment, and anyone with ALTER USER ... SET MINS_TO_UNLOCK = 0 can lift it early.
Once you've decided it's an incident, close every way the identity can get in. Do the steps in this order so that you don't close one path while the attacker is still using another:
- Disable the user with
ALTER USER ... SET DISABLED = TRUE, which stops new logins. SettingDISABLED = FALSEre-enables the user later. - Abort active sessions with
SYSTEM$ABORT_SESSION(<session_id>). An account administrator can find the session IDs under Account » Sessions in the web interface. - Revoke key material. Remove a legacy key with
ALTER USER ... UNSET RSA_PUBLIC_KEY, and handle named key pairs as shown below. Revoke the identity's programmatic access tokens too. - Tighten the network policy on the user so that requests from the attacker's IP addresses are blocked.
- Stop data leaving through sharing. If the incident involves data shared outside the account, revoke or suspend the share or listing as described in Shares, listings and reader accounts.
ALTER USER janesmith SET DISABLED = TRUE;SELECT SYSTEM$ABORT_SESSION(1065153868222);Named key pairs give you three options, and which one is right depends on whether you'll need the key again. REMOVE KEY PAIR is permanent, because a removed key pair can't be recovered. MODIFY KEY PAIR ... SET DISABLED = TRUE blocks the key but keeps its metadata, so you can re-enable it if the investigation clears it. A service user that has to keep running needs a third option: rotate in a new public key and set the old key to expire immediately. The default grace period would otherwise leave the stolen key working for 24 hours.
ALTER USER IF EXISTS example_user ROTATE KEY PAIR my_key
PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgK...'
EXPIRE_ROTATED_KEY_PAIR_AFTER_HOURS = 0;The precedence rules for network policies affect how well containment works. A user-level policy beats the account policy but loses to a security integration's policy. Snowflake OAuth token requests also skip the user-level policy, except through the built-in SNOWFLAKE$LOCAL_APPLICATION integration. If the attacker comes in through OAuth, tighten the integration's policy as well. An attacker can't simply bypass a policy either: only Snowflake can set MINS_TO_BYPASS_NETWORK_POLICY, and only when you ask Snowflake Support.
| Action | Path it closes | Reversible? |
|---|---|---|
| ALTER USER ... SET DISABLED = TRUE | New logins by the user | Yes, SET DISABLED = FALSE |
| SYSTEM$ABORT_SESSION | A session that is already open | No, the session is terminated |
| REMOVE KEY PAIR | Authentication with that key | No, the key pair cannot be recovered |
| MODIFY KEY PAIR ... SET DISABLED = TRUE | Authentication with that key | Yes, metadata is retained |
| User-level network policy | Requests from IPs outside the policy | Yes, but a security integration policy overrides it |
Checkpoint 7 of 9· Check yourself
While triaging a login alert, you find a successful login to a service user from an unfamiliar IP address. Five failed password attempts follow it, and Snowflake has now locked the user out. A colleague says the identity is already contained and nothing more needs to be done. What is the right response?
A lockout is a temporary pause, not containment. It clears automatically after about 15 minutes, and it does nothing about sessions that are already open or about key-based credentials. Containment means disabling the user with SET DISABLED = TRUE, aborting active sessions and revoking key pairs and tokens.
“After the period of time elapses, the system automatically clears the lock and the user can attempt to log in again.”Source: docs.snowflake.com
5.Finding the root cause and removing persistence
Containment shuts out the attacker's current access. Eradication removes anything they left behind so they can get back in later. Start with the root cause: how did they get in? The login records show which credential type and which client IP succeeded. If it was a password-only login from outside your network, the fix is a stronger authentication method or a network policy that should have been there, not just a new password.
Next, list what the attacker could have planted. Check for users and roles created during the incident window, new role grants, tasks and integrations you don't recognize, and extra key pairs on users who otherwise look clean. SHOW USER KEY PAIRS lists a user's named key pairs. Rotated-out keys appear there too, under a name ending in _ROTATED_<epoch_ms>, until their grace period ends. Attackers also tend to drop the objects they used. ACCOUNT_USAGE keeps records of dropped objects, and adding the HISTORY clause to a SHOW command includes dropped objects in its output.
Changes to settings can also be a form of persistence. An attacker who sets Time Travel retention to 0 on a schema means that the next thing dropped there can't be restored. Look for those settings and reset them before you move on to recovery.
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = 0;Checkpoint 8 of 9· Fill the gap
During eradication, you suspect the attacker turned off Time Travel on some schemas so that anything dropped there later can't be restored. Which value completes this query so that it lists exactly those schemas?
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = ? ;A retention period of 0 days turns off Time Travel for an object, so a schema with retention_time = 0 can't be restored once it is dropped. A value of 1 is the standard default. 7 is the length of Fail-safe, not a Time Travel setting, and 90 is the Enterprise maximum. None of those values means Time Travel was turned off.
Source: docs.snowflake.com6.Restoring data to its pre-incident state
Data recovery comes last, once the attacker can no longer get back in. It relies on Time Travel, which keeps historical data for each object's retention period (Time Travel and DATA_RETENTION_TIME_IN_DAYS). Within that period you have three tools. UNDROP restores a dropped table, schema or database. A SELECT with AT or BEFORE lets you query past data. CREATE ... CLONE with AT or BEFORE builds a copy as it was at an earlier point.
Use the clone to check data before you change anything. If QUERY_HISTORY gives you the query ID of the attacker's UPDATE, clone the table BEFORE(STATEMENT => '<query_id>'). The copy is the table exactly as it was before that statement, and the live table stays untouched until you've validated the copy and decided how to merge it back. TIMESTAMP and OFFSET do the same job when you know the time but not the statement.
The recovery window is set separately for each change: it ends at the time of the change plus the retention period. For a table with 1 day of retention, data overwritten on Day 5 at 1:00 PM can be recovered with Time Travel until Day 6 at 1:00 PM. After that it moves into Fail-safe for 7 days, and only Snowflake can recover it, so contacting Snowflake Support is the only remaining option. An object with a retention period of 0 cannot be restored with UNDROP once it has been dropped.
No. When a schema is cloned at a timestamp, the clone does not include its user tasks, external tables or internal stages. As a result, a task the attacker planted is not copied over, but neither are your legitimate tasks. Recreate those from source control and check them before you resume them.
Checkpoint 9 of 9· Match them up
An attacker has been contained, and it is time to recover. Match each recovery tool to the situation where it is the right choice.
Tap a term, then the definition that fits it.
Within the retention period, UNDROP restores dropped objects, a SELECT with AT or BEFORE queries past data, and a clone with BEFORE(STATEMENT) builds a copy you can check before you change anything. After the retention period ends, the data is in Fail-safe for 7 days. You can't query, clone or UNDROP it yourself, and only Snowflake can recover it.
“After Day 6 at 1:00 PM, that version of the row moves into Fail-safe for 7 days. During that time, only Snowflake can recover it.”Source: docs.snowflake.com
Sources11
Sources
(11)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.snowflake.com/en/user-guide/alertsOfficial docs
“Alert on new data: Snowflake evaluates the condition against any new rows in a specified table or a view.”
↩︎ Building an alert for a security condition“This privilege can only be granted by a user with the ACCOUNTADMIN role.”
↩︎ Building an alert for a security condition“The maximum size for a serverless alert run is equivalent to an XXLARGE warehouse.”
↩︎ Building an alert for a security condition“The runbook is a free-text string (up to 2048 characters), typically a URL pointing to internal documentation.”
↩︎ Building an alert for a security condition“the alert is suspended by default. You must resume the newly created alert in order for the alert to execute.”
↩︎ Testing that an alert fires fast enough and reaches someone“You cannot use the EXECUTE ALERT command to execute an alert on new data.”
↩︎ Testing that an alert fires fast enough and reaches someone“The runbook value appears in the output of SHOW ALERTS, DESCRIBE ALERT, and ALERT_HISTORY.”
↩︎ Testing that an alert fires fast enough and reaches someone“the alert is suspended by default. You must resume the newly created alert in order for the alert to execute.”
↩︎ Exam trap 2“When you create an alert, the alert is suspended by default. You must resume the newly created alert in order for the alert to execute.”
↩︎ Checkpoint - 2.
“LOGIN_HISTORY | Historical | 2 hours | Data retained for 1 year.”
↩︎ Testing that an alert fires fast enough and reaches someone“LOGIN_HISTORY | Historical | 2 hours | Data retained for 1 year.”
↩︎ Exam trap 1“Records for dropped objects are included in each view. Longer retention time for historical usage data. Data latency.”
↩︎ Prediction - 3.
“This Account Usage view can be used to query login attempts by Snowflake users within the last 365 days (1 year).”
↩︎ Testing that an alert fires fast enough and reaches someone - 4.
“The SECURITY_VIEWER role provides visibility into security-based information.”
↩︎ Giving a SIEM collector least-privilege access to audit data - 5.
“Network policies applied to a user override network policies applied to the account”
↩︎ Giving a SIEM collector least-privilege access to audit data“Whenever possible, narrowly scope a network policy to a group of users or a security integration rather than an entire account.”
↩︎ Giving a SIEM collector least-privilege access to audit data“For Snowflake OAuth, the user-level network policy is not considered for client-to-Snowflake traffic such as token requests.”
↩︎ Triaging a login alert and containing the identity“Only Snowflake can set the value for this object property.”
↩︎ Triaging a login alert and containing the identity“but are overridden by a network policy applied to a security integration”
↩︎ Prediction - 6.
“the user is locked out of their account for a period of time (currently 15 minutes)”
↩︎ Triaging a login alert and containing the identity“Disabling a user prevents the user from logging into Snowflake.”
↩︎ Key concept“the user is locked out of their account for a period of time (currently 15 minutes)”
↩︎ Exam trap 3“After the period of time elapses, the system automatically clears the lock and the user can attempt to log in again.”
↩︎ Checkpoint - 7.
“Aborts the specified session.”
↩︎ Triaging a login alert and containing the identity - 8.
“Use ALTER USER … UNSET RSA_PUBLIC_KEY to remove those legacy keys.”
↩︎ Triaging a login alert and containing the identity“You cannot recover a removed key pair.”
↩︎ Exam trap 4 - 9.
“Rotate a key pair and immediately expire the prior key pair (useful when responding to a suspected private-key compromise):”
↩︎ Triaging a login alert and containing the identity - 10.
“list a user’s named key pairs with SHOW USER KEY PAIRS”
↩︎ Finding the root cause and removing persistence - 11.
“To include information about objects that have already been dropped, include the HISTORY clause with the SHOW command.”
↩︎ Finding the root cause and removing persistence“When an object with no retention period is dropped, you will not be able to restore the object.”
↩︎ Finding the root cause and removing persistence“UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
↩︎ Restoring data to its pre-incident state“STATEMENT (query ID for statement)”
↩︎ Restoring data to its pre-incident state“End of Time Travel = time of the change + data retention period”
↩︎ Restoring data to its pre-incident state“that version of the row moves into Fail-safe for 7 days. During that time, only Snowflake can recover it.”
↩︎ Restoring data to its pre-incident state“User tasks in a database or schema are not cloned when using CREATE SCHEMA … TIMESTAMP.”
↩︎ Restoring data to its pre-incident state“that version of the row moves into Fail-safe for 7 days. During that time, only Snowflake can recover it.”
↩︎ Exam trap 5“After Day 6 at 1:00 PM, that version of the row moves into Fail-safe for 7 days. During that time, only Snowflake can recover it.”
↩︎ Checkpoint