SnowPro Advanced: Security Engineer Question 367
Single answerACCOUNT_USAGE viewsA security engineer must produce a daily report showing all grants of highly privileged roles and direct object privileges to users and roles across the Snowflake account. The report is generated from a centralized auditing database and should avoid querying every individual database. The engineer also knows the report may not need to be real-time, but it must be based on Snowflake-provided historical metadata rather than custom logging. Which approach best meets these requirements?
- A
Query SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS, SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES, and related ACCOUNT_USAGE views from the centralized auditing database, accounting for the documented latency in ACCOUNT_USAGE data.
- B
Query INFORMATION_SCHEMA views in each user database because they provide account-wide historical grant data with no latency, making them the best source for centralized security reporting.
- C
Enable a task to copy SHOW GRANTS output into custom tables every minute because Snowflake does not expose grant history through SNOWFLAKE.ACCOUNT_USAGE views.
- D
Query only SNOWFLAKE.ACCOUNT_USAGE.USERS because role and privilege assignments are fully denormalized there for historical security analysis.
Show answer and explanation
Correct answer: A
Explanation
For account-wide security auditing, Snowflake's SNOWFLAKE.ACCOUNT_USAGE schema is the primary built-in source for historical metadata analysis. It is specifically suited to centralized reporting because it avoids the need to iterate through each database's INFORMATION_SCHEMA. In a grants-focused scenario, the engineer should use the relevant grant-oriented ACCOUNT_USAGE views, such as GRANTS_TO_USERS and GRANTS_TO_ROLES, and design the report with the known ACCOUNT_USAGE latency in mind. This is a common exam theme: choosing the correct metadata source based on scope and timeliness requirements. INFORMATION_SCHEMA is useful for database-scoped metadata access, but it is not the preferred source for centralized account-level historical security reporting. Snowflake documentation for ACCOUNT_USAGE and metadata views notes both the account-level scope and the potential latency of these views, which is why ACCOUNT_USAGE is appropriate here when near-real-time data is not required.
- A. Correct.
Correct. ACCOUNT_USAGE is the appropriate Snowflake-provided source for centralized historical metadata reporting across the account, including grant-related analysis. For security reporting, views such as GRANTS_TO_USERS and GRANTS_TO_ROLES are designed to support account-level auditing use cases without requiring queries against every database's INFORMATION_SCHEMA. A key implementation detail is that ACCOUNT_USAGE data is not real-time and has documented latency, so the reporting design must tolerate that delay.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA is scoped at the database level and is not the best fit for centralized account-wide historical reporting. It also does not provide the same account-level historical auditing model as ACCOUNT_USAGE. A common misconception is that INFORMATION_SCHEMA is simply a lower-latency version of ACCOUNT_USAGE for all use cases; in practice, for cross-account security audit reporting, ACCOUNT_USAGE is the standard Snowflake-provided source.
- C. Incorrect.
Incorrect. SHOW GRANTS can be useful operationally, but the statement that Snowflake does not expose grant history through ACCOUNT_USAGE is false. Building a custom pipeline from SHOW output may be unnecessary when Snowflake already provides relevant metadata views in SNOWFLAKE.ACCOUNT_USAGE. This option reflects a common mistake of overengineering an audit solution instead of using built-in account usage views.
- D. Incorrect.
Incorrect. The USERS view does not replace the dedicated grant relationship views for role assignments and privilege analysis. It contains user metadata, but not a complete denormalized historical representation of all role grants and object privilege grants. Relying only on USERS would miss the actual grant relationships needed for a defensible security audit.