ADA-C01 Question 181
Single answerAudit access history using the ACCESS_HISTORY viewsA security team needs to investigate whether analysts in the FINANCE role indirectly viewed sensitive columns in the PROD_DB.FINANCE.PAYROLL table during the last 7 days. The team wants to capture not only direct SQL references to the table, but also cases where users queried a view that ultimately read from PAYROLL. Which approach should the Snowflake administrator use to produce the most reliable audit results?
- A
Query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY, filter by query start time for the last 7 days, and inspect the base_objects_accessed and objects_modified columns for references to PROD_DB.FINANCE.PAYROLL and its columns.
- B
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for SELECT statements containing the string PROD_DB.FINANCE.PAYROLL, because this captures both direct queries and view-based access.
- C
Query INFORMATION_SCHEMA.TABLES for the PAYROLL table and join it to LOGIN_HISTORY to identify users who logged in during the last 7 days and therefore could have accessed the data.
- D
Use SHOW GRANTS ON TABLE PROD_DB.FINANCE.PAYROLL to determine which users accessed the table during the last 7 days, because object privileges indicate actual usage.
Show answer and explanation
Correct answer: A
Explanation
For auditing whether users accessed a specific table or its columns, especially through views, the best source is SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY. This view records object access for executed queries and includes nested fields such as direct_objects_accessed and base_objects_accessed. base_objects_accessed is particularly important when the query referenced a view but Snowflake still needs to record the underlying base table access. By filtering ACCESS_HISTORY to the required time window and then inspecting entries for PROD_DB.FINANCE.PAYROLL, an administrator can determine whether the table or sensitive columns were accessed directly or indirectly. QUERY_HISTORY alone is not sufficient for this use case because SQL text searches do not reliably reveal underlying objects accessed through views. Likewise, LOGIN_HISTORY and privilege metadata such as SHOW GRANTS are not evidence of actual data access. This aligns with Snowflake guidance to use ACCESS_HISTORY in ACCOUNT_USAGE for governance, lineage, and auditing of object access.
- A. Correct.
Correct. ACCESS_HISTORY is designed for auditing object-level data access and includes semi-structured fields such as base_objects_accessed that identify underlying objects touched by a query, including cases where a user queried through a view. Filtering by time and then examining references to the PAYROLL table and relevant columns provides the most reliable way to audit both direct and indirect access. objects_modified is more relevant when a query changes data, but reviewing ACCESS_HISTORY as a whole is still the right approach for comprehensive auditing.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful for reviewing executed SQL text and query metadata, but searching SQL text for the table name will miss many indirect access cases where a view was queried instead of the base table. The SQL text may reference only the view name, while the underlying base table access is captured in ACCESS_HISTORY.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLES contains metadata about tables, not data access events. LOGIN_HISTORY shows who authenticated to Snowflake, not which objects they queried. A user logging in does not mean they accessed the PAYROLL table, so this approach cannot provide a reliable audit trail for table or column access.
- D. Incorrect.
Incorrect. SHOW GRANTS identifies which roles have privileges on the table, but privileges do not prove that access actually occurred. Auditing requires evidence of executed queries and object access events, which is what ACCESS_HISTORY provides.