SnowPro Advanced: Security Engineer Question 375
Single answerAnalyze query logs (query_history) to identify what actions were performedA Security Engineer is investigating whether a contractor account executed any data-changing statements against a finance schema during the last 24 hours. The engineer wants to use Snowflake query logs to determine exactly what actions were performed by that user, including whether statements were INSERT, UPDATE, DELETE, MERGE, or only SELECT statements. Which approach will best provide this information?
- A
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY filtering by USER_NAME and START_TIME, then inspect QUERY_TEXT and QUERY_TYPE to identify the executed actions.
- B
Query SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY filtering by USER_NAME and EVENT_TIMESTAMP, then inspect CLIENT_IP and REPORTED_CLIENT_TYPE to infer which SQL operations were run.
- C
Query SHOW GRANTS TO USER for the contractor account and compare the granted privileges to the finance schema objects to determine which statements were executed.
- D
Query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY and use only BASE_OBJECTS_ACCESSED to determine whether the user ran INSERT, UPDATE, DELETE, or MERGE statements.
Show answer and explanation
Correct answer: A
Explanation
For determining what actions a user actually performed, Snowflake Security Engineers should start with QUERY_HISTORY, available through ACCOUNT_USAGE and Information Schema table functions. QUERY_HISTORY records executed statements and includes fields such as USER_NAME, START_TIME, QUERY_TEXT, and QUERY_TYPE, making it the most appropriate source for identifying whether a user ran SELECT, INSERT, UPDATE, DELETE, MERGE, DDL, or other commands. LOGIN_HISTORY is limited to authentication events, and SHOW GRANTS only shows permissions, not activity. ACCESS_HISTORY is valuable for deeper auditing and lineage analysis, especially for understanding which objects were accessed, but it is not the best standalone source for classifying the exact SQL action performed. This aligns with Snowflake best practices for auditing user activity by using query history views/functions to review executed SQL and corroborating with other telemetry only when needed.
- A. Correct.
Correct. QUERY_HISTORY is the primary source for reviewing executed SQL statements and identifying what action was performed. Filtering by USER_NAME and time window allows the investigator to isolate the contractor's activity, while QUERY_TEXT shows the exact SQL submitted and QUERY_TYPE provides a normalized statement classification such as SELECT, INSERT, UPDATE, DELETE, or MERGE. This is the most direct way to determine what actions were actually executed.
- B. Incorrect.
Incorrect. LOGIN_HISTORY shows authentication-related events such as logins and connection metadata, not the SQL statements that were run after login. CLIENT_IP and client type may help an investigation, but they cannot identify whether the user executed DML or only SELECT statements. This option reflects the common misconception that session access logs are sufficient to reconstruct query activity.
- C. Incorrect.
Incorrect. SHOW GRANTS TO USER reveals what the user was allowed to do, not what the user actually did. A user may have privileges to modify data but never execute a modifying statement. This is a common audit mistake: confusing authorization state with observed activity.
- D. Incorrect.
Incorrect. ACCESS_HISTORY is useful for object-level access lineage and understanding which objects were touched, including direct and indirect object access, but using only BASE_OBJECTS_ACCESSED does not reliably identify the exact SQL action type. To determine whether the user performed INSERT versus UPDATE versus DELETE, QUERY_HISTORY is the better source because it records the statement text and query type directly.