Access history and audit logging in Snowflake - Commands & Configuration

Introduction

Access history and audit logging help you see who did what and when in your Snowflake account. This is important to keep your data safe and understand user actions.

When you want to check who accessed sensitive data in your Snowflake account.

When you need to find out if someone made changes to your database objects.

When you want to track failed login attempts to detect possible security issues.

When you need to comply with company policies or legal regulations about data access.

When you want to monitor usage patterns to optimize your Snowflake resources.

Commands

This command retrieves the last 5 access events in your Snowflake account, showing who accessed what and when.

Terminal

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ORDER BY EVENT_TIMESTAMP DESC LIMIT 5;

Expected Output

QUERY_ID                             | EVENT_TIMESTAMP       | USER_NAME | OBJECT_NAME | OBJECT_DOMAIN | CLIENT_IP   

-----------------------------------|----------------------|-----------|-------------|---------------|------------

01a2b3c4-d5e6-789f-0123-456789abcdef | 2024-06-01 12:34:56  | JDOE      | SALES_DATA  | TABLE         | 192.168.1.10

02b3c4d5-e6f7-890a-1234-56789abcdef0 | 2024-06-01 12:30:22  | AMILLER   | CUSTOMER_DB | DATABASE      | 192.168.1.11

03c4d5e6-f7a8-901b-2345-6789abcdef01 | 2024-06-01 12:25:10  | JDOE      | ORDERS      | TABLE         | 192.168.1.10

04d5e6f7-a8b9-012c-3456-789abcdef012 | 2024-06-01 12:20:05  | LSMITH    | INVENTORY   | TABLE         | 192.168.1.12

05e6f7a8-b9c0-123d-4567-89abcdef0123 | 2024-06-01 12:15:00  | AMILLER   | SALES_DATA  | TABLE         | 192.168.1.11

This command shows the last 5 login attempts to your Snowflake account, including successes and failures.

Terminal

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY ORDER BY EVENT_TIMESTAMP DESC LIMIT 5;

Expected Output

EVENT_TIMESTAMP       | USER_NAME | SUCCESS | CLIENT_IP   

----------------------|-----------|---------|------------

2024-06-01 12:35:00   | JDOE      | TRUE    | 192.168.1.10

2024-06-01 12:33:45   | AMILLER   | FALSE   | 192.168.1.11

2024-06-01 12:30:00   | LSMITH    | TRUE    | 192.168.1.12

2024-06-01 12:25:30   | JDOE      | TRUE    | 192.168.1.10

2024-06-01 12:20:15   | AMILLER   | TRUE    | 192.168.1.11

This command fetches the last 3 queries run by user JDOE to audit their activity.

Terminal

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = 'JDOE' ORDER BY START_TIME DESC LIMIT 3;

Expected Output

QUERY_ID                             | START_TIME           | QUERY_TEXT                         | ROWS_PRODUCED

-----------------------------------|----------------------|----------------------------------|--------------

0a1b2c3d-4e5f-6789-0abc-def123456789 | 2024-06-01 12:34:00  | SELECT * FROM SALES_DATA;          | 1000

1b2c3d4e-5f67-890a-bcde-f1234567890a | 2024-06-01 12:20:00  | UPDATE ORDERS SET STATUS='SHIPPED' | 50

2c3d4e5f-6789-0abc-def1-234567890abc | 2024-06-01 12:10:00  | DELETE FROM INVENTORY WHERE QTY=0 | 10

Key Concept

If you remember nothing else from this pattern, remember: Snowflake's ACCOUNT_USAGE views let you see detailed records of who accessed your data and when.

Common Mistakes

Trying to query access history without the ACCOUNTADMIN role or proper privileges.

You will get an error or empty results because access to these views is restricted for security.

Use a role with the necessary privileges like ACCOUNTADMIN or a custom role granted access to ACCOUNT_USAGE views.

Assuming access history shows real-time data immediately.

The data in ACCOUNT_USAGE views can be delayed by up to 45 minutes.

Plan audits accordingly and understand that recent events may not appear instantly.

Not filtering or limiting query results, causing very large outputs.

This can slow down queries and make it hard to find relevant information.

Always use filters like USER_NAME, time ranges, or LIMIT clauses to focus your audit.

Summary

Use SELECT queries on SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY to see who accessed data and when.

Check SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY to monitor login attempts and detect failures.

Filter and limit your queries to get relevant audit information efficiently.

Practice

1. What does the ACCESS_HISTORY view in Snowflake primarily provide?

easy

A. Information about who accessed data and when

B. Details about Snowflake account billing

C. List of all database tables and schemas

D. Performance metrics of queries

Solution

1. Step 1: Understand the purpose of ACCESS_HISTORY

   The ACCESS_HISTORY view records user activity related to data access in Snowflake.

2. Step 2: Identify what ACCESS_HISTORY tracks

   It tracks who accessed what data and when, useful for audit and compliance.

3. Final Answer:

   Information about who accessed data and when -> Option A

4. Quick Check:

   ACCESS_HISTORY = Data access logs [OK]

Hint: ACCESS_HISTORY logs user data access events only [OK]

Common Mistakes:

• Confusing ACCESS_HISTORY with billing or performance views
• Thinking it shows database structure
• Assuming it tracks query performance

2. Which of the following is the correct syntax to query the ACCESS_HISTORY view for all accesses in the last 7 days?

easy

A. SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME = CURRENT_DATE - 7;

B. SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME < CURRENT_DATE + 7;

C. SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME > CURRENT_DATE - 7;

D. SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME BETWEEN CURRENT_DATE AND CURRENT_DATE + 7;

Solution

1. Step 1: Understand date filtering in Snowflake SQL

   To get records from the last 7 days, filter where ACCESS_TIME is greater than current date minus 7 days.

2. Step 2: Check each option's date condition

   SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME > CURRENT_DATE - 7; correctly uses ACCESS_TIME > CURRENT_DATE - 7 to get recent records.

3. Final Answer:

   SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME > CURRENT_DATE - 7; -> Option C

4. Quick Check:

   Date filter for last 7 days = SELECT * FROM ACCESS_HISTORY WHERE ACCESS_TIME > CURRENT_DATE - 7; [OK]

Hint: Use > CURRENT_DATE - 7 for last 7 days filter [OK]

Common Mistakes:

• Using < instead of > for recent dates
• Using = which matches only one day
• Using BETWEEN with future dates incorrectly

3. What will be the result of this query?

SELECT USER_NAME, COUNT(*) AS ACCESS_COUNT
FROM ACCESS_HISTORY
WHERE OBJECT_NAME = 'CUSTOMERS'
GROUP BY USER_NAME
ORDER BY ACCESS_COUNT DESC
LIMIT 3;

medium

A. All users who accessed any object named CUSTOMERS

B. An error because OBJECT_NAME is not a valid column

C. The total number of accesses to all objects

D. Top 3 users who accessed the CUSTOMERS object most frequently

Solution

1. Step 1: Analyze the query components

   The query counts accesses per user filtered by OBJECT_NAME = 'CUSTOMERS'.

2. Step 2: Understand GROUP BY and ORDER BY

   It groups by USER_NAME, orders by count descending, and limits to top 3 users.

3. Final Answer:

   Top 3 users who accessed the CUSTOMERS object most frequently -> Option D

4. Quick Check:

   GROUP BY USER_NAME with LIMIT 3 = Top 3 users who accessed the CUSTOMERS object most frequently [OK]

Hint: GROUP BY user and LIMIT 3 shows top 3 users [OK]

Common Mistakes:

• Thinking it lists all users without limit
• Assuming OBJECT_NAME is invalid
• Confusing total accesses with per-user counts

4. You run this query but get an error:

SELECT * FROM ACCESS_HISTORY WHERE USER = 'JOHN';

What is the likely cause and fix?

medium

A. Column USER does not exist; use USER_NAME instead

B. ACCESS_HISTORY view is not accessible to your role

C. Syntax error due to missing semicolon

D. USER is a reserved keyword; enclose in double quotes

Solution

1. Step 1: Check column names in ACCESS_HISTORY

   The correct column for user is USER_NAME, not USER.

2. Step 2: Correct the query column name

   Replace USER with USER_NAME to fix the error.

3. Final Answer:

   Column USER does not exist; use USER_NAME instead -> Option A

4. Quick Check:

   Use USER_NAME column, not USER [OK]

Hint: Use USER_NAME, not USER, as column name [OK]

Common Mistakes:

• Using USER instead of USER_NAME
• Assuming missing semicolon causes error
• Thinking ACCESS_HISTORY is inaccessible

5. You want to audit all SELECT queries on the SALES table in the last 30 days. Which query correctly retrieves this from ACCESS_HISTORY?

hard

A. SELECT QUERY_TEXT, USER_NAME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TYPE = 'INSERT' AND ACCESS_TIME > CURRENT_DATE - 30;

B. SELECT QUERY_TEXT, USER_NAME, ACCESS_TIME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TYPE = 'SELECT' AND ACCESS_TIME > CURRENT_DATE - 30;

C. SELECT * FROM ACCESS_HISTORY WHERE TABLE_NAME = 'SALES' AND QUERY_TYPE = 'SELECT' AND ACCESS_TIME > CURRENT_DATE - 30;

D. SELECT QUERY_TEXT, USER_NAME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TIME < CURRENT_DATE - 30;

Solution

1. Step 1: Identify correct column names and filters

   ACCESS_HISTORY uses OBJECT_NAME for table, ACCESS_TYPE for operation, and ACCESS_TIME for timestamp.

2. Step 2: Check filter conditions for SELECT and last 30 days

   SELECT QUERY_TEXT, USER_NAME, ACCESS_TIME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TYPE = 'SELECT' AND ACCESS_TIME > CURRENT_DATE - 30; filters OBJECT_NAME = 'SALES', ACCESS_TYPE = 'SELECT', and ACCESS_TIME > CURRENT_DATE - 30 correctly.

3. Final Answer:

   SELECT QUERY_TEXT, USER_NAME, ACCESS_TIME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TYPE = 'SELECT' AND ACCESS_TIME > CURRENT_DATE - 30; -> Option B

4. Quick Check:

   Correct columns and filters = SELECT QUERY_TEXT, USER_NAME, ACCESS_TIME FROM ACCESS_HISTORY WHERE OBJECT_NAME = 'SALES' AND ACCESS_TYPE = 'SELECT' AND ACCESS_TIME > CURRENT_DATE - 30; [OK]

Hint: Filter by OBJECT_NAME, ACCESS_TYPE, and recent ACCESS_TIME [OK]

Common Mistakes:

• Using TABLE_NAME or QUERY_TYPE which don't exist
• Filtering for INSERT instead of SELECT
• Using ACCESS_TIME < CURRENT_DATE - 30 (old data)