MariaDB Enterprise Audit

While application design specifications and database configurations may intend specific limitations to be placed around access to data, audit mechanisms help confirm the effectiveness of these controls.

MariaDB Enterprise Audit is a plugin that logs data access and database operations. MariaDB Enterprise Server 10.4 includes MariaDB Enterprise Audit. MariaDB Enterprise Server 10.3 and 10.2 use the older community release of the Audit plugin.

Audit mechanisms are most effective when they produce a manageable quantity of output. MariaDB Enterprise Audit includes advanced filtering features to enable narrowly define which information is logged.

Where audit mechanisms may only be effective when control parameters can also be audited, MariaDB Enterprise Audit implements logging of configuration changes.

General Operation

When MariaDB Enterprise Audit plugin has been installed, loaded, and enabled:

  1. As is typical for MariaDB Enterprise Server, a user connects and initiates a SQL query that accesses data in a table.

  2. Audit Filters define events considered significant on a Server level or on a per-user account level.

  3. Events identified for logging by Audit Filters are logged at time of query completion. If such a query fails to complete (e.g. due to lack of permissions), logging occurs at time of failure. In the case of transactions, queries are written at time of individual completion, not at time of COMMIT/ROLLBACK.

  4. Logged data is written to dedicated log or to the system log.

Log Destination Considerations

Care must be taken when logging data for audit to maintain alignment to business requirements. Concerns include:

  • Queries should not be logged for tables containing Personally Identifiable Information (PII) or Sensitive PII (SPII) such as passwords, since audit log data is written unencrypted.

  • Backups of audit data should occur no less frequently than database backups.

  • Audit log data on database servers may be tampered with if the database server is compromised. Consider secure transmission of log data to a hardened and remote logging server.

  • Where audit data is mission-critical, it should be subject to controls, data protection, data retention, and highly-available storage as are used for other mission-critical data.

Enabling the Audit Plugin

The MariaDB Enterprise Audit plugin must be installed, loaded, and enabled to enable logging.

Audit Plugin Installation

The MariaDB Enterprise Audit plugin must be installed to enable logging. MariaDB Enterprise Server installations ship with MariaDB Enterprise Audit.

To confirm that the MariaDB Enterprise Audit plugin is installed, verify that SERVER_AUDIT, loaded with Library server_audit2.so appears in the plugin list:

SHOW PLUGINS;

Audit Plugin Load

The MariaDB Enterprise Audit plugin must be loaded, after installation, to enable logging. MariaDB Enterprise Server installation defaults include a line to load the server_audit2 plugin.

To confirm that the MariaDB Enterprise Audit plugin is loaded, verify that SERVER_AUDIT has a Status of ACTIVE in the plugin list:

SHOW PLUGINS;

Audit Plugin Enablement

The MariaDB Enterprise Audit plugin must be enabled, after installation and loading, to enable logging. MariaDB Enterprise Server must be configured to start with MariaDB Enterprise Audit enabled:

[mariadb]
server_audit_logging = ON

To confirm that the MariaDB Enterprise Audit plugin is enabled, confirm that the value of the Server_audit_active status variable is ON:

SHOW STATUS LIKE 'Server_audit_active';

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Server_audit_active | ON    |
+---------------------+-------+

If you need to enable the MariaDB Enterprise Audit plugin on a temporary basis before a configuration file change takes effect, MariaDB Enterprise Audit may be temporarily enabled using the server_audit_logging system variable:

SET GLOBAL server_audit_logging=ON;

Securing the Audit Plugin

In a secure environment, the MariaDB Enterprise Audit provides administrators with a record of the actions users performed on MariaDB Enterprise Server. To protect the integrity of the record, users should not be able to uninstall MariaDB Enterprise Audit.

Disable Uninstall Audit

When MariaDB Enterprise Server loads MariaDB Enterprise Audit on startup, you have the option of forcing its load and disabling the uninstall option for the user. To do so, set the server_audit plugin activation option to FORCE_PLUS_PERMANENT in the configuration file. Note that this is the default setting on new installations of MariaDB Enterprise Server.

[mariadb]
plugin_load_add = server_audit2
server_audit    = FORCE_PLUS_PERMANENT

If a user attempts to run UNINSTALL against MariaDB Enterprise Audit when the server_audit plugin activation option is FORCE_PLUS_PERMANENT, the user receives Error 1702.

UNINSTALL PLUGIN server_audit;

ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent
and can not be unloaded

