MariaDB Enterprise Audit – The New Plugin

Compliance regulations, like the European Union’s GDPR, require a complete audit of access to sensitive data, and a database, the component used for persistent storage of this data, is the primary element auditors inspect.

Auditing modern data stores is a challenge because of the scale of the generated audit trace. Modern services are always online, and successful services generate even more audit trace data as the data increases by the number of connections and data queries.

These challenges must be addressed by providing a flexible filter layer, which allows the database to store as little data as needed but enough to fulfill the compliance regulations for data access. 

A database server typically serves data from multiple applications. Users can be anything from an application user – where the queries are defined by an application over reporting where, by definition, access is limited to reads – up to DevOps and DBAs, which can execute any kind of query. As a result, a filter layer attached to users is an obvious solution.

With MariaDB Enterprise Server 10.4 we are introducing the MariaDB Enterprise Audit plugin, with enhancements for filters.

What’s New

MariaDB Enterprise Audit provides: 

  • Auditing of:

– Connects
– Queries
– Access to tables
New: Auditing settings and changes 

  • New: System tables for filter templates and users
  • New: Default filter
  • New: Per-user account level filters

 

How to Install

The MariaDB Enterprise Audit plugin is already part of the MariaDB Enterprise Server 10.4 installation, as auditing is a core requirement for business-critical environments. The only required step is to activate the auditing via a setting in the config file in order to keep the setting persistent in the case of a server restart. 

[mariadb]
plugin-load-add=server_audit2
server_audit=FORCE_PLUS_PERMANENT
server_audit_logging=ON

Auditing can also be temporarily activated via a SET command if a server restart should be avoided. 

SET GLOBAL server_audit_logging=on;

The audit trace is logged to a file, if not configured differently. The MariaDB Enterprise Documentation covers possible settings, like using a syslog instead of a file. 

Auditing is active at this stage, but without any filter defined. In this case all users will get a full audit! In the next step a default filter will be created.

The Default Filter

The default filter defines the auditing rules for any user without a defined dedicated filter. This also assures that a newly created user will be audited based on a defined auditing rule. It is recommended to always define the default rule, so an auditor can verify a good default setting without the need to prove a hard-coded auditing.

The first of the two new system tables, server_audit_filters, is needed to define a default. 

The table consists of:

filtername Needs to be default for the default filter, otherwise a freely selectable template name
rule Filter definition in JSON format 

The following statement creates a default filter, which records all connection events and all table writes for any otherwise un-audited user:

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

The filtername is required to be ‘default’! The documentation provides the full set of filter options

MariaDB Enterprise Audit caches the auditing rules; therefore, it is required to reload the rules.

SET GLOBAL server_audit_reload_filters=ON;

NOTE: An error will occur if the auditing rules cannot be loaded, usually because of a wrong filter definition. The auditing switches back to the “audit everything” mode in such a case.

The next step explains how to create a per-user, account-level filter.

User Filters

Audit logs can get huge if a complete audit – connects, full set of queries and table access – is stored for all users. This is not always needed as compliance auditing also covers the application level. In turn, applications (like Jira) often come with their own user management and a fixed set of queries. On the database backend there exists only an “application user”.

If, in the process of the compliance auditing, the data access is already documented on an application level, it might be enough to log the connects and disconnects of this application user. 

This can be configured by the definition of a filter template. In this example, rule “jira_server” defines only the log events of type connect (connects, failed connects, disconnects).

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

The new system table server_audit_users in MariaDB Enterprise Server 10.4 is used to store the user information and template used.

Table server_audit_users:

host Connection client host 
user User name of the connection 
filtername Filtername from table server_audit_filters

In this example, there exists an application user “jira_production”, the account used for a Jira instance in production. There also exists an application user “jira_staging” used by another Jira instance for testing. The statement below is used to attach the defined filter template “jira_server” to these users:

INSERT INTO mysql.server_audit_users (host, user, filtername)
VALUES ("10.0.0.10", "jira_production", "jira_server"),("10.0.0.9", "jira_staging", "jira_server");

After reloading the filter definitions any connect, disconnect or failed connect from user “jira_production” and “jira_test” will be logged, but no table access or query.

NOTE: The users “jira_production” and “jira_staging” might have been created with a host “10.0.0.%” in MariaDB Enterprise Server. The default filter would be used if these users connect from any other IP than 10.0.0.10 and 10.0.0.9, respectively.

Auditing the Audit Plugin

MariaDB Enterprise Audit also comes with a new event “AUDIT_CONFIG”, which cannot be disabled. Logged information of this type indicates a change of a MariaDB Enterprise Audit config parameter, or is added to the beginning of each new audit log file to log the current settings. 

This way the setting of the MariaDB Enterprise Audit can be evaluated for any given point in time.

Example 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

 

Summary

MariaDB Enterprise Audit provides the functionality needed by customers to store information about data access, which is essential when a company needs to run compliance audits or report data objects at a given point in time. The new functionality of per-user filters helps companies log only what needs to be logged.

MariaDB Enterprise Audit does not create a history of the data. MariaDB Enterprise Server also includes the system-versioned tables feature, if this is required.

MariaDB customers can start using MariaDB Enterprise Audit today, simply download MariaDB Enterprise Server now.