SnowPro Advanced: Security Engineer Question 374
Single answerAnalyze query logs (query_history) to identify what actions were performedA security engineer is investigating a report that a contractor may have deleted customer data from a production table yesterday afternoon. The engineer has access to ACCOUNT_USAGE.QUERY_HISTORY and needs to determine what action was actually executed by the contractor's Snowflake user. Which approach will most reliably identify whether the user issued a DELETE statement against the table?
- A
Query ACCOUNT_USAGE.QUERY_HISTORY for the user's sessions during the time window and inspect QUERY_TYPE together with QUERY_TEXT for statements targeting the production table.
- B
Query ACCOUNT_USAGE.ACCESS_HISTORY and use only BASE_OBJECTS_ACCESSED to determine whether a DELETE occurred on the table.
- C
Query SHOW GRANTS TO USER for the contractor and, if the user had DELETE privilege on the table, conclude that a DELETE was performed.
- D
Query LOGIN_HISTORY for the contractor and use the reported client IP and login time to infer whether a DELETE statement was executed.
Show answer and explanation
Correct answer: A
Explanation
To determine what action was performed, the investigator should use ACCOUNT_USAGE.QUERY_HISTORY because it records executed statements and metadata such as QUERY_TEXT, QUERY_TYPE, USER_NAME, ROLE_NAME, START_TIME, and execution status. In a security investigation, best practice is to filter QUERY_HISTORY by the suspected user and time range, then inspect the exact SQL text and statement classification to confirm whether a DELETE, UPDATE, TRUNCATE, or another operation occurred. ACCESS_HISTORY can complement the investigation by showing object access patterns, but it is not the primary source for proving the exact DML statement that ran. LOGIN_HISTORY and privilege reviews are helpful context, but they do not establish that a destructive action actually occurred. This aligns with Snowflake documentation on ACCOUNT_USAGE views, especially QUERY_HISTORY for executed SQL and ACCESS_HISTORY for object access auditing.
- A. Correct.
Correct. ACCOUNT_USAGE.QUERY_HISTORY is the appropriate source to identify what SQL statements were executed. In this scenario, filtering by USER_NAME, time range, and table name, then reviewing QUERY_TYPE and QUERY_TEXT, is the most reliable way to confirm whether a DELETE statement was actually issued. QUERY_TYPE helps classify the statement, while QUERY_TEXT provides the exact SQL text submitted.
- B. Incorrect.
Incorrect. ACCESS_HISTORY is useful for understanding data access lineage and object interactions, but using only BASE_OBJECTS_ACCESSED is not sufficient to determine that a DELETE occurred. A table can appear in access history for many reasons, including SELECT and other operations. It does not replace reviewing the actual executed SQL text in QUERY_HISTORY when the goal is to identify the specific action performed.
- C. Incorrect.
Incorrect. SHOW GRANTS TO USER reveals what the user was allowed to do, not what the user actually did. Having DELETE privilege on a table does not prove that a DELETE statement was executed. This is a common audit mistake: confusing authorization with observed activity.
- D. Incorrect.
Incorrect. LOGIN_HISTORY can help establish whether and when a user authenticated and from where, but it does not capture SQL statement execution details. It is useful for access investigations, not for confirming whether a specific DELETE operation was run.