Starting, Stopping, and Monitoring the Audit Plugin

MariaDB Enterprise Server will start and handle traffic even if MariaDB Enterprise Audit is not installed, not loaded, or not enabled.

Monitoring services should actively and continuously track the Server_audit_active status variable, raising alerts if MariaDB Enterprise Audit goes offline.

Audit can be enabled on-demand:

SET GLOBAL server_audit_logging=ON;

Audit can also be disabled on-demand:

SET GLOBAL server_audit_logging=OFF;

See Error Handling at Startup for additional information.

Additional Points of Control

When enabled, MariaDB Enterprise Audit logs designated events that occur on a running instance of MariaDB Enterprise Server.

Additional audit practices should be established to cover:

  • Data backup controls

  • System-level controls, including authentication, file system, and process execution

  • Network-level controls

  • Monitoring systems, including checks of the Server_audit_active server status variable

  • Changes to the mysql.global_priv table, which may augment the meaning of MariaDB Enterprise Audit filters

Audit Filters

MariaDB Enterprise Audit allows you to specify the types of events that you want to audit as filters.

You can then assign these filters to specific users to log their activities.

Audit Events

Audit events refer to the operations that MariaDB Enterprise Audit monitors for and logs.

MariaDB Enterprise Audit divides events into five categories: Audit Config events, Connect events, Query events, Table events, and Logging events.

You create filters by specifying the types of events you want to audit. Then, the filters can be assigned to specific users to log their activities.

Audit Config Events

MariaDB Enterprise Audit logs Audit Config events when a new file is created, such as when rotating log files.

Audit Config events also record changes in the MariaDB Enterprise Audit configuration, allowing identification of any cases where a user disables auditing.

Four lines (wrapped) of example Audit Config log output:

20190622 02:10:21,localhost.localdomain,,,0,0,AUDIT_CONFIG,,file_path=server_audit.log,0
20190622 02:10:21,localhost.localdomain,,,0,0,AUDIT_CONFIG,,rotate_size=1000000,0
20190622 02:10:21,localhost.localdomain,,,0,0,AUDIT_CONFIG,,file_rotations=9,0
20190622 02:10:21,localhost.localdomain,root,localhost,8,7,QUERY,mysql,
  'set global server_audit_logging = on',0

Connection Events

Connection events run when a user attempts to connect to or disconnect from MariaDB Enterprise Server.

The filter definition that triggers these events uses a connect_event key.

Supported Connection events (connect_event):

Connection Event

Description

CONNECT

Records when the user connects to MariaDB Enterprise Server

DISCONNECT

Records when the user disconnects from MariaDB Enterprise Server

FAILED_CONNECT

Records when a user attempts to connect to MariaDB Enterprise Server, but fails due to authentication or similar issues

CHANGE_USER

Records when a user switches to a different user account

ALL

Records all connection events

Four line example of Connection event log output:

20190710 00:05:30,localhost.localdomain,root, localhost,2,0,CONNECT,,,0
20190710 00:05:53,localhost.localdomain,root, localhost,2,0,DISCONNECT,,,0
20190710 00:06:28,localhost.localdomain,unknownuser,localhost,3,0,FAILED_CONNECT,,,1045
20190710 00:06:28,localhost.localdomain,unknownuser, localhost,3,0,DISCONNECT,,,0

Query Events

Query events run when a user attempts to run certain subsets of SQL statements.

The filter definition that triggers these events uses a query_event key.

Supported query events (query_event):

Query Event

Description

DML

Records any SQL statements in the Data Manipulation Language subset, including INSERT, UPDATE, and DELETE statements.

DDL

Records any SQL statements in the Data Definition Language subset, including CREATE TABLE and ALTER TABLE, as well as DROP TABLE and TRUNCATE operations.

DCL

Records any SQL statements in the Data Control Language subset, including GRANT and REVOKE.

ALL

Records any SQL statements run by the user.

One line example (wrapped) of Query event log output:

20190710 02:21:07,localhost.localdomain,John,localhost,3,30,QUERY,db1,
'SELECT * FROM services WHERE typeid IN
(SELECT id FROM services_types WHERE name="consulting")',0
Query Event Considerations

Auditing Query events is only effective when the user issues a statement of the given subset against a regular or temporary table. They are not effective when the query issues against a view, calls a stored procedure or stored function, or runs as a trigger.

When a Query event audits a view, it shows the view that was called but does not display the underlying tables accessed. To audit table objects as well, use Table Events.

