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.