What you will be able to do
- Choose between ACCOUNT_USAGE views and Information Schema table functions based on how fresh and how old the evidence needs to be
- Find bulk reads, unloads and out-of-place roles or warehouses in QUERY_HISTORY
- Use direct and base objects in ACCESS_HISTORY to show who read a base-table column, including reads through secure views
- Track changes to secure views, UDFs and procedures, and who made them
- Recognise password spraying, brute force and other authentication anomalies in LOGIN_HISTORY
- Investigate brute-force and unauthorized login attempts by combining manual LOGIN_HISTORY queries with Trust Center authentication-failure scanners
- Trace queries issued by Snowpark Container Services services and AI agents
Key concept
Direct versus base objects — ACCESS_HISTORY records each statement twice over. Direct objects are the objects the query named, such as a view. Base objects are the underlying tables and columns the data actually came from. To show who touched a sensitive column, you have to search the base objects.
1.Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA
Every Snowflake security investigation starts by picking which view to read. Three views in SNOWFLAKE.ACCOUNT_USAGE do most of the work. QUERY_HISTORY records what ran, LOGIN_HISTORY records who authenticated and how, and ACCESS_HISTORY records which objects and columns each statement read or wrote. Each keeps 365 days of history. Each also lags behind real time: up to 45 minutes for queries, up to 2 hours for logins and up to 3 hours for access records.
The Information Schema table functions with the same names give up history in exchange for freshness. QUERY_HISTORY and LOGIN_HISTORY as table functions only cover the last 7 days. If you call LOGIN_HISTORY without TIME_RANGE_END, it returns the most recent login events. A request for a range older than 7 days returns an error, not an empty result.
| Source | History kept | Latency | Wins when |
|---|---|---|---|
| QUERY_HISTORY view in ACCOUNT_USAGE | 365 days | Up to 45 minutes | Reconstructing query activity weeks or months back |
| LOGIN_HISTORY view in ACCOUNT_USAGE | 365 days | Up to 120 minutes | Authentication trends and audit evidence |
| ACCESS_HISTORY view in ACCOUNT_USAGE | 365 days | Up to 180 minutes | Column-level evidence of who read or wrote what |
| INFORMATION_SCHEMA QUERY_HISTORY / LOGIN_HISTORY table functions | Last 7 days | Returns the most recent events | Detection rules that must fire within minutes |
LOGIN_HISTORY(
[ TIME_RANGE_START => <constant_expr> ]
[, TIME_RANGE_END => <constant_expr> ]
[, RESULT_LIMIT => <num> ] )Let the question decide the source. A rule that has to flag a burst of failed logins within minutes should read the LOGIN_HISTORY table function. Polling the ACCOUNT_USAGE view every minute does not help, because the rows that poll needs have not reached the view yet. An auditor asking about last quarter needs ACCOUNT_USAGE, because the table functions have nothing older than a week.
Latency also quietly breaks incremental exports. Suppose a pipeline copies LOGIN_HISTORY every 15 minutes and starts each run after the highest EVENT_TIMESTAMP it has already loaded. That pipeline drops every event that lands in the view after a newer event has been copied. The fix is to re-read a trailing window at least as wide as the view's latency and remove duplicates on EVENT_ID.
Checkpoint 1 of 9· Check yourself
A security team wants a rule that flags a burst of failed logins within minutes. Their draft polls SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY every minute. What is wrong with this design, and what should they read instead?
ACCOUNT_USAGE.LOGIN_HISTORY can lag by up to 120 minutes, so the newest failed logins have not reached it yet, however often it is polled. The Information Schema LOGIN_HISTORY table function returns the most recent events from the last 7 days, which suits detection rules that must fire within minutes.
“Latency for the view may be up to 120 minutes (2 hours).”Source: docs.snowflake.com
Checkpoint 2 of 9· Exam question
A SOC needs to alert within minutes on repeated failed logins, but SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY can lag by up to two hours. Which data source gives the lowest-latency view of recent login attempts?
Correct answer: D — Call the INFORMATION_SCHEMA.LOGIN_HISTORY table function, which has no ingestion lag but covers only about the last seven days.
- A. Organization-level views add latency rather than removing it, so they are not a faster source of login events.
- B. SESSIONS rows depend on successful logins and carry the same ACCOUNT_USAGE latency, so failed attempts do not appear there.
- C. Polling more often does not change when rows become visible, so recent failures still appear only after the ingestion delay.
- D. The Information Schema table function reads current data without the ACCOUNT_USAGE ingestion delay, in exchange for a short retention window.
2.Suspicious query patterns in QUERY_HISTORY
Exfiltration rarely needs unusual SQL. It looks like an ordinary statement with the wrong volume, the wrong context or the wrong destination, and QUERY_HISTORY records all three for each statement. user_name, role_name and warehouse_name show who ran the query, under which role and on which compute. Those are the first columns to compare against what that user normally does.
These columns map to the patterns analysts look for:
- Bulk selects: high
bytes_scannedorbytes_written_to_resulton a sensitive schema, especially from aSELECT *. - Unloads: non-zero
rows_unloaded.outbound_data_transfer_cloud,outbound_data_transfer_regionandoutbound_data_transfer_bytesshow data leaving for another region or cloud. - Remote calls:
external_function_total_sent_rowsandexternal_function_total_sent_bytesshow how much a query pushed to remote services. - Unusual context: a privileged
role_name, a warehouse the user never uses, an unexpectedquery_tag, orsecondary_role_statsshowing extra roles were in play. - Failed probing:
execution_statusof fail, witherror_codeanderror_message, often from someone testing privileges.
query_text holds the statement as it was submitted, truncated at 100K characters. A search of that text finds the objects the user typed. It does not find the tables behind a view, so reads through a view need ACCESS_HISTORY instead. One coverage gap is worth knowing: short-running queries that touch only hybrid tables no longer appear in QUERY_HISTORY. Those are monitored through AGGREGATE_QUERY_HISTORY and AGGREGATE_ACCESS_HISTORY.
Checkpoint 3 of 9· Match them up
Match each QUERY_HISTORY column to the exfiltration or misuse signal it helps an analyst spot.
Tap a term, then the definition that fits it.
Each pattern shows up in its own column. Result size shows bulk selects, rows_unloaded shows exports, the external_function_* columns show data sent to remote services, failed status with error codes shows probing, and secondary_role_stats shows extra roles in the query's context.
“The total number of bytes that this query sent in all calls to all remote services.”Source: docs.snowflake.com
3.Reading direct objects, base objects and writes in ACCESS_HISTORY
ACCESS_HISTORY holds one row per SQL statement. That row connects the user, the query, the objects, the columns and the data, which makes it the evidence auditors ask for. Its query_id is the same identifier used in query history, so the two views join directly.
direct_objects_accessed lists what the query named, including columns pulled in by *. base_objects_accessed lists the underlying objects needed to run it. Base objects include source columns and columns used only to filter, such as a WHERE column that never appears in the result. Secure views follow the same rule: the log record contains the underlying base table. Suppose analysts only query a secure view built on PROD.HR.EMPLOYEES and an auditor asks who read its SSN column. Flatten base_objects_accessed, filter on that table's objectName and the SSN columnName, and read user_name. Filtering direct_objects_accessed on the base table finds nothing, because that column holds the view. A list of SELECT grants proves permission, not that anyone actually read the column.
Writes are recorded in objects_modified. Each target column lists directSources and baseSources, which gives you column lineage. Loads, unloads, PUT, GET, CTAS and clones are all write operations in this view. When a stage is involved, stageKind and location are filled in, so a COPY INTO an external location shows the destination URL. policies_referenced lists the masking and row access policies that were enforced on the statement. Not every row in QUERY_HISTORY appears in ACCESS_HISTORY, because the structure of the statement decides whether a record is written.
Stored procedures and multi-statement requests spread one user action across several query IDs. parent_query_id and root_query_id link each nested statement back to the call that started it. The Python connector and SQL API send several statements as one request and return a single ID. That ID is the parent ID of each individual statement.
SELECT query_id, parent_query_id, direct_objects_accessed FROM snowflake.account_usage.access_history WHERE parent_query_id = 6789;Checkpoint 4 of 9· Check yourself
An auditor asks who actually read the SSN column of PROD.HR.EMPLOYEES last quarter. Analysts never query the table itself. They only query a secure view built on top of it. Which approach produces that evidence?
For a secure view, ACCESS_HISTORY records the underlying table and its columns in base_objects_accessed. direct_objects_accessed holds only the view the query named, and query_text only contains what the user typed. A list of grants proves permission, not that anyone actually read the column.
“Secure Views. The log record contains the underlying base table (i.e. base_objects_accessed) to generate the view.”Source: docs.snowflake.com
Checkpoint 5 of 9· Exam question
An auditor asks which users read the SSN column of the base table PROD.HR.EMPLOYEES during the last month. Analysts only ever query the secure view PROD.HR_SHARE.V_EMPLOYEES that is built on that table. Which ACCOUNT_USAGE approach answers the question?
Correct answer: C — Flatten ACCESS_HISTORY.BASE_OBJECTS_ACCESSED, filter on the SSN column of the base table, and read USER_NAME for each matching query.
- A. Grants show who could read the data, not who actually did, so they cannot answer an access question.
- B. DIRECT_OBJECTS_ACCESSED lists only the objects named in the statement, which here is the view, so the base table never appears there.
- C. BASE_OBJECTS_ACCESSED resolves views down to the underlying table columns, so reads through the secure view are still attributed to the base column.
- D. QUERY_TEXT stores the SQL as submitted, so analysts querying the view never have the base column name in the text.
Sources3
4.Tracking changes to secure views, functions and procedures
Secure views, secure UDFs and secure procedures hide their definitions from consumers. That makes an unreviewed change to one of them especially dangerous. Rewriting a secure view to drop a filter widens access without touching a single grant.
For views, object_modified_by_ddl in ACCESS_HISTORY records DDL on databases, schemas, tables, views and columns. It also records statements that attach a row access policy or masking policy and statements that change tags. A row there carries user_name and query_start_time, which tells you who redefined the view and when. Join on query_id to QUERY_HISTORY to get the role_name that was active and the full query_text of the new definition.
For functions and procedures, record both changes and use. A redefinition is a statement in QUERY_HISTORY, so filter query_text for the object's name and read user_name and role_name. Use appears in ACCESS_HISTORY. direct_objects_accessed lists UDFs and stored procedures as FUNCTION or procedure entries with argumentSignature and dataType, which separates overloads that share a name. Tables read inside a UDF are logged in base_objects_accessed.
Checkpoint 6 of 9· Check yourself
Someone removed the WHERE filter from a secure view last week. Consumers now see more rows, but no grant changed. Which source tells you who redefined the view and when?
object_modified_by_ddl records DDL on views, and the same row has user_name and query_start_time, which give you who changed the view and when. To get the active role_name and the full new definition, join on query_id to QUERY_HISTORY. policies_referenced and base_objects_accessed only describe reads, not the change itself.
“Specifies the DDL operation on a database, schema, table, view, and column.”Source: docs.snowflake.com
Sources3
5.Authentication anomalies in LOGIN_HISTORY
LOGIN_HISTORY has one row per login attempt. The columns that matter most for security are CLIENT_IP, USER_NAME, IS_SUCCESS, ERROR_CODE, FIRST_AUTHENTICATION_FACTOR and SECOND_AUTHENTICATION_FACTOR. Attacks show up as patterns across many rows, not in any single row.
These are the shapes to recognise:
- Password spraying: a single
CLIENT_IPwithIS_SUCCESS = 'NO'across dozens of distinctUSER_NAMEvalues in a short window, all using a password first factor. - Successful takeover: a burst of failures from one address followed by a success for one of the targeted users from the same
CLIENT_IP. - Brute force: repeated failures against one
USER_NAME. - Unidentified attempts: failures with a NULL
USER_NAME, which happen when authentication fails before Snowflake can resolve the user. Examples are invalid OAuth client credentials and a key pair that references an unknown user. - Missing MFA: a NULL
SECOND_AUTHENTICATION_FACTORfor a user who should always use multi-factor authentication.
Two columns are easy to misread. REPORTED_CLIENT_TYPE and REPORTED_CLIENT_VERSION are not authenticated, so a client can claim any value. A change in reported client is a lead to follow up, not proof of anything. INTERNAL_SNOWFLAKE_IP/0.0.0.0 marks logins triggered by Snowflake's own operations, such as Snowsight worksheet sessions, not attacker traffic. Error codes also need care: a federated-authentication, MFA or OAuth error points at that authentication path, not at someone guessing passwords.
Leaked password protection and malicious IP protection leave traces here as well. LOGIN_DETAILS is a JSON object that holds the malicious IP protection category, the risk category and the blocking status of each login. A blocked high-risk address shows the protection working. A run of risky addresses that were not blocked is a reason to check whether the protection was turned off. To follow a suspicious session into the data it touched, join QUERY_HISTORY.authn_event_id to LOGIN_HISTORY.EVENT_ID, then join query_id to ACCESS_HISTORY.
Manual review answers a pointed question about one address or one account. Group failed attempts by CLIENT_IP and count distinct USER_NAME values to expose spraying. Group them by USER_NAME per time window to expose brute force. Then look for a success from the same address after the failures, which turns an attempt into an incident. Read the table function for the last few hours and the ACCOUNT_USAGE view for anything older.
The Trust Center covers the same ground on a schedule, with nobody writing the query. Its Threat Intelligence scanner package includes a scanner that identifies users with a high volume of authentication failures and returns a risk-severity finding with a mitigation recommendation, and a Login protection scanner that finds recent logins from unusual IP addresses. The package runs once a day by default, so it is a daily sweep and not a real-time rule. A flagged user is also not necessarily under attack: the same finding can come from misconfigurations, exceeded quotas or permission issues, so confirm the pattern in LOGIN_HISTORY before locking an account. Trust Center findings as alert sources covers wiring them into alerts.
To put those findings in front of an external on-call tool, attach notification integrations to the scanner package with a severity threshold. One integration can take every finding while a PagerDuty integration receives only HIGH or CRITICAL ones, so the pager is not woken by low-severity noise.
Checkpoint 7 of 9· Match them up
A review of LOGIN_HISTORY turns up the findings below. Pair each one with what it actually tells the investigator.
Tap a term, then the definition that fits it.
Attacks such as spraying only show up as a pattern across many rows. Some single values look alarming but are harmless. INTERNAL_SNOWFLAKE_IP/0.0.0.0 marks Snowflake's own internal logins, not proxied attacker traffic. The reported client columns are not authenticated, so a change there is only a lead.
“INTERNAL_SNOWFLAKE_IP/0.0.0.0 appears as the client IP for login events triggered by internal Snowflake operations that support your usage.”Source: docs.snowflake.com
Checkpoint 8 of 9· Exam question
Which two LOGIN_HISTORY patterns best indicate a password-spraying attack coming from a single source?(Select 2)
Correct answers: A, D — A burst of failures from one CLIENT_IP that is followed by a successful login for one of the targeted users from that same address.; One CLIENT_IP with IS_SUCCESS = 'NO' rows spread across dozens of distinct USER_NAME values inside a short window, all using a password factor.
- A. A success right after a failure burst from the same source suggests one guessed credential and is the highest-priority follow-up.
- B. Expired assertions are a federation or clock-skew problem and do not involve guessing passwords.
- C. That value marks internal Snowflake operations, not external attackers.
- D. Spraying tries few passwords against many accounts, so many users failing from the same IP is the classic signature.
- E. Different client types with MFA succeeding is normal for one user and says nothing about guessing.
6.Tracing data access by container services and AI agents
AI/ML workloads running on Snowpark Container Services query Snowflake as a service, not as a person, and the audit views record them that way. In QUERY_HISTORY, a query run by a service has user_type set to SNOWFLAKE_SERVICE. user_database_name and user_schema_name then identify the database and schema the service belongs to. Filtering on those columns separates a model-serving container's reads from human activity. Its query_id values then lead into ACCESS_HISTORY for the exact columns the container read.
Two consequences change how you investigate. When a service logs in, LOGIN_HISTORY masks its client IP to INTERNAL_SNOWFLAKE_IP/0.0.0.0, so IP-based rules cannot tell services apart. Use the service's database and schema instead. Agent-driven access has its own markers. QUERY_HISTORY.agent_type reports CORTEX_AGENT, CORTEX_LITE_AGENT or EXTERNAL_AGENT. ACCESS_HISTORY.agents_info lists the invoking agents in order, from the nearest agent to the top-level agent.
Checkpoint 9 of 9· Check yourself
Two model-serving containers on Snowpark Container Services, each in its own database and schema, both read a sensitive table. You need to know which container read which columns. Which approach separates them?
Service queries carry user_type SNOWFLAKE_SERVICE, and user_database_name and user_schema_name name the service's database and schema. Joining query_id to ACCESS_HISTORY then gives the exact columns each one read. IP rules don't work here. Snowflake masks service logins to INTERNAL_SNOWFLAKE_IP/0.0.0.0, and the same value appears for other internal operations such as Snowsight worksheet sessions.
“When the value in the user_type column is SNOWFLAKE_SERVICE, it specifies the service’s database name; otherwise, it’s NULL.”Source: docs.snowflake.com
Sources
(7)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“the table function restricts the results to activity over the past 7 days, versus 365 days for the Account Usage view”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA“Latency for the view may be up to 45 minutes.”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA“Number of rows unloaded during data export.”
↩︎ Suspicious query patterns in QUERY_HISTORY“Target region for statements that unload data to another region and/or cloud.”
↩︎ Suspicious query patterns in QUERY_HISTORY“Text of the SQL statement. The limit is 100K characters.”
↩︎ Suspicious query patterns in QUERY_HISTORY“This ID corresponds to the value in the event_id column in the LOGIN_HISTORY view.”
↩︎ Authentication anomalies in LOGIN_HISTORY“If a Snowpark Container Services service executes the query, the user type is SNOWFLAKE_SERVICE.”
↩︎ Tracing data access by container services and AI agents“The total number of bytes that this query sent in all calls to all remote services.”
↩︎ Checkpoint“When the value in the user_type column is SNOWFLAKE_SERVICE, it specifies the service’s database name; otherwise, it’s NULL.”
↩︎ Checkpoint - 2.
“Latency for the view may be up to 120 minutes (2 hours).”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA“Failed authentication attempts where the user could not be identified appear with a NULL USER_NAME.”
↩︎ Authentication anomalies in LOGIN_HISTORY“Reported type of the client software, such as JDBC_DRIVER, ODBC_DRIVER, and so on. This information is not authenticated.”
↩︎ Authentication anomalies in LOGIN_HISTORY“including the malicious IP protection category name, the risk category, and the blocking status.”
↩︎ Authentication anomalies in LOGIN_HISTORY“When a Snowpark Container Services service logs into Snowflake, the client IP is masked to INTERNAL_SNOWFLAKE_IP/0.0.0.0.”
↩︎ Tracing data access by container services and AI agents“Latency for the view may be up to 120 minutes (2 hours).”
↩︎ Exam trap 2“INTERNAL_SNOWFLAKE_IP/0.0.0.0 appears as the client IP for login events triggered by internal Snowflake operations that support your usage.”
↩︎ Exam trap 3“INTERNAL_SNOWFLAKE_IP/0.0.0.0 appears as the client IP for login events triggered by internal Snowflake operations that support your usage.”
↩︎ Checkpoint - 3.
“Latency for the view may be up to 180 minutes (3 hours).”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA“Short-running queries that operate exclusively against hybrid tables will no longer generate a record in the QUERY_HISTORY view”
↩︎ Suspicious query patterns in QUERY_HISTORY“Secure Views. The log record contains the underlying base table (i.e. base_objects_accessed) to generate the view.”
↩︎ Reading direct objects, base objects and writes in ACCESS_HISTORY“The URL of the external location when the data access is an external location (e.g. s3://mybucket/a.csv).”
↩︎ Reading direct objects, base objects and writes in ACCESS_HISTORY“Records in the QUERY_HISTORY view do not always get recorded in the ACCESS_HISTORY view.”
↩︎ Reading direct objects, base objects and writes in ACCESS_HISTORY“The query ID of the topmost job in the chain or NULL if the job does not have a parent.”
↩︎ Reading direct objects, base objects and writes in ACCESS_HISTORY“Specifies the DDL operation on a database, schema, table, view, and column.”
↩︎ Tracking changes to secure views, functions and procedures“The name and data type for each argument in the UDF or stored procedure.”
↩︎ Tracking changes to secure views, functions and procedures“An ordered JSON array of invoking agents, from the nearest agent to the top-level agent.”
↩︎ Tracing data access by container services and AI agents - 4.
“If TIME_RANGE_END is not specified, the function returns the most recent login events.”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA“If the time range does not fall within the last 7 days, an error is returned.”
↩︎ Choosing between ACCOUNT_USAGE and INFORMATION_SCHEMA - 5.
“Identifies users with a high number of authentication failures or job errors”
↩︎ Authentication anomalies in LOGIN_HISTORY“Finds recent logins from unusual IP addresses.”
↩︎ Authentication anomalies in LOGIN_HISTORY“The Threat Intelligence scanner package runs once a day by default, but you can change the schedule.”
↩︎ Authentication anomalies in LOGIN_HISTORY“The Threat Intelligence scanner package runs once a day by default, but you can change the schedule.”
↩︎ Exam trap 4 - 6.
“the PagerDuty integration receives notifications for HIGH or CRITICAL findings”
↩︎ Authentication anomalies in LOGIN_HISTORY
Also cited
“base_table in the base_objects_accessed column because that is the original source of the data in view_2.”
↩︎ Key concept“view_2 in the direct_objects_accessed column because the query specifies view_2.”
↩︎ Exam trap 1“base_table in the base_objects_accessed column because that is the original source of the data in view_2.”
↩︎ Prediction