Access history and audit logging in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

💻 Command Output

intermediate

Querying Access History for a User

What is the output of this query that retrieves access history for user 'JOHN_DOE' in the last 7 days?

Snowflake

SELECT event_time, user_name, object_name, action FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE user_name = 'JOHN_DOE' AND event_time >= DATEADD(day, -7, CURRENT_TIMESTAMP());

A

[
  {"event_time": "2024-06-10 14:23:01", "user_name": "JOHN_DOE", "object_name": "SALES_DATA", "action": "SELECT"},
  {"event_time": "2024-06-12 09:15:45", "user_name": "JOHN_DOE", "object_name": "CUSTOMERS", "action": "INSERT"}
]

B SyntaxError: missing FROM keyword

C Error: Table SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY does not exist

D []

💡 Hint

Check the table name and filter conditions carefully.

The query correctly filters access history for user 'JOHN_DOE' in the last 7 days, returning matching events.

🧠 Conceptual

intermediate

Purpose of Access History in Snowflake

What is the primary purpose of the ACCESS_HISTORY view in Snowflake?

A To store user passwords and authentication details

B To monitor system resource usage like CPU and memory

C To track all user activities on database objects for auditing and compliance

D To manage user roles and permissions

💡 Hint

Think about what audit logging means in a database context.

ACCESS_HISTORY records user actions on database objects, helping with auditing and compliance.

❓ Troubleshoot

advanced

Troubleshooting Missing Audit Logs

You notice that recent user activities are not appearing in the ACCESS_HISTORY view. What could be a likely cause?

A The ACCOUNT_USAGE schema is not enabled or data sharing is delayed

B User has no SELECT privileges on the ACCESS_HISTORY view

C The Snowflake account is in a paused state

D The ACCESS_HISTORY view only shows data older than 30 days

💡 Hint

Consider how Snowflake replicates usage data to ACCOUNT_USAGE.

ACCOUNT_USAGE views can have a delay of up to 45 minutes or more before recent data appears.

🔀 Workflow

advanced

Setting Up Audit Logging for Compliance

Which sequence of steps correctly sets up audit logging access for a security team in Snowflake?

A 2,1,3,4

B 1,2,3,4

C 1,3,2,4

D 3,1,2,4

💡 Hint

Think about granting access before verification.

First create the role with privileges, then grant it, verify access, and finally monitor logs.

✅ Best Practice

expert

Best Practice for Retaining Audit Logs

What is the recommended best practice for retaining audit logs in Snowflake for long-term compliance?

A Disable ACCESS_HISTORY to improve query performance

B Rely solely on the default retention period of ACCESS_HISTORY in Snowflake

C Delete old audit logs manually to save storage

D Periodically export ACCESS_HISTORY data to an external secure storage like AWS S3

💡 Hint

Think about compliance requirements for long-term data retention.

Snowflake's default retention may not meet all compliance needs; exporting logs externally is best practice.