Home
Knowledge Center
Snowflake Database Activity History

Snowflake Database Activity History

Monitoring and understanding Snowflake Database Activity History enables organizations to maintain robust security and compliance standards. Recent findings from ENISA’s Threat Landscape 2024 highlight the growing importance of this capability: ransomware attacks targeting databases increased by 128% in the past year, while 47% of all data breaches originated from unauthorized database access. Through comprehensive tracking and analysis of Snowflake database activity history, organizations can effectively identify suspicious patterns, maintain audit trails, and prevent potential security incidents before they escalate.

What is Snowflake Database Activity History?

Snowflake Database Activity History refers to the record of all actions performed within a Snowflake environment. This includes details about who accessed the system, what actions they performed, when they occurred, and the impact of those actions. Additionally, monitoring this activity is crucial for keeping your data safe and helps you follow rules like GDPR, HIPAA, and PCI-DSS.

Snowflake provides numerous built-in features that help database administrators track activities. As a result, they can maintain a clear audit trail with Snowflake’s native Database Activity History, tracking query executions, user access, login attempts, and data changes within one central platform.

Native Database Activity History in Snowflake

Snowflake provides comprehensive native tools for tracking database activities. These features help security teams monitor user actions and maintain audit trails effectively.

Using SQL for Auditing

Snowflake offers native auditing tools that use SQL to capture detailed logs of user activity. Subsequently, these tools track operations on databases, tables, and specific rows of data.

Example 1: Query History

The QUERY_HISTORY function serves as a primary tool for auditing activity in Snowflake. This built-in view shows a history of all queries run in your Snowflake account, displaying user, timestamp, and execution status information.

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > '2024-01-01'
ORDER BY START_TIME DESC;

This query provides a clear view of database activities, helping you monitor data access and changes.

Example 2: Access History

Additionally, Snowflake provides the ACCESS_HISTORY view for tracking specific data access. This tool monitors sensitive data access patterns and operation details.

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE TABLE_NAME = 'customer_data'
ORDER BY ACCESS_TIME DESC;

This query helps identify who accessed sensitive data and when these actions occurred.

Using Snowflake Views for Auditing

Furthermore, Snowflake’s information schema views play an essential role in auditing. These views monitor different parts of the Snowflake environment, tracking changes to schemas, tables, and database objects.

SELECT * 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'public';

This query examines tables within a particular schema. Custom queries help track schema changes and user activities, identifying unexpected changes or unauthorized access.

Using Snowflake Stored Procedures

Stored procedures in Snowflake automate data audit tasks effectively. These procedures collect audit information and trigger alerts when specific conditions occur. Here’s a basic stored procedure example:

CREATE OR REPLACE PROCEDURE audit_table_changes()
  RETURNS STRING
  LANGUAGE SQL
  AS
  $$
  BEGIN
    INSERT INTO audit_log (user, action, timestamp)
    SELECT CURRENT_USER(), 'UPDATE', CURRENT_TIMESTAMP();
  END;
  $$;

This stored procedure logs user actions in an audit_log table. You can schedule it to run automatically, ensuring continuous audit data collection.

Snowflake CLI for Auditing

The Snowflake CLI (SnowSQL) provides a hands-on approach to run SQL queries and retrieve audit data. This tool allows query execution and audit log exports for analysis.

Example of connecting to Snowflake using SnowSQL:

snowsql -a <account> -u <user> -r <role> -d <database> -s <schema>

After connecting, you can run queries for QUERY_HISTORY or ACCESS_HISTORY to collect and analyze audit data.

Using Snowflake Web Interface for Activity Monitoring

The Snowflake web-based UI offers an intuitive way to access activity history:

Log into your Snowflake account
Navigate to “Monitoring”
Click on “Query History”

The interface provides detailed information about database queries, with filters available for:

Time Range
User
Warehouse
Query Status

Selecting any query reveals:

Full SQL text
Query plan
Execution metrics
Resource consumption

Enhancing Security with DataSunrise

While Snowflake’s Database Activity History features provide strong security, integrating DataSunrise enhances monitoring capabilities. This comprehensive security suite helps organizations secure sensitive data, ensure compliance, and streamline auditing processes.

Creating a DataSunrise Instance

Setting up a new instance for Snowflake monitoring involves these steps:

Log into DataSunrise: Access the DataSunrise interface with your account.
Create a New Instance: From the dashboard, add a new Snowflake instance with your connection details.

Configure Audit Rule: Create an audit rule to track specific database activities.

View Activity Logs: Access detailed event information through the interface.

View Detailed Information About Event in DataSunrise

Benefits of Using DataSunrise for Sensitive Data Protection

DataSunrise’s security suite enhances Snowflake native Database Activity History tracking through:

Centralized Control: Manage all masking and auditing policies from a single interface.
Data Masking: Sensitive data protection through automatic masking.
Comprehensive Auditing: Advanced tracking of query executions, sensitive data access, and system modifications.
Regulatory Compliance: Automated compliance management for GDPR, HIPAA, and PCI-DSS standards.

Best Practices for Database Activity History Management

Effective database activity history management requires specific attention in several key areas.

Retention Management and Compliance: Organizations must establish clear retention policies aligned with regulatory requirements. Essential practices include storing login history for 365 days, maintaining query history for audits, and archiving access patterns. Furthermore, regulated industries follow specific timeframes: 7 years for financial services (SOX), 6 years for healthcare (HIPAA), and 5 years for personal data (GDPR).

Security Monitoring and Alert Systems: Robust alert mechanisms form the foundation of security monitoring. Key practices include monitoring failed login attempts, tracking large data exports, and identifying unusual access patterns. Security teams should implement real-time alerts with 15-minute response protocols for critical events.

Access Pattern Analysis: Regular pattern analysis reveals security insights through weekly access reports, query pattern monitoring, and resource consumption analysis. Teams should conduct monthly cross-database access reviews and quarterly path investigations.

Documentation and Standardization: Comprehensive documentation ensures consistent monitoring. This includes alert handling procedures, response protocols, and clear escalation paths, supported by updated security runbooks and training materials.

Third-Party Solution Integration: Solutions like DataSunrise provide advanced monitoring features through centralized security management, threat detection, and automated compliance reporting. These tools improve response times and reporting efficiency.

Conclusion

In today’s evolving cybersecurity landscape, robust database activity monitoring serves as a critical business necessity. While Snowflake’s native capabilities provide strong monitoring features, integration with specialized solutions like DataSunrise enhances overall security posture. This comprehensive approach enables organizations to maintain security, ensure compliance, and protect sensitive data effectively.

DataSunrise’s security suite strengthens protection through centralized control over data masking and security rules. Through advanced monitoring, threat detection, and automated compliance reporting, organizations can build strong defenses against emerging threats. To explore these capabilities and strengthen your database security strategy, consider scheduling an online demo through our website.