Database Audit for Percona Server

Introduction

In this article, we’ll explore database audit for Percona Server (MySQL), focusing on data and query auditing, and how tools like DataSunrise can enhance your overall database security strategy.

Did you know that recent cybersecurity reports reveal threat actors prefer efficient data theft methods like Business Email Compromise (BEC)? This trend highlights poorly managed data as a prime target. Such alarming insights underscore the critical importance of robust database auditing.

Percona MySQL Audit Capabilities

Native Audit Plugin

Percona Server for MySQL comes with a built-in audit plugin that offers basic auditing capabilities. This plugin allows you to track various database events and user activities.

To enable the audit plugin, you can use the following command:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Check if the plugin is active:
SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';

Once enabled, you can configure the plugin using system variables. For example:

SET GLOBAL audit_log_file = '/var/log/mysql/audit.log';
SET GLOBAL audit_log_policy = 'ALL';

First one (audit_log_file) is read only if you set up Percona Server with Docker Image. These settings define the log file location and audit policy.

You can have a look at them as follows:

SHOW GLOBAL VARIABLES LIKE 'audit_log_file';
SHOW GLOBAL VARIABLES LIKE 'audit_log_policy';
SHOW GLOBAL VARIABLES LIKE 'general_log';
SHOW GLOBAL VARIABLES LIKE 'general_log_file';

Query Logging

Percona Server also supports query logging, which can be useful for monitoring and troubleshooting database performance.

To enable query logging:

SET GLOBAL general_log = 'ON';

This will log all executed queries to the specified file.

Native Data Audit in Percona Server

Percona Server’s audit features are not as advanced as other tools. However, they still offer valuable information on user actions and monitoring database activity.

Tracking User Logins

To track user logins, you can use the following SQL query:

SELECT user, host, db, command, time
FROM information_schema.processlist
WHERE command = 'Connect';

This query shows active connections to the database. The ‘Connect’ state is very short-lived. It only appears briefly when a connection is being established. Try this command to see all states:

SELECT user, host, db, command, time

FROM information_schema.processlist;

Most active connections will be in other states like ‘Sleep’, ‘Query’, or ‘Idle’.

Monitoring Table Access

To monitor table access, you can use triggers:

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50) NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    user VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER audit_MOCK_DATA AFTER INSERT ON MOCK_DATA
FOR EACH ROW
INSERT INTO audit_log (action, table_name, user)
VALUES ('INSERT', 'MOCK_DATA', CURRENT_USER());

INSERT INTO MOCK_DATA (id, first_name, last_name, email) VALUES 
(21, 'L1', 'R1', '[email protected]');

SELECT * from MOCK_DATA md;
SELECT * FROM audit_log;

This code creates a table for the trigger to use. It then creates a trigger named ‘audit_MOCK_DATA’ that inserts data into the audit_log table. This trigger logs INSERT operations on the specified table.

Enhancing Audit Capabilities with DataSunrise

While native tools provide basic auditing, many organizations require more comprehensive solutions. This is where specialized tools like DataSunrise come in handy.

Creating a DataSunrise Instance

To set up DataSunrise for Percona MySQL auditing:

1. Download and install DataSunrise on your server.
2. Launch the DataSunrise console and navigate to the “Instances” section.
3. Click “Add New Instance” and select “Percona MySQL” as the database type.
4. Enter your Percona Server connection details and save the configuration.

In the figure below, locate the Percona MySQL Instance at the bottom of the list.

Detailed Event Descriptions for Data Audit

DataSunrise offers granular control over audit events. To access detailed event descriptions:

1. Go to the “Audit” section in the DataSunrise console.
2. Create a new audit rule by clicking “+ Add Rule.”
3. Select your Percona MySQL instance.
4. Select the events and database objects you want to monitor and configure any additional parameters. Save the rule.

The rule setup is really straightforward and simple.

DataSunrise allows you to track a wide range of events, including:

• SELECT, INSERT, UPDATE, and DELETE operations
• Stored procedure executions
• User authentication attempts
• Schema changes
• Privilege modifications

The figure below illustrates Transactional Trails. Each event is interactive, providing valuable insights into rules and databases across your data assets. Click on any trail to explore detailed information about your entire data ecosystem.

Benefits of Advanced Database Auditing

Implementing a robust database audit solution offers numerous advantages:

1. Improved Security: By monitoring user behavior and database activity monitoring, you can quickly detect and respond to potential security threats.
2. Compliance: Many industries require detailed audit trails. Advanced auditing helps meet regulatory requirements like GDPR, HIPAA, and PCI DSS.
3. Performance Optimization: Analyzing query patterns can help identify bottlenecks and optimize database performance.
4. Forensic Analysis: In case of a security incident, detailed audit logs provide valuable information for investigation and recovery.

Best Practices for Database Auditing

To maximize the effectiveness of your database audit strategy:

1. Define clear audit policies aligned with your organization’s security goals.
2. Regularly review and analyze audit logs for suspicious activities.
3. Implement real-time alerts for critical events.
4. Ensure proper access controls for audit logs themselves.
5. Periodically test your audit mechanisms to ensure they’re capturing all relevant data.

Conclusion

Database auditing is a critical component of a comprehensive security strategy for Percona Server environments. While native tools offer basic capabilities, specialized solutions like DataSunrise provide more robust and flexible auditing options.

By implementing thorough database auditing, you can enhance security, ensure compliance, and gain valuable insights into your database operations. Remember, effective auditing is not just about collecting data – it’s about using that data to make informed decisions and protect your valuable information assets.

DataSunrise offers user-friendly and flexible tools for database security, including comprehensive audit capabilities, data masking, and data discovery features. To explore how DataSunrise can enhance your Percona Server security, visit our website for an online demo and discover the power of advanced database protection.