SnowPro Advanced: Security Engineer Question 377
Single answerReview access logs (access_history) to determine which tables, views, and columns were read or modifiedA security engineer is investigating whether a contractor's role accessed sensitive customer data during the last 7 days. The team needs to identify which queries read from or wrote to a specific table, and which columns were involved. The engineer wants a solution that uses Snowflake's native access logging with enough detail to distinguish direct object access from objects modified by DML. Which approach should the engineer use?
- A
Query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY, filter by the time window and query IDs of interest, and inspect BASE_OBJECTS_ACCESSED, DIRECT_OBJECTS_ACCESSED, and OBJECTS_MODIFIED to identify the tables, views, and columns read or changed.
- B
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY only, because it contains the full SQL text and therefore definitively identifies all tables, views, and columns read or modified by every statement.
- C
Query SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY and correlate successful logins to the contractor's user, because login metadata includes the database objects and columns accessed after authentication.
- D
Use SHOW GRANTS TO ROLE for the contractor's role, because granted privileges reveal which tables, views, and columns were actually read or modified during the investigation period.
- E
Query INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS for the target database, because current metadata can be used to reconstruct which columns were read or modified in the past 7 days.
Show answer and explanation
Correct answer: A
Explanation
For reviewing which tables, views, and columns were read or modified, Snowflake's ACCESS_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE is the appropriate native source. It is specifically intended for auditing data access and includes structured fields such as DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED, and OBJECTS_MODIFIED. This is important in security investigations because SQL text alone may not accurately reveal underlying base-table access through views or identify exact columns involved. QUERY_HISTORY is often complementary for narrowing the set of relevant statements, users, roles, or time ranges, but it is not sufficient on its own for authoritative column-level access analysis. Best practice is to correlate QUERY_HISTORY and ACCESS_HISTORY when needed, while relying on ACCESS_HISTORY for the actual access evidence.
- A. Correct.
Correct. ACCESS_HISTORY is the native Snowflake source designed to analyze data access at the object and column level. In this scenario, the engineer needs to know what was read and what was modified. DIRECT_OBJECTS_ACCESSED identifies objects directly referenced by the query, BASE_OBJECTS_ACCESSED helps resolve underlying base objects involved in access paths such as views, and OBJECTS_MODIFIED identifies objects changed by operations such as INSERT, UPDATE, DELETE, MERGE, and CTAS-like write activity where applicable. These fields also include column-level details, which is exactly what the investigation requires.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful for finding statements, users, roles, execution status, and SQL text, but it does not by itself provide authoritative column-level access lineage for what was actually read or modified. Parsing SQL text is unreliable for this purpose, especially with views, aliases, masking policies, indirect access paths, or generated statements. It is common to start with QUERY_HISTORY to narrow candidates, but ACCESS_HISTORY is the correct source for detailed object and column access analysis.
- C. Incorrect.
Incorrect. LOGIN_HISTORY records authentication events such as login attempts and related metadata. It does not capture which tables, views, or columns were accessed after login. Choosing this option reflects the misconception that authentication logs can substitute for data access auditing.
- D. Incorrect.
Incorrect. SHOW GRANTS TO ROLE shows what the role is allowed to do, not what it actually did. Privilege grants are authorization metadata, not usage evidence. A role might have SELECT on many objects and only access a few of them during the time window. For an investigation, actual access logs are required.
- E. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS describe current metadata, not historical access events. They can help identify object definitions or column names, but they cannot determine which queries read or modified those objects in the last 7 days.