CertSafari
    Snowflake SnowPro Advanced: Security Engineer (SEA-C01)· Lessons

    Domain 3 · Lesson 12/21

    Auditing Snowflake activity with QUERY_HISTORY, ACCESS_HISTORY and LOGIN_HISTORY

    Monitor data security.

    17 min read
    6% of exam
    7 sources
    Published 20 Oct 2026
    Docs as of 4 Oct 2026

    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.

    Retention and latency of the main audit sources
    SourceHistory keptLatencyWins when
    QUERY_HISTORY view in ACCOUNT_USAGE365 daysUp to 45 minutesReconstructing query activity weeks or months back
    LOGIN_HISTORY view in ACCOUNT_USAGE365 daysUp to 120 minutesAuthentication trends and audit evidence
    ACCESS_HISTORY view in ACCOUNT_USAGE365 daysUp to 180 minutesColumn-level evidence of who read or wrote what
    INFORMATION_SCHEMA QUERY_HISTORY / LOGIN_HISTORY table functionsLast 7 daysReturns the most recent eventsDetection rules that must fire within minutes
    The Information Schema LOGIN_HISTORY table function takes an optional time range inside the last 7 dayssql
    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?

    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?

    Sources1234

    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_scanned or bytes_written_to_result on a sensitive schema, especially from a SELECT *.
    • Unloads: non-zero rows_unloaded. outbound_data_transfer_cloud, outbound_data_transfer_region and outbound_data_transfer_bytes show data leaving for another region or cloud.
    • Remote calls: external_function_total_sent_rows and external_function_total_sent_bytes show how much a query pushed to remote services.
    • Unusual context: a privileged role_name, a warehouse the user never uses, an unexpected query_tag, or secondary_role_stats showing extra roles were in play.
    • Failed probing: execution_status of fail, with error_code and error_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.

    Sources13

    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.

    Expanding a multi-statement request into its individual statementssql
    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?

    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?

    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?

    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_IP with IS_SUCCESS = 'NO' across dozens of distinct USER_NAME values 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_FACTOR for 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.

    Checkpoint 8 of 9· Exam question

    Which two LOGIN_HISTORY patterns best indicate a password-spraying attack coming from a single source?(Select 2)

    0 of 2 selected

    Sources2156

    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?

    Sources123

    Sources

    (7)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 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. 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. 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. 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. 6.
      “the PagerDuty integration receives notifications for HIGH or CRITICAL findings”
      ↩︎ Authentication anomalies in LOGIN_HISTORY

    Also cited

    Continue to part 2 of 2

    Snowflake security alerts, notifications and telemetry export

    Spotted a mistake, or was something unclear? Tell us.