Access history and auditing in Snowflake represents a comprehensive security framework that enables organizations to monitor, track, and analyze all activities occurring within their Snowflake environment. This functionality is essential for maintaining compliance, detecting potential security thre…Access history and auditing in Snowflake represents a comprehensive security framework that enables organizations to monitor, track, and analyze all activities occurring within their Snowflake environment. This functionality is essential for maintaining compliance, detecting potential security threats, and understanding user behavior patterns.
Snowflake provides several key features for access history and auditing. The ACCOUNT_USAGE schema contains views that store historical data about account activities for up to one year. Key views include ACCESS_HISTORY, which tracks all data access operations including SELECT statements and data manipulation activities. This view captures what data was accessed, who accessed it, and when the access occurred.
The QUERY_HISTORY view maintains records of all queries executed within the account, including execution time, warehouse used, and query status. This helps administrators identify performance issues and unusual query patterns that might indicate security concerns.
LOGIN_HISTORY tracks all authentication attempts, both successful and failed, providing visibility into potential unauthorized access attempts. This is crucial for identifying brute force attacks or compromised credentials.
SESSION_HISTORY provides details about user sessions, including connection information and session duration, helping track user activity patterns.
For real-time monitoring, the INFORMATION_SCHEMA provides similar views but with a shorter retention period of seven days to two weeks, depending on the specific view.
Snowflake also supports integration with external SIEM (Security Information and Event Management) tools through data sharing and export capabilities, allowing organizations to correlate Snowflake activities with other enterprise security events.
Role-based access control ensures that only authorized personnel can view audit information. The ACCOUNTADMIN role typically has full access to these monitoring capabilities, while custom roles can be configured for security teams requiring limited audit access.
These auditing capabilities help organizations meet regulatory requirements such as GDPR, HIPAA, and SOC 2 compliance standards.
Access History and Auditing in Snowflake
Why Access History and Auditing is Important
Access history and auditing are critical components of data governance and security in Snowflake. Organizations must track who accessed what data, when they accessed it, and what operations they performed. This information is essential for regulatory compliance (GDPR, HIPAA, SOX), security investigations, and understanding data usage patterns across the organization.
What is Access History in Snowflake?
Snowflake provides comprehensive auditing capabilities through the Account Usage schema in the SNOWFLAKE database. The key views for access history include:
• ACCESS_HISTORY - Tracks read and write operations on tables, views, and columns including data lineage • LOGIN_HISTORY - Records all login attempts (successful and failed) to the account • QUERY_HISTORY - Contains details of all queries executed in the account • SESSIONS - Information about user sessions
How Access History Works
The ACCESS_HISTORY view captures: • Base objects accessed - The actual tables and views that were read or modified • Direct objects accessed - The objects referenced in the query • Column-level tracking - Specific columns that were accessed • Data lineage - Tracks data flow from source to destination
Data in Account Usage views has a latency of up to 2 hours before appearing. For near real-time data, use the INFORMATION_SCHEMA, which has no latency but limited retention (7 days to 6 months depending on the view).
Key Differences: Account Usage vs Information Schema
• Account Usage: Up to 365 days retention, 45 minutes to 2 hours latency, dropped objects included • Information Schema: 7 days to 6 months retention, real-time or near real-time, dropped objects excluded
Required Privileges
To access Account Usage views, users need the IMPORTED PRIVILEGES granted on the SNOWFLAKE database. Only the ACCOUNTADMIN role has this by default.
Exam Tips: Answering Questions on Access History and Auditing
1. Remember the latency: Account Usage views have latency (up to 2 hours), while Information Schema is real-time. Questions often test this distinction.
2. Know the retention periods: Account Usage retains data for up to 365 days, while Information Schema varies by view type.
3. Understand ACCESS_HISTORY specifics: This view tracks both read and write operations and provides column-level lineage information.
4. LOGIN_HISTORY details: Remember it captures both successful and failed login attempts, useful for security monitoring.
5. Privilege requirements: IMPORTED PRIVILEGES on the SNOWFLAKE database is required to query Account Usage views.
6. Location matters: Account Usage is in SNOWFLAKE.ACCOUNT_USAGE schema, while Information Schema is in each database.
7. Dropped objects: Account Usage includes information about dropped objects; Information Schema does not.
9. Common exam scenarios: Questions may ask about the best view to use for compliance reporting (Account Usage due to longer retention) versus real-time monitoring (Information Schema).
10. Data lineage: The ACCESS_HISTORY view is the primary source for understanding data lineage and tracking sensitive data access across your Snowflake environment.