What is Percona Server for MySQL Audit Trail?

Introduction

In today’s data-driven world, ensuring the security and integrity of your database is more critical than ever. One effective way to achieve this is by implementing a robust Percona Server for MySQL Audit Trail. This feature allows organizations to track and log database activities, providing a detailed record of who accessed the data, what changes were made, and when these actions occurred. Percona Server for MySQL, a high-performance, open-source database solution, offers native capabilities to support such auditing needs, making it a preferred choice for businesses that want to enhance their database security and compliance.

The Percona Server for MySQL Audit Trail is not only a tool for compliance but also a critical component for detecting suspicious activities, ensuring data integrity, and maintaining a transparent record of all database operations. As databases become more complex, the need for a reliable audit trail becomes even more pronounced. Let’s dive into the native capabilities of Percona Server for MySQL and explore how it helps you establish a secure and compliant database environment.

Native Percona Server for MySQL Audit Techniques: Audit Log Filter Plugin

Installing the Plugin

Percona Server for MySQL includes a powerful Audit Log Plugin, which provides native support for tracking and logging database activities. This plugin lets you monitor user actions, query executions, and connection attempts, ensuring that every activity within your database is recorded. To get started, enable the audit log plugin with the following bash command:

cd /usr/share/mysql
mysql -u -D database -p < audit_log_filter_linux_install.sql

Verifying the Setup

After you install the plugin, verify its status by running the following query:

SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';

Configuring Audit Log Policies

You can configure the audit log to track all activities by setting the audit policy to ‘ALL’:

SET GLOBAL audit_log_policy = 'ALL';

You can also customize the log format and output file location. For example, you can set the log format to JSON and specify the log file location using the following commands:

SET GLOBAL audit_log_format = 'JSON';
SET GLOBAL audit_log_file = '/var/log/mysql/audit.log';

With these settings in place, you can start monitoring database activities in real-time. The audit log will capture detailed information, including user actions, query types, and timestamps, providing a comprehensive view of your database operations.

Key Features of Percona’s Audit Log Plugin

• Granular Logging: Tracks specific user activities, queries, and connections.
• Customizable Filters: Lets you filter by user accounts, SQL command types, and databases.
• Real-Time Monitoring: Provides real-time insights into database activities.
• Tamper-Evident Logs: Protects the integrity of audit logs by making them resistant to tampering.

Audit Log Filter Plugin

Percona Server for MySQL also includes an Audit Log Filter Plugin, which lets you define more granular filtering rules for your audit logs. This plugin enables you to specify which events to log based on various criteria, such as user, database, or SQL command type. This feature can be particularly useful for reducing the volume of log data to improve data value and focusing on critical events.

To enable the Audit Log Filter Plugin, use the following command:

INSTALL PLUGIN audit_log_filter SONAME 'audit_log_filter.so';

Once installed, you can create and manage filter rules. For example, to create a filter that logs only SELECT queries on a specific database, use these commands:

SELECT audit_log_filter_set_filter('select_filter', '{ "filter": { "class": { "name": "general", "event": { "name": "query", "action": { "name": "select" } } }, "database": "your_database_name" } }');
SELECT audit_log_filter_set_user('%', 'select_filter');

Sample Audit Output

Here’s an example of what the audit log output might look like in JSON format:

{
  "timestamp": "2023-10-01T12:34:56Z",
  "user": "admin",
  "host": "192.168.1.1",
  "ip": "192.168.1.1",
  "connection_id": 12345,
  "query": "SELECT * FROM employees WHERE id = 101;",
  "database": "hr",
  "command": "SELECT",
  "status": "success"
}

This log entry provides detailed information about the query execution, including the user who executed the query, the database accessed, and the query itself.

For more detailed information on configuring and using the audit log plugin, refer to the Percona Server for MySQL documentation.

Database Audit with DataSunrise

While Percona Server for MySQL offers robust native auditing capabilities, there are scenarios where additional tools can further enhance your database security and compliance. This is where DataSunrise comes into play.

DataSunrise lets you create custom audit rules, monitor database activities in real time, and generate detailed reports. For example, you can set up a security rule to block connections from specific IP addresses, ensuring that only authorized users can access your database. Here’s how to create such a rule:

• Create a Test Audit Rule: Start by creating a test audit rule to gather information about the host you plan to block.
• Select the Target Instance: Choose the Percona Server instance and configure the rule settings.
• Create a Security Rule: Based on the gathered data, create a security rule to block the specified host.

DataSunrise also provides a user-friendly interface for managing audit logs, making it easy to review and analyze database activities. With its powerful rule-based system, you can ensure your database remains secure and compliant with regulatory requirements.

By integrating DataSunrise with Percona Server for MySQL, you can achieve a higher level of security and control over your database operations. Whether you need to monitor user activities, detect potential threats, or generate compliance reports, DataSunrise provides the tools you need.

Conclusion

In conclusion, implementing a Percona Server for MySQL Audit Trail is essential for maintaining a secure and compliant database environment. While Percona Server for MySQL offers powerful native auditing capabilities, and integrating a third-party solutions like DataSunrise can further enhance your database security and provide additional features for monitoring and reporting.

For businesses operating in complex and regulated environments, combining Percona Server for MySQL with DataSunrise offers a comprehensive solution for database auditing and security. To take your database security to the next level, consider booking a personalized online demo or downloading the DataSunrise trial version today.