SnowPro Advanced: Security Engineer Question 222
Single answerConduct periodic audits of replication configurationsA security engineering team uses Snowflake database replication to maintain a read-only copy of a production database in a secondary region for disaster recovery. During a quarterly security audit, the team must verify that the replication configuration still aligns with policy: only approved databases are replicated, replication targets are still authorized, and failover/replication privileges have not drifted to unintended roles. Which approach provides the most effective periodic audit of the replication configuration?
- A
Review account metadata for replication and failover groups using SHOW commands and ACCOUNT_USAGE views, then validate which objects are included, the target accounts/regions, and which roles hold replication-related privileges.
- B
Query QUERY_HISTORY for the last 90 days to identify replication events and assume any database not refreshed recently is not part of replication.
- C
Inspect only the secondary database in the target region; if it exists and is read-only, the replication configuration is compliant.
- D
Rotate all database object ownership to SECURITYADMIN before the audit because ownership automatically reveals all replication relationships and removes privilege drift.
Show answer and explanation
Correct answer: A
Explanation
For periodic audits of Snowflake replication configurations, the strongest approach is to inspect current metadata and grants rather than infer state from usage patterns or object existence. Security teams should validate: (1) which databases, replication groups, or failover groups are configured; (2) which target accounts/regions are defined; and (3) which roles have replication- and failover-related privileges. In Snowflake, SHOW commands and Snowflake-provided metadata views in ACCOUNT_USAGE are the primary mechanisms for this type of verification, supplemented by review of GRANTS to detect privilege drift. This aligns with least privilege and change-audit best practices. By contrast, QUERY_HISTORY is useful for operational troubleshooting but not for proving the current authorized configuration, and checking only the secondary side misses source-side definitions and grants.
- A. Correct.
Correct. A periodic audit should use authoritative Snowflake metadata and privilege information rather than inference. In practice, security engineers review replication/failover configuration with SHOW commands and applicable ACCOUNT_USAGE views to determine what is being replicated, where it is replicated, and which principals have privileges such as MONITOR, REPLICATE, or FAILOVER where relevant. This directly supports auditing approved scope, authorized destinations, and role-based access drift.
- B. Incorrect.
Incorrect. QUERY_HISTORY shows executed statements, not the complete current replication configuration. A database might be configured for replication but not refreshed within the time window, and historical queries do not reliably prove current authorized targets or current privilege assignments. This is a common mistake: auditing activity instead of configuration state.
- C. Incorrect.
Incorrect. The existence of a secondary database only proves that a replica exists, not that the overall replication configuration is compliant. It does not confirm whether only approved objects are included, whether all target accounts/regions remain authorized, or whether replication/failover privileges have drifted. It also ignores metadata at the source side where replication is defined and administered.
- D. Incorrect.
Incorrect. Changing ownership broadly is not an audit control and can create unnecessary risk and operational impact. Ownership does not automatically provide a clean inventory of replication relationships, and rotating ownership is not a recommended method for detecting privilege drift. Audits should be read-oriented and based on metadata and grants, not disruptive privilege changes.