Table Events

Table events run when the user accesses or operates on table objects. This is similar to Query Events, but is based on the resources accessed rather than the queries run, which prevents a view or trigger from hiding what happens from the audit.

The filter definition that triggers these events uses a table_event key.

Supported Table events (table_event):

Table Event

Description

READ

Records read operations run on table objects, such as from a SELECT statement or an INSERT SELECT

WRITE

Records write operations run on table objects, such as INSERT or UPDATE statements

CREATE

Records any creation operations run on table objects, such as from a CREATE TABLE or CREATE SERVER.

DROP

Records any deletion operations run on table objects, such as DELETE or DROP TABLE

ALTER

Records any modifications made on table objects, such as ALTER TABLE or ALTER USER.

RENAME

Records any renaming operations run on table objects, such as RENAME TABLE

ALL

Records all operations run on table objects

Six line example of Table event log output:

20190710 02:21:06,localhost.localdomain,John,localhost,3,25,CREATE,db1,services,
20190710 02:21:06,localhost.localdomain,John,localhost,3,27,READ,db1,services,
20190710 02:21:07,localhost.localdomain,John,localhost,3,29,WRITE,db1,services,
20190710 02:21:27,localhost.localdomain,John,localhost,3,35,ALTER,db1,services,
20190710 02:21:27,localhost.localdomain,John,localhost,3,36,RENAME,db1,services,db1,services_new
20190710 02:21:45,localhost.localdomain,John,localhost,3,38,DROP,db1,services_new,
Auditing DELAYED Statements

SQL statements that use the DELAYED keyword execute not as the user calling the query but rather as an internal session. When auditing table events, these queries show as being run by the DELAYED user. To track users issuing DELAYED statements, use Query events in addition to Table events.

Logging Events

In cases where you want to turn on or off all auditing filters collectively for a particular user or users, MariaDB Enterprise Audit offers a special logging event.

The filter definition that triggers these events uses a logging key.

Logging Event

Description

ON

Enables all audit logging events for this filter

OFF

Disables all audit logging events for this filter

Setting Filters

Filters are JSON objects that specify what you want MariaDB Enterprise Audit to monitor. MariaDB Enterprise Audit stores filters in the mysql.server_audit_filters table. You give each filter a logical name. Names make it easy to apply specific filters to particular users.

Creating Audit Filters

Creating filters for MariaDB Enterprise Audit requires an INSERT statement such as:

INSERT INTO mysql.server_audit_filters (filtername, rule)
VALUES ('reporting', '{"connect_event": ["CONNECT","DISCONNECT"],
"table_event":["WRITE","CREATE","DROP","RENAME","ALTER"]}');

This example adds a new filter to the server_audit_filters table called reporting. The filter allows for tracking read-only users, recording their connection to and disconnection from MariaDB Enterprise Server, but ignoring their reads as legitimate activity. It records table writes and schema changes as these are illegitimate activities for a read-only user and should be raised in the logs as problems.

Creating Default Audit Filter

MariaDB Enterprise Audit also supports creating a default filter. User accounts that do not have filters specified will use the default filter.

INSERT INTO mysql.server_audit_filters (filtername, rule)
VALUES ('default','{"connect_event":"ALL","table_event":"WRITE"}');

This example creates a filter that records all connection events and all table writes for any otherwise un-audited user.

When making changes to filters, such as correcting an error or expanding a filter definition, remember to reload the filter definitions.

Using Filters

With the exception of the special default filter, Audit filters only go into effect when they are assigned to specific users. MariaDB Enterprise Audit tracks filter assignments using the mysql.server_audit_users table. This table specifies the filter you want to use with the name and host of the user account to use it on.

Assigning filters requires an INSERT statement to the mysql.server_audit_users table such as:

INSERT INTO mysql.server_audit_users (host, user, filtername)
VALUES ("%", "reader", "reporting");

This example statement applies the reporting filter created above to the reader@% user account, here used by the application to read data. In this example, MariaDB Enterprise Audit will log an audit event whenever the user connects, disconnects, or performs illegitimate write operations and schema changes.

Audit and the global_priv System Table

MariaDB Enterprise Audit does not track changes in the mysql.global_priv table. If you delete or rename a user account, the change doesn't cascade to the audit filter.

When making changes to the filter specification, such as assigning new filters to a user or removing old ones, remember to reload the filter definitions.

Reload Audit Filters

