ScyllaDB Data Audit Trail

Data auditing is a crucial aspect of database management, enabling administrators to monitor and track changes, queries, and events within their systems. ScyllaDB offers robust tools for data auditing, ensuring compliance, security, and operational efficiency. This article explores ScyllaDB’s native data audit trail capabilities and the additional benefits of integrating DataSunrise for comprehensive database auditing.

Understanding Data Audit Trails

A data audit trail meticulously captures every operation within a database, documenting activities such as data modifications, executed queries, and administrative actions. This comprehensive record allows organizations to maintain regulatory compliance, swiftly identify and address security vulnerabilities, and analyze historical data for trends and insights, thereby strengthening operational transparency and control.

ScyllaDB facilitates this process through both native capabilities and external tools, enabling effective tracking and analysis of database events for varied business needs.

ScyllaDB Native Data Auditing Features

ScyllaDB Enterprise supports native data auditing by configuring the scylla.yaml file. Administrators can log activities into either a Syslog or a ScyllaDB table, depending on their storage and monitoring preferences.

Enabling ScyllaDB Auditing

To enable auditing, set the audit parameter in the scylla.yaml file:

• none: Disables auditing (default).
• table: Stores audit logs in a ScyllaDB table.
• syslog: Sends audit logs to Syslog.

Example configuration for auditing:

# Audit settings
audit: "table"
# Audit categories
audit_categories: "DCL,DDL,AUTH"
# Specific tables and keyspaces to audit
audit_tables: "mykeyspace.mytable"
audit_keyspaces: "mykeyspace"

Restart the Scylla node to apply changes:

sudo systemctl restart scylla-server

Configurable Audit Parameters

ScyllaDB allows fine-tuning of audit settings using the following parameters:

Parameter	Description
audit_categories	Comma-separated list of event categories.
audit_tables	Tables to audit in <keyspace>.<table> format.
audit_keyspaces	Keyspaces to audit entirely.

Event Categories

• AUTH: Logs login events.
• DML: Tracks data manipulation (INSERT, UPDATE, DELETE).
• DDL: Logs schema changes (CREATE, ALTER, DROP).
• DCL: Logs permissions changes.
• QUERY: Captures all queries (high storage overhead).
• ADMIN: Logs administrative operations.

Viewing Audit Logs

Logs are stored based on the configured method:

Syslog Example

Logs appear in the system’s Syslog under the scylla-audit process name. Example output:

Mar 18 09:53:52 scylla-audit[28387]: "10.143.2.108", "DDL", "ONE", "team_roster", "nba", "DROP TABLE nba.team_roster;", "127.0.0.1", "anonymous", "false"

Table Example

Audit logs in a table can be queried using SQL:

SELECT * FROM audit.audit_log;

Output:

date	category	keyspace_name	operation
2024-12-01 12:00:00	DDL	nba	DROP TABLE nba.team_roster;

Native Data Auditing with SQL

ScyllaDB supports auditing through SQL features such as views and stored procedures. Let’s explore examples.

Using Views for Auditing

Views can track changes by creating an audit view linked to a table:

CREATE MATERIALIZED VIEW audit_view AS
SELECT * FROM mykeyspace.mytable
WHERE timestamp > now() - interval '1 day';

This query captures recent changes for monitoring purposes.

Using Stored Procedures for Auditing

Stored procedures can log events into a custom audit table:

CREATE OR REPLACE FUNCTION log_audit(event TEXT, user TEXT)
RETURNS VOID AS $$
BEGIN
INSERT INTO mykeyspace.audit_log (event, user, event_time)
VALUES (event, user, NOW());
END;
$$ LANGUAGE plpgsql;

Invoke the function during operations to maintain an audit trail.

Connecting to ScyllaDB

To connect and configure audits, use your preferred client, such as cqlsh or a programming language like Python:

from cassandra.cluster import Cluster
cluster = Cluster(["127.0.0.1"])
session = cluster.connect("mykeyspace")
session.execute("ALTER TABLE mytable ...")

DataSunrise Integration for ScyllaDB Auditing

DataSunrise enhances ScyllaDB’s auditing with advanced features, providing centralized control and detailed analytics.

Configuring DataSunrise for ScyllaDB Auditing

1. Create an Instance: Launch a new DataSunrise instance and connect to your ScyllaDB database.
2. Set Up Filters: Define filter statements for auditing:
• Object Group: Monitor specific database objects.

• Query Group: Filter queries based on types (DML, DDL).

• Query Types: Detect SQL injection attempts.

• SQL Injection:

• Session Events: Capture user session activities.

Viewing Audit Data in DataSunrise

Audit results are available in the DataSunrise dashboard. Export logs or generate reports for analysis.

Benefits of DataSunrise

• Centralized Control: Uniform rules across multiple databases.
• Compliance Assurance: Simplifies meeting regulatory requirements.
• Enhanced Security: Detects anomalous behaviors, including SQL injections.

Conclusion

Implementing a ScyllaDB data audit trail is a pivotal step toward robust database security and operational oversight. Native ScyllaDB auditing features provide precise and configurable mechanisms to monitor user actions and database changes, ensuring compliance and uncovering potential vulnerabilities. By leveraging SQL-based tools like views and stored procedures, administrators can build custom, adaptable auditing processes tailored to organizational needs.

Integrating DataSunrise elevates this capability further by offering centralized auditing and security. The platform’s advanced filters and session tracking ensure a uniform approach to data governance across multiple systems, enhancing compliance and operational efficiency. Additionally, DataSunrise’s analytics and detailed reporting simplify the identification of anomalies and threats.

To explore how DataSunrise can streamline and enhance your database auditing strategy, visit the official website and discover its comprehensive features through an online demonstration.