ADA-C01 Question 180
Single answerAudit access history using the ACCESS_HISTORY viewsA Snowflake administrator is investigating whether a sensitive column, CUSTOMERS.SSN, was accessed after a new BI dashboard was released last week. The administrator needs to identify which queries actually read that specific column and who ran them. Which approach should the administrator use?
- A
Query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY and inspect the base_objects_accessed or direct_objects_accessed columns to find references to the CUSTOMERS table and the SSN column, then correlate the query details and user information from the same view.
- B
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and search the query_text for the string "SSN" to determine which users accessed the sensitive column.
- C
Query SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY to identify the users who logged in during the dashboard release window, then assume those users accessed the SSN column if they used the BI tool.
- D
Use SHOW GRANTS ON COLUMN CUSTOMERS.SSN to list users with column privileges, because anyone with privileges on the column must have accessed it.
- E
Query SNOWFLAKE.ORGANIZATION_USAGE.ACCESS_HISTORY because column-level access history is only available at the organization level, not the account level.
Show answer and explanation
Correct answer: A
Explanation
To determine whether a sensitive column was actually accessed, the administrator should use SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY. This view is designed for auditing data access and includes semi-structured fields such as direct_objects_accessed and base_objects_accessed that can be used to identify accessed objects and columns, including when access occurs through views. This is more reliable than scanning QUERY_HISTORY text, because BI tools and view-based queries may not explicitly reference the underlying column name in the SQL text. LOGIN_HISTORY and SHOW GRANTS answer different questions: who authenticated and who could access, respectively, not who actually accessed the data. Snowflake documentation for ACCESS_HISTORY and Account Usage views supports this approach as a best practice for auditing object and column access.
- A. Correct.
Correct. ACCESS_HISTORY is the appropriate source for auditing object access at a detailed level, including column-level lineage and access metadata. In practice, an administrator can inspect the VARIANT fields such as base_objects_accessed and direct_objects_accessed to determine whether a query accessed the CUSTOMERS table and specifically the SSN column. The same view also includes query and user context needed to identify who ran the query. This is the best fit for proving actual access, not just potential access.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful for seeing executed SQL text and execution metadata, but searching query_text for "SSN" is unreliable for auditing actual column access. Queries may reference views, use generated SQL from BI tools, or omit the literal column name in the text while still accessing the underlying column. Conversely, the text may mention a column that was not ultimately read. This option reflects the common misconception that query text alone is sufficient for data access auditing.
- C. Incorrect.
Incorrect. LOGIN_HISTORY shows authentication events, not data access. A user logging in during the time window does not demonstrate that they queried the sensitive column. This distractor represents confusing account activity auditing with object access auditing.
- D. Incorrect.
Incorrect. SHOW GRANTS identifies who is permitted to access an object or column, not who actually did access it. Security investigations generally need evidence of actual usage, and grants only show possible access paths. This is a common mistake when auditors ask for proof of access rather than entitlement.
- E. Incorrect.
Incorrect. ACCESS_HISTORY is available in SNOWFLAKE.ACCOUNT_USAGE for account-level auditing. It is not true that column-level access history is only available through ORGANIZATION_USAGE. This option is factually wrong and confuses account-scoped usage views with organization-wide reporting views.