Access history and audit logging in Snowflake - Step-by-Step Execution

Process Flow - Access history and audit logging

User performs action

↓

Snowflake records event

↓

Event stored in ACCESS_HISTORY view

↓

Admin queries ACCESS_HISTORY

↓

Audit logs reviewed for security/compliance

When a user does something in Snowflake, the system records it in access history. Admins can then query these logs to review actions for security and compliance.

Execution Sample

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE USER_NAME = 'JOHN_DOE'
ORDER BY EVENT_TIMESTAMP DESC
LIMIT 5;

This query fetches the last 5 access events performed by user JOHN_DOE from Snowflake's access history.

Process Table

Step	Action	Query/Command	Result/Output
1	User JOHN_DOE runs a SELECT query	SELECT * FROM SALES;	Event recorded in ACCESS_HISTORY with USER_NAME=JOHN_DOE, QUERY_TYPE=SELECT
2	User JOHN_DOE runs an UPDATE query	UPDATE SALES SET AMOUNT=100 WHERE ID=1;	Event recorded in ACCESS_HISTORY with USER_NAME=JOHN_DOE, QUERY_TYPE=UPDATE
3	Admin queries ACCESS_HISTORY for JOHN_DOE	SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY WHERE USER_NAME='JOHN_DOE' ORDER BY EVENT_TIMESTAMP DESC LIMIT 5;	Returns last 5 events by JOHN_DOE with timestamps and query details
4	Admin reviews audit logs	N/A	Admin sees detailed history of JOHN_DOE's actions for compliance
5	Exit	N/A	No more events to fetch or review

💡 All recent access events for JOHN_DOE retrieved and reviewed

Status Tracker

Variable	Start	After 1	After 2	After 3	Final
USER_NAME	N/A	JOHN_DOE	JOHN_DOE	JOHN_DOE	JOHN_DOE
QUERY_TYPE	N/A	SELECT	UPDATE	N/A	N/A
EVENT_TIMESTAMP	N/A	2024-06-01 10:00:00	2024-06-01 10:05:00	2024-06-01 10:10:00	Latest timestamp from query

Key Moments - 3 Insights

Why do we see multiple entries for the same user in ACCESS_HISTORY?

Each entry records a separate action or query by the user, as shown in execution_table rows 1 and 2 where different queries by JOHN_DOE are logged.

Can ACCESS_HISTORY show actions from all users or just one?

ACCESS_HISTORY contains events for all users, but the query filters by USER_NAME to show only JOHN_DOE's actions, as in execution_table row 3.

What happens if no recent events exist for a user?

The query returns an empty result set, meaning no recorded actions for that user in the queried time frame.

Visual Quiz - 3 Questions

Look at the execution_table, what QUERY_TYPE is recorded at Step 2?

A SELECT

B UPDATE

C INSERT

D DELETE

💡 Hint

Check the QUERY_TYPE column in execution_table row for Step 2

At which step does the admin query the ACCESS_HISTORY view?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look for the step where a SELECT query on ACCESS_HISTORY is executed

If the user JOHN_DOE runs a DELETE query, how would the execution_table change?

A No change because DELETE queries are not logged

B Step 1 QUERY_TYPE would change to DELETE

C A new row with QUERY_TYPE=DELETE would appear after Step 2

D The admin query in Step 3 would fail

💡 Hint

Access history logs all query types; new actions add new rows as in Steps 1 and 2

Concept Snapshot

Snowflake records user actions in ACCESS_HISTORY.
Admins query ACCESS_HISTORY to audit user activity.
Each row logs user, query type, timestamp.
Use filters to find specific user events.
Helps track security and compliance.
Query example: SELECT * FROM ACCESS_HISTORY WHERE USER_NAME='X';

Full Transcript

In Snowflake, every user action like running queries is recorded in a special log called ACCESS_HISTORY. When a user runs a query, Snowflake saves details like who ran it, what type of query it was, and when it happened. Admins can then run queries on ACCESS_HISTORY to see what users have done recently. For example, to see the last 5 actions by user JOHN_DOE, an admin runs a SELECT query filtering by that user. This helps keep track of activity for security and compliance. The execution table shows steps from user actions to admin review. Variables like USER_NAME and QUERY_TYPE change as actions happen. Common confusions include why multiple entries exist for one user (each is a separate action) and that ACCESS_HISTORY logs all users' actions, not just one. Quizzes test understanding of query types logged and when admin queries happen. The quick snapshot summarizes how to use ACCESS_HISTORY for audit logging in Snowflake.

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)