ScyllaDB Database Activity History

Database auditing is a cornerstone of modern data management, offering insights into user activities and data changes for enhanced security and compliance. ScyllaDB, a high-performance NoSQL database, provides robust auditing features to monitor database activity. This article delves into the native database auditing capabilities of ScyllaDB, including configuration and usage examples. We also explore the extended database activity history features provided by DataSunrise, a comprehensive database security tool.

Native ScyllaDB Database Activity History

ScyllaDB’s native auditing tools allow administrators to monitor queries, data changes, and other database activities. These features, available only in Scylla Enterprise, support auditing via Syslog or dedicated Scylla tables.

Configuring Auditing in ScyllaDB

Auditing in ScyllaDB is enabled and configured through the scylla.yaml file. The following steps outline the process:

1. Enable Auditing
   
   

Open the scylla.yaml file and set the audit parameter to table or syslog. For example:

 audit: "table"
audit_categories: "DCL,DDL,AUTH"
audit_keyspaces: "mykeyspace"

Restart the Scylla server:


 sudo systemctl restart scylla-server

2. Audit Categories ScyllaDB supports the following audit categories:
   
   
• AUTH: Logs login events.
• DML: Logs data manipulation operations (e.g., INSERT, UPDATE).
• DDL: Logs schema-altering events (e.g., CREATE, DROP).
• DCL: Logs permission changes (e.g., GRANT, REVOKE).
• QUERY: Logs all queries.
• ADMIN: Logs administrative operations.

Audit Keyspaces and Tables Specify keyspaces and tables to audit:



 audit_tables: "mykeyspace.mytable"

3.  Leave audit_tables empty to audit all tables in the specified keyspaces.
   
   

Viewing Audit Logs

ScyllaDB stores audit logs either in a table or Syslog. Below are examples of accessing these logs:

Table-Based Audit Logs

Audit logs stored in the audit.audit_log table can be queried with SQL:

SELECT * FROM audit.audit_log;

Example output:

Syslog-Based Audit Logs

Syslog logs can be redirected to a file by configuring rsyslog on CentOS:

sudo dnf install rsyslog
sudo echo "if $programname contains 'scylla-audit' then /var/log/scylla-audit.log" >> /etc/rsyslog.conf
sudo systemctl start rsyslog
sudo systemctl enable rsyslog

Audit messages will appear in /var/log/scylla-audit.log.

Example: Tracking Table Drops

Enable auditing for DDL operations in the scylla.yaml file:

audit: "table"
audit_categories: "DDL"
audit_keyspaces: "mykeyspace"

Restart Scylla and perform a table drop operation:

DROP TABLE mykeyspace.mytable;

Query the audit.audit_log table to view the event:

SELECT * FROM audit.audit_log WHERE operation LIKE 'DROP TABLE%';

Enhanced Auditing with DataSunrise

DataSunrise extends ScyllaDB’s auditing capabilities by providing centralized management, dynamic auditing, and advanced reporting. Below, we outline how to configure DataSunrise for ScyllaDB auditing.

Setting Up DataSunrise for ScyllaDB

1. Create a DataSunrise Instance
   
   
• Log in to the DataSunrise management console.
• Add a new instance and configure the proxy to connect to your ScyllaDB cluster.
• Use the dynamic proxy mode for real-time auditing or static mode for periodic snapshots.


ScyllaDB instance type selection among other supported databases in DataSunrise

2. Configure Audit Rules
   
   
• Define auditing rules for ScyllaDB in the DataSunrise interface. For example:
• Monitor specific SQL commands.
• Track access to sensitive data.


configuring datasunrise auditing on failed login attempts

3. View Audit Results
   
   
• Navigate to the “Audit Logs” section in DataSunrise to view comprehensive activity logs.
• Filter logs by user, event type, or time range for detailed analysis.

Benefits of Using DataSunrise

• Centralized Management: Manage auditing rules for multiple databases from a single console.
• Dynamic Auditing: Monitor real-time database activity without impacting performance.
• Advanced Reporting: Generate detailed compliance reports.
• Security Integration: Implement data masking, access control, and threat detection alongside auditing.

Example: Auditing Data Access

To audit SELECT queries on sensitive tables:

Define a rule in DataSunrise to track SELECT statements.

Access the table via SQL:

SELECT * FROM sensitive_table;

View the audit log in DataSunrise:
Time: 2024-01-01 12:00:00

User: admin

Query: SELECT * FROM sensitive_table;

Conclusion

ScyllaDB’s native auditing features and the extended capabilities of DataSunrise provide powerful tools for monitoring database activity and ensuring compliance. While ScyllaDB offers detailed auditing options through SQL-based configurations, DataSunrise enhances these capabilities with centralized management and advanced analytics.

In addition to its robust audit capabilities, DataSunrise provides comprehensive security features, including security rules, data masking, sensitive data discovery, vulnerability assessments, and more. To learn more about DataSunrise’s database security suite, including its auditing and compliance features, visit our website for an online demonstration.