MariaDB Enterprise Audit caches filters as it uses them. In cases where you are making changes to the filters or users, you need to reload the filters for the changes to take effect. To reload filters, set the server_audit_reload_filters system variable to ON:

SET GLOBAL server_audit_reload_filters=ON;

When you set this system variable to ON, MariaDB Enterprise Audit reloads the cached filters to their latest definitions, then sets server_audit_reload_filters to OFF when it's done.

Log Output Formats

MariaDB Enterprise Audit can write to either a dedicated log file or to the system log (syslog).

The destination of log output is controlled using the server_audit_log_output system variable.

Logging to Audit Files

When logging to audit files, set the server_audit_log_output system variable to the FILE value. Use the server_audit_file_path system variable to define the path to the audit logs, absolute or relative to the data directory.

MariaDB Enterprise Audit supports the use of log rotation when using the file output type. With log rotation you can set an arbitrary size for your log files using the server_audit_rotate_size system variable. By setting server_audit_rotate_now to ON, MariaDB Enterprise Audit creates a new log file when the current file reaches this size.

Using log files causes MariaDB Enterprise Audit to use this general logging format for each line of output:

<timestamp>,<serverhost>,<username>,<host>,<connectionid>,<queryid>,
<operation>,<database>,<object>,<retcode>

Logging to System Log

When logging to the system log (syslog), including cases where logs are to be transmitted securely to a remote syslog server, set the server_audit_log_output system variable to the SYSLOG value.

Using the system log causes MariaDB Enterprise Audit to use this general logging format for each line of output:

<timestamp> <syslog_host> <syslog_ident>: <syslog_info> <serverhost>,<username>,<host>,
<connectionid>,<queryid>,<operation>,<database>,<object>,<retcode>

Error Handling on Startup

If MariaDB Enterprise Server starts with auditing disabled, no error is generated.

If MariaDB Enterprise Server starts with auditing enabled, MariaDB Enterprise Audit logs an error message noting that the Server has started with full logging turned on.

If MariaDB Enterprise Audit is enabled using a SET statement on the server_audit_logging system variable, MariaDB Enterprise Audit logs an error message to the audit log, and a detailed error message is logged to the server error log.

If MariaDB Enterprise Audit is enabled using a SET statement on the server_audit_filter_reload system variable, a detailed error is generated in the server log that auditing was started with full logging.

If MariaDB Enterprise Audit fails to load (e.g., due to an invalid filter definition):

  • Audit functionality will be disabled.

  • MariaDB Enterprise Server will continue to handle connections.

  • An error message is generated to the audit log.

Monitoring services should actively and continuously track the Server_audit_active status variable, raising alerts if MariaDB Enterprise Audit goes offline.

Upgrades

MariaDB Enterprise Audit ships with MariaDB Enterprise Server 10.4. Special consideration is needed when upgrading from an older version of MariaDB Enterprise Server (10.2 or 10.3) or from any release of MariaDB Community Server to MariaDB Enterprise Server 10.4, as this involves migrating also from the community release of the Audit Plugin to MariaDB Enterprise Audit.

Migrating Audit Filters

The community release of the Audit plugin does not use system tables for filters. Instead, filters are defined in the configuration file using the server_audit_events, server_audit_inc_users, and server_audit_excl_users system variables.

[mariadb]
server_audit_events=CONNECT,QUERY

If you are upgrading from the Audit Plugin to MariaDB Enterprise Audit, remove or comment out lines involving these system variables from the configuration file and replace in the server_audit_filters system tables.

INSERT INTO mysql.server_audit_filters
VALUES ('default', '{"connect_event": "ALL", "query_event": "ALL"}');

Migrating Users

In the case of the server_audit_inc_users system variable, replace these entries with user specifications in the mysql.server_audit_users system table. When replacing the server_audit_excl_users system variables, you can combine the default filter with an exclusion filter.

INSERT INTO mysql.server_audit_filters
VALUES ('default', '{"logging": "ON"}');

Once this is done, you can add excluded users, by setting the excl_users filter on users you want to exclude from auditing on the server_audit_users system table.

INSERT INTO mysql.server_audit_filters
VALUES ('excl_users', '{"logging": "OFF"}');

Update System Tables

In an upgrade from MariaDB Community Server, run the MariaDB Enterprise Server version of mysql_upgrade to update the system tables in the MariaDB Enterprise Server data directory with the new MariaDB Enterprise Audit user and filter tables.