Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes.
Audits are needed for security. You can track data access and be alerted to suspicious activity. Audits are required for data integrity. They are the only way to validate that changes made to data are correct and legal.
There are several regulations that require database audits:
- Sarbanes-Oxley (SOX) Act of 2002 is a US federal law that regulates how financial data must be handled and protected.
- Payment Card Industry Data Security Standard, otherwise known as PCI-DSS is an international standard developed to protect cardholder’s data.
- Health Insurance Portability and Accountability Act (HIPAA) enacted by the U.S. Congress to protect medical and personal information.
MySQL since version 5.5.3 provides the Audit Plugin API which can be used to write an Audit Plugin. The API provides notification for the following events:
- messages written to general log (LOG)
- messages written to error log (ERROR)
- query results sent to client (RESULT)
- logins (including failed) and disconnects (CONNECT)
All current audit plugins for MySQL provide an audit log as result of their work. They differ in record format, filtering capabilities and verbosity of log records.
McAfee MySQL Audit Plugin
This plugin is available for MySQL versions 5.1, 5.5, 5.6. It does not officially support Percona Server and MariaDB. It doesn’t use the Audit API and has better verbosity and better filtering features. This is achieved by binary patching the server at runtime inserting the hooks which extract data stored in known offsets in memory. Thus, the plugin is sensitive to any changes of server code.
Summary:
- json log format
- log to file or UNIX socket (allows to log with syslog-ng)
- filter logged events by users, databases and tables, commands (insert, update, delete)
Oracle Enterprise Audit Log Plugin
Oracle provides this audit plugin as a part of the MySQL Enterprise pack. It uses the MySQL Audit API and is able to log RESULT and CONNECT events. The plugin has support for two XML-based formats.
Summary:
- XML format
- log to file
- filter by event type
MariaDB Audit Plugin
MariaDB developers extended the MySQL Audit API by adding fields for existing events and adding new TABLE event which notifies of operation with tables (read, write, create, drop, alter). The plugin can still be used with MySQL and Percona Server but MariaDB’s additions will not be available.
Summary:
- CSV log format
- log to file or syslog
- filter by users, event types
Percona Server Audit Log feature
Percona has developed an audit log feature that is a part of Percona Server since 5.5.35-37.0 and 5.6.17-65.0. It’s goal is to be compatible with Oracle’s Enterprise Audit Plugin providing a similar set of features for Percona Server users. It asynchronously logs all queries and connections in order to “audit” Percona Server usage, without the overhead of the General Query Log. The Audit Log feature can be very beneficial for web applications that deal with sensitive data (e.g., credit card numbers or medical records) and require security compliance (e.g., HIPAA or SOX). Administrators of multi-tenant applications or MySQL as a service can easily audit data access from a security and performance standpoint when using the Audit Log feature in Percona Server. The Audit Log feature is helpful for investigating and troubleshooting issues and auditing performance, too. The Audit Log feature can be dynamically enabled (does not require a server restart).
There are more options for audit logging:
Enabling the general log and then parsing it to extract the needed data. This has serious impact on performance. But besides the McAfee plugin this is one of the only solutions for MySQL 5.1 and earlier.
Tracing network packets. MySQL traffic is often unencrypted so it can be inspected. It sounds interesting, but I don’t known if anyone is doing this currently. Also most regulations require encryption and then it might not work anymore (See https://isc.sans.edu/forums/diary/Psst+Your+Browser+Knows+All+Your+Secrets+/16415 about inspecting SSL/TLS traffic).
Using a proxy like mysql-proxy. Could work, but it could result in it being a bottleneck/SPOF.
Running auditing client-side. Some clients and ORM’s support interceptor plugins or similar constructions. This might not fully cover all regulations, but it might be a scalable solution.
Is anyone aware of any bench mark for the performance degradation with McAffe MySQL Audit Plugin enabled?
This blog is very nice.Database auditing includes watching a database in order to know about the activities of database clients. for more information visit here:- https://www.datasunrise.com