SnowPro Advanced: Security Engineer Question 376
Single answerReview access logs (access_history) to determine which tables, views, and columns were read or modifiedA security engineer is investigating whether a contractor account improperly viewed sensitive fields in the PROD database over the last 24 hours. The engineer must identify which base tables or views were accessed by the contractor's queries and determine the specific columns that were read, including cases where a view was queried instead of the underlying table directly. Which approach should the engineer use?
- A
Query ACCOUNT_USAGE.ACCESS_HISTORY, join it to ACCOUNT_USAGE.QUERY_HISTORY by QUERY_ID, filter for the contractor user and time range, and inspect DIRECT_OBJECTS_ACCESSED plus BASE_OBJECTS_ACCESSED to identify the tables/views and columns read.
- B
Query ACCOUNT_USAGE.LOGIN_HISTORY for the contractor user and review the REPORTED_CLIENT_TYPE and FIRST_AUTHENTICATION_FACTOR columns to determine which tables and columns were accessed.
- C
Query ACCOUNT_USAGE.GRANTS_TO_USERS for the contractor user and infer that any table or column privilege granted in the last 24 hours represents data that was actually read.
- D
Query INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS for the PROD database and compare the metadata to the contractor's assigned role to determine which columns were read through views.
Show answer and explanation
Correct answer: A
Explanation
For this requirement, Snowflake's ACCOUNT_USAGE.ACCESS_HISTORY view is the key auditing source because it records object access for queries, including column-level information. In investigations, it is common to join ACCESS_HISTORY with ACCOUNT_USAGE.QUERY_HISTORY using QUERY_ID so the auditor can correlate the query text, executing user, timestamps, and execution context. DIRECT_OBJECTS_ACCESSED shows objects explicitly referenced by the query, while BASE_OBJECTS_ACCESSED is especially important when a user accessed data through a view, because it helps identify the underlying base objects and columns involved. This distinction matters in security investigations where a user may query a view that exposes sensitive columns from one or more base tables. By contrast, LOGIN_HISTORY covers authentication events only, and GRANTS or INFORMATION_SCHEMA views describe possible access or metadata rather than actual data access. This aligns with Snowflake best practice: use ACCESS_HISTORY for auditing what data was accessed, and use QUERY_HISTORY for supporting context about the statements that caused that access.
- A. Correct.
Correct. ACCESS_HISTORY is the appropriate source for analyzing object-level and column-level data access. In this scenario, the engineer needs to know not just what the user could access, but what was actually accessed. Joining ACCESS_HISTORY to QUERY_HISTORY by QUERY_ID helps correlate the access event with the SQL statement, user, and time range. DIRECT_OBJECTS_ACCESSED identifies objects directly referenced in the query, while BASE_OBJECTS_ACCESSED helps reveal underlying base objects accessed through constructs such as views. These fields include column-level details, making this the best approach for determining which tables, views, and columns were read.
- B. Incorrect.
Incorrect. LOGIN_HISTORY records authentication and login-related events, not query-level object access. It can help establish when and how a user connected, but it does not show which tables, views, or columns were read or modified. This is a common misconception because login auditing is often confused with data access auditing.
- C. Incorrect.
Incorrect. GRANTS_TO_USERS shows privileges assigned to users, which indicates potential access, not actual usage. A contractor may have SELECT on a table or view without ever querying it. The requirement here is to investigate what was actually read in the last 24 hours, so privilege metadata is insufficient.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA metadata describes database objects and structure, and role review can indicate possible access paths. However, metadata does not provide an audit trail of actual queries or accessed columns. It also cannot reliably determine whether a user queried a view or which underlying base columns were read during a specific time window.