MariaDB Enterprise Audit

Overview

MariaDB Enterprise Audit logs database access and database operations:

  • Changes to audit configuration are logged

  • Filters define what information is logged, reducing unnecessary output

  • If filter definitions are malformed, the system fails safe by logging everything

Compatibility

  • Multi-Node Analytics

  • Replicated Transactions

  • Single Node Analytics

  • Single Node Transactions

Lifecycle

When

What

How

Before initial setup or changes

Planning (Recommended)

  • Read and understand MariaDB Enterprise Audit documentation prior to implementing or changing Enterprise Audit logging

Before initial setup or changes

Evaluate Control Requirements (As Needed)

  • When a service will house production data, audit logging is often configured in consultation with IT management, compliance, audit, infosec, or internal controls functions.

  • When a service will house non-production test data, audit logging is often configured in consultation with software development and quality assurance functions.

Initial setup

Enable Enterprise Audit functionality (Required)

Initial setup

Define Audit Filters (Required)

Initial setup

Verification (Recommended)

Ongoing

Update Audit Filters (As Needed)

Ongoing

Download logs (As Needed)

  • Download audit logs and/or error logs from the Log Archive

  • Review log data as needed

Ongoing

Verification (As Needed)

Ongoing

Evidence capture (As Needed)

  • Capture evidence as required by your audit process

Configuration Manager

Audit logging functionality is available only when the MariaDB Enterprise Audit plugin is loaded and audit logging is enabled.

Two SkySQL Configuration Manager configuration parameters must be set to enable audit logging functionality:

  1. The server_audit configuration parameter defines how the MariaDB Enterprise Audit plugin is loaded:

    Setting

    Plugin loaded?

    Server start requires plugin load?

    Plugin can be uninstalled?

    OFF

    No

    No

    Yes

    ON

    Yes

    No

    Yes

    FORCE

    Yes

    Yes

    Yes

    FORCE_PLUS_PERMANENT (default)

    Yes

    Yes

    No

  2. The server_audit_logging parameter defines whether audit logging is enabled:

    • Audit logging is disabled when server_audit_logging is OFF (default).

    • Audit logging is enabled when server_audit_logging is ON.

After a configuration has been prepared with the intended parameters, it must be applied to the desired service for this change to take effect. The service will be restarted when the configuration is applied.

MariaDB Enterprise Audit can only perform logging if the plugin is loaded and audit logging is enabled.

Log Archive

MariaDB Enterprise Audit log data is available in the Log Archive.

To download log data:

  1. Log in to the MariaDB SkySQL portal.

  2. Access "Monitoring" in left navigation.

  3. From SkySQL Monitoring, access "Logs" in left navigation.

  4. Select the desired server(s), log types (such as "Enterprise Audit Log"), and time frame, then click the "Download" button to download the logs.

    • Log files can be downloaded in your choice of JSON and/or plain text formats.

    • The downloaded .zip archive will contain one file per server, log, day, and format.

Data Retention

  • The Log Archive retains log data for 7 years. Data is retained even if the SkySQL service is deleted.

  • Logs are stored in GCS buckets with HTTPS data-in-transit encryption and Google default (AES256) data-at-rest encryption.

  • Logs are stored by day, by service, by server, by log type.

  • To request log data expungement, submit a Support Request.

  • Data retention requirements vary by business. A business process may be needed to download audit log data and error log data in support of your data retention requirements.

Log Format

Fields

  1. timestamp

  2. server host

  3. database username

  4. user host

  5. connection ID

  6. query ID

  7. operation

  8. database name

  9. object

  10. return code

Note

MariaDB Enterprise Audit logs can contain sensitive information, such as PII and SPII.

Plain Text

In plain text format, MariaDB Enterprise Audit log has this format for each line:

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

JSON

In JSON format, MariaDB Enterprise Audit log has this format for each line, incorporating the plain text format:

{"log":"PLAIN_TEXT","offset":OFFSET_VALUE}

One JSON object is produced for each logical line from the original plain text log file.

JSON key order is not guaranteed.

Audit Filters and Audit Users

Audit logging should be configured based on business-specific control requirements which define the database objects and/or users whose activity must be logged.

Audit Filters and Audit Users are mechanisms used to specify what what will be audit logged.

Audit Filters

Audit Filters define what MariaDB Enterprise Audit should log:

  • The Default Audit Filter, if present, is an Audit Filter with filtername of default.

  • Named Audit Filters are any Audit Filters with filtername other than default.

  • The Default Audit Filter is applied for any user not subject to a Named Audit Filter.

  • Named Audit Filters are applied as defined by Audit Users.

  • Audit Filters are stored in the mysql.server_audit_filters table.

  • Audit Filter rules can contain Event Filters, Logging Filters, and Object Filters.

Audit Users

Audit Users assign a Named Audit Filter to a specific user:

  • User is specified as a hostname and username combination.

  • Audit Users are stored in the mysql.server_audit_users table.

Audit Filter Display

Audit Filter behavior is defined in the mysql.server_audit_filters and mysql.server_audit_users tables.

Displaying JSON Data

In many of the examples on this page, the JSON_DETAILED() function is used to make JSON objects more human-readable. The JSON_LOOSE() function is an alternative.

Using a function to make JSON objects more human-readable is optional but recommended.

Default Audit Filter

To view the Default Audit Filter, if one exists, query the database server:

SELECT filtername,
   JSON_DETAILED(rule)
FROM mysql.server_audit_filters
WHERE filtername='default'\G

Named Audit Filters

To view Named Audit Filters, if any exist, query the database server:

SELECT filtername,
   JSON_DETAILED(rule)
FROM mysql.server_audit_filters
WHERE filtername != 'default'\G
*************************** 1. row ***************************
         filtername: reporting
JSON_DETAILED(rule): {
    "table_event":
    [
        "WRITE",
        "CREATE",
        "DROP",
        "RENAME",
        "ALTER",

        {
            "log_databases":
            [
                "production",
                "reporting"
            ]
        }
    ]
}

Audit Users

User assignments for Named Audit Filters can be understood by joining the mysql.server_audit_filters and mysql.server_audit_users system tables.

To view user assignments for Named Audit Filters, if any exist, query the database server:

SELECT sau.host, sau.user, saf.filtername,
   JSON_DETAILED(saf.rule)
FROM mysql.server_audit_filters saf
JOIN mysql.server_audit_users sau
   ON saf.filtername = sau.filtername
WHERE saf.filtername != 'default'\G
*************************** 1. row ***************************
                   host: %
                   user: reader
             filtername: reporting
JSON_DETAILED(saf.rule): {
    "table_event":
    [
        "WRITE",
        "CREATE",
        "DROP",
        "RENAME",
        "ALTER",

        {
            "log_databases":
            [
                "production",
                "reporting"
            ]
        }
    ]
}
*************************** 2. row ***************************
                   host: %
                   user: writer
             filtername: reporting
JSON_DETAILED(saf.rule): {
    "table_event":
    [
        "WRITE",
        "CREATE",
        "DROP",
        "RENAME",
        "ALTER",

        {
            "log_databases":
            [
                "production",
                "reporting"
            ]
        }
    ]
}

Audit Filter Management

Audit Filter behavior is defined in the mysql.server_audit_filters and mysql.server_audit_users tables.

Changes to Audit Filters are applied by stopping and starting the service.

Compacting JSON Data

In many of the examples on this page, the JSON_COMPACT() function is used to compact JSON data before insertion into a system table.

This is optional but recommended.

Named Audit Filters

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider audit logging which may be appropriate for a read-only user that queries data to produce business reports. For this user, we might want to record their connect and disconnect events, but ignore read activity. For this user, table writes and schema changes would be illegitimate activities.

  1. To create our reporting Audit Filter:

    INSERT INTO mysql.server_audit_filters (filtername, rule)
       VALUES ('reporting',
          JSON_COMPACT(
             '{
                "connect_event": [
                  "CONNECT",
                  "DISCONNECT"
                ],
                "table_event":[
                  "WRITE",
                  "CREATE",
                  "DROP",
                  "RENAME",
                  "ALTER"
                ]
             }'
          ));
    
  2. Our filter must now be applied to the user. To apply the reporting Audit Filter to the reader@% user account:

    INSERT INTO mysql.server_audit_users (host, user, filtername)
    VALUES ("%", "reader", "reporting");
    
  3. Finally, to apply this Audit Filter change, we will stop and start the service.

MariaDB Enterprise Audit includes advanced filter settings to define which events are logged.

Default Audit Filter

Any user account not assigned to a Named Audit Filter is subject to the Default Audit Filter (if one is defined).

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider logging all connection events and all table writes for users otherwise un-audited.

  1. To create our Default Audit Filter:

    INSERT INTO mysql.server_audit_filters (filtername, rule)
       VALUES ('default',
          JSON_COMPACT(
             '{
                "connect_event":"ALL",
                "table_event":"WRITE"
             }'
       ));
    
  2. Finally, to apply this Audit Filter change, we will stop and start the service.

MariaDB Enterprise Audit includes advanced filter settings to define which events are logged.

Event Filters

Event Type

Purpose

Audit Config

Logs changes to the audit log configuration

Connection

Logs connection attempts, authentication failures, and user account changes due to certain authentication plugins

Query

Logs execution of a specific subset of SQL statements

Table

Logs when a table is accessed or modified

Logging Filters and Object Filters can change the logging behavior of other filters.

Special configuration is needed when SQL statements use the DELAYED keyword. See "Auditing DELAYED Statements".

Audit Config Events

Detail

Description

Purpose

Log changes to the audit log configuration

JSON Key

Audit Config Events are not customer-configurable and are always logged

Event Types

  • AUDIT_CONFIG - audit log configuration changes

Behavior

  • When one of MariaDB Enterprise Audit's system variables is changed with the SET GLOBAL statement, the change is logged.

  • When the audit log file is rotated, it is logged.

Sample Filter:

Audit Config events are always logged. These events are not configurable.

Sample Log:

In the following example output, 3 Audit Config Events and 1 Query Event are shown:

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

Detail

Description

Purpose

Logs connection attempts, authentication failures, and user account changes due to certain authentication plugins

JSON Key

connect_event

Event Types

  • 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

  • PROXY_CONNECT - Records proxy user connections

  • ALL - Records all connection events

Behavior

  • When a user successfully connects, it is logged with the CONNECT Event sub-class.

  • When a user disconnects, it is logged with the DISCONNECT Event sub-class.

  • When a user fails to connect, it is logged with the FAILED_CONNECT Event sub-class.

  • When an existing connection authenticates as a different user, it is logged with the CHANGE_USER Event sub-class.

  • When an authentication plugin changes to a proxy user, it is logged with the PROXY_CONNECT Event sub-class.

Sample Filter:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES ('connections',
      JSON_COMPACT(
         '{
            "connect_event": [
               "CONNECT",
               "DISCONNECT"
            ]
         }'
      ));

Sample Log:

In the following example output, 4 Connection Events are shown:

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

Detail

Description

Purpose

Logs execution of a specific subset of SQL statements

JSON Key

query_event

Event Types

  • 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

Behavior

  • When a SQL statement is directly executed, it is logged with the QUERY Event.

  • Query Events are not logged for SQL statements that are indirectly executed. For example, if an SQL statement is executed as part of a view, stored procedure, stored function, or trigger, the query will not be logged. If you want to audit all table accesses, including indirect table accesses, it is recommended to enable audit logging for Table Events in addition to Query Events.

  • Object Filters can be used to limit the logging of Query Events.

  • Special configuration is needed when SQL statements use the DELAYED keyword. See "Auditing DELAYED Statements".

Sample Filter:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES ('queries',
      JSON_COMPACT(
         '{
            "query_event": [
               "DML",
               "DDL"
            ]
         }'
      ));

Sample Log:

In the following example output, 1 Query Event is shown:

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

Table Events

Detail

Description

Purpose

Logs when a table is accessed or modified

JSON Key

table_event

Event Types

  • 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

Behavior

  • When an operation reads from a table, it is logged with the READ Event sub-class.

  • When an operation writes to a table, it is logged with the WRITE Event sub-class.

  • When an operation creates a table, it is logged with the CREATE Event sub-class.

  • When an operation drops a table, it is logged with the DROP Event sub-class.

  • When an operation alters a table, it is logged with the ALTER Event sub-class.

  • When an operation renames a table, it is logged with the RENAME Event sub-class.

  • Table Events are logged when a table is accessed or modified directly or indirectly by a query. They complement Query Events very well, because the Query Event causes the raw query to be audit logged, and the Table Event causes all table operations to be audit logged. Both Query Events and Table Events are logged with the query ID, so each Table Event can easily be mapped to its corresponding Query Event. The combination of Query Events and Table Events can be useful when table operations can be hidden by views or triggers, or when certain query logging must be skipped due to the presence of PII/SPII.

  • Object Filters can be used to limit the logging of Table Events.

  • Special configuration is needed when SQL statements use the DELAYED keyword. See "Auditing DELAYED Statements".

  • If using the query cache, see "Implementation Considerations: Query Cache".

Sample Filter:

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

Sample Log:

In the following example output, 6 sub-classes of Table Events are shown:

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

When a query uses the DELAYED keyword, behavior changes:

  • The query is executed by a system user.

  • Any Table Event associated with the query is written to the audit log with the user set to DELAYED

  • Any Query Event associated with the query is written to the audit log with the original user.

For example:

20190622 02:10:21,localhost.localdomain,root,localhost,8,5,QUERY,test,'INSERT DELAYED INTO t1 VALUES(1),(2),(3);',0
20190622 02:10:25,localhost.localdomain,root,localhost,2,2,WRITE,test,t1,
20190622 02:10:25,localhost,DELAYED,localhost,2,2,WRITE,test,t1,

To support audit of queries with the DELAYED keyword, it is recommended to use both Query Events and Table Events as to ensure that a complete set of details are logged.

Logging Filters

Detail

Description

Purpose

Turn on or turn off all Audit Filters for a user or users

JSON Key

logging

Event Types

  • ON - Enables all audit logging events for this filter

  • OFF - Disables all audit logging events for this filter

Behavior

  • When a user's Audit Filter contains a Logging Filter that disables audit logging, all audit logging for the user's activity will be skipped.

Sample Filter:

The following example defines a Default Audit Filter that enables logging for all Events for any user account without a Named Audit Filter:

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

Object Filters

Object Filters allow Audit Filters to be limited to specific databases and/or tables.

Examples

An Object Filter to audit log the production and reporting databases, and to skip audit logging for all other databases and tables:

{"log_tables": ["production.*", "reporting.*"]}

An Object Filter to skip audit logging for the production.app_log table:

{"ignore_tables": "production.app_log"}

Object Filter Types

Object Filters are formatted as JSON objects, which are key-value pairs.

The Object Filter key specifies the behavior to apply:

Object Filter Key

Behavior

ignore_databases

  • The specified databases will not be audit logged.

  • The ignore_databases Object Filter key is an alias for the ignore_tables Object Filter key, with the table identified set to the wildcard character (*).

  • The ignore_databases Object Filter key cannot be specified in the same Object Filter as the log_databases and log_tables Object Filter keys.

ignore_tables

  • The specified tables will not be audit logged.

  • Table names must be provided in the form database.table

  • Wildcard characters (*) are allowed.

  • The ignore_tables Object Filter key cannot be specified in the same Object Filter as the log_databases and log_tables Object Filter keys.

log_databases

  • The specified databases will be audit logged.

  • All unspecified databases will not be audit logged.

  • The log_databases Object Filter key is an alias for the log_tables Object Filter key, with the table identified set to the wildcard character (*).

log_tables

  • The specified tables will be audit logged.

  • All unspecified tables will not be audit logged.

  • Table names must be provided in the form database.table

  • Wildcard characters (*) are allowed.

Specify a Single Object

Object Filters are formatted as JSON objects, which are key-value pairs.

The Object Filter value specifies one or more object names.

When the Object Filter only applies to one object, the object name can be specified as a string scalar value in the JSON object:

{"object_filter_key": "object"}

Specify Multiple Objects

Object Filters are formatted as JSON objects, which are key-value pairs.

The Object Filter value specifies one or more object names.

When the Object Filter applies to multiple objects, the object names can be specified in a JSON array in the JSON object:

{"object_filter_key": [ "object", "object" ]}

Set Object Filters at Event Scope

The location of an Object Filter within the Audit Filter's JSON object will depend on the desired scope of the Object Filter. Several scopes are possible.

At Event Scope, an Object Filter can be specified for a Query Event or Table Event within an Audit Filter.

This is done by specifying the Object Filter's JSON object as part of the JSON object for the Event Filter.

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider a reporting Audit Filter to audit log the specified table events for only the production and reporting databases:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES (
       'reporting',
       JSON_COMPACT(
           '{
              "table_event": [
                  "WRITE",
                  "CREATE",
                  "DROP",
                  "RENAME",
                  "ALTER",
                  {
                     "log_tables": [
                        "production.*",
                        "reporting.*"
                     ]
                  }
              ]
           }'
       )
    );

Set Object Filters at Audit Filter Scope

The location of an Object Filter within the Audit Filter's JSON object will depend on the desired scope of the Object Filter. Several scopes are possible.

At Audit Filter Scope, an Object Filter can be specified for an entire Audit Filter.

This is done by specifying the Object Filter's JSON object as part of the root JSON object for the Audit Filter.

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider a reporting Audit Filter to audit log for only the production and reporting databases:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES (
       'reporting',
       JSON_COMPACT(
          '{
              "log_tables": [
                  "production.*",
                  "reporting.*"
              ]
          }'
       )
    );

Embedded Event Filters

When an Object Filter is specified at Audit Filter Scope, it can contain embedded Event Filters.

Query Events and Table Events support Object Filters.

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider a reporting Audit Filter to include Event Filters on specific Query Event sub-classes and Table Event sub-classes:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES (
       'reporting',
       JSON_COMPACT(
          '{
              "log_tables": [
                  "production.*",
                  "reporting.*",
                  {
                     "query_event": [
                         "DML",
                         "DDL"
                     ],
                     "table_event": [
                         "WRITE",
                         "CREATE",
                         "DROP",
                         "RENAME",
                         "ALTER"
                     ]
                  }
              ]
          }'
       )
    );

Combine Object Filters at Audit Filter and Event Scope

When an Object Filter is specified at Audit Filter Scope with embedded Event Filters, the embedded Event Filters can contain additional Object Filters. When you combine Object Filters at different levels, it is possible to combine Object Filters that are normally incompatible, like tables and ignore_tables.

To create Object Filters at Audit Filter and Event Type Scope, specify the JSON object for the Object Filter at Audit Filter Scope as part of the root JSON object for the Audit Filter and specify the JSON object for the Object Filter at Event Type Scope as part of the JSON object for the Event Filter.

Audit filters are customized based on your specific business requirements. There is no one-size-fits-all approach.

As a simple example, we consider a reporting Audit Filter to specify an Event Filter for Table Events with an embedded Object Filter that includes all tables in the production and reporting databases, but it excludes Query Events that target specific tables that store Personally Identifiable Information (PII), so that the sensitive information does not appear in the audit log:

INSERT INTO mysql.server_audit_filters (filtername, rule)
   VALUES (
       'reporting',
       JSON_COMPACT(
          '{
              "log_tables": [
                  "production.*",
                  "reporting.*",
                  {
                     "table_event": [
                         "WRITE",
                         "CREATE",
                         "DROP",
                         "RENAME",
                         "ALTER"
                     ],
                     "query_event": [
                         "DML",
                         "DDL",
                         {
                             "ignore_tables": [
                                 "production.customer_profiles",
                                 "production.customer_addresses"
                             ]
                         }
                     ]
                  }
              ]
          }'
       )
    );

Implementation Considerations

Business requirements may vary, and there is no one-size-fits-all approach to audit.

Migration

When migrating to MariaDB SkySQL from MariaDB Server, some changes may be necessary to your Enterprise Audit user and filter tables. Contact us for assistance.

Privileges

When Enterprise Audit is used, applications should connect to the database with user accounts that do not have privileges to modify the Audit Filters:

  • The default user for the database has privileges to modify Audit Filters, so this user should not be used for application database access.

  • Applications should connect to the database using non-default database users who do not have privileges to DELETE, INSERT, or UPDATE the mysql.server_audit_filters or mysql.server_audit_users tables.

User Renames and Deletes

Manual action may be needed to update Named Audit Filters when a database user account is renamed or deleted:

  • When a user is deleted, manual deletion may be needed to clean-up any mysql.server_audit_users rows for the old username.

  • When a user is renamed, manual update may be needed for any mysql.server_audit_users rows for the old username.

  • The Default Audit Filter is applied for any user without a Named Audit Filter matching their username.

Query Cache

By default, the query cache is disabled.

If the query cache is enabled, READ Table Events may not be audit logged. If MariaDB Enterprise Audit detects that the query cache is enabled during startup, MariaDB Enterprise Audit writes the following message to the MariaDB error log:

2021-07-23  0:11:26 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

Application Checks

Application code could perform checks when connecting to the database to verify that:

Error Log Checks

MariaDB Enterprise Server error logs indicate presence of MariaDB Enterprise Audit:

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

  • If MariaDB Enterprise Server starts with auditing enabled, MariaDB Enterprise Audit logs a message to the MariaDB error log.

Verification

Business requirements may vary, and there is no one-size-fits-all approach to audit.

The following are common steps that could be used to help verify proper operation of MariaDB Enterprise Audit.

This is not the only way to verify correct operation of MariaDB Enterprise Audit. Different steps may be suitable for your specific business needs.

Plugin Loaded

Detail

Description

How

With sufficient privileges, query the database server

Sample Query

SELECT * FROM information_schema.PLUGINS
  WHERE PLUGIN_NAME = 'server_audit'\G

Output Details

  • Specific output will depend on load options and server version.

  • server_audit2.so is the MariaDB Enterprise Audit plugin.

Expected Result

If the correct plugin is loaded, a row with PLUGIN_LIBRARY of server_audit2.so will be present, and LOAD_OPTION will not be OFF

Sample Output

MariaDB> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME ='server_audit'\G
*************************** 1. row ***************************
           PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 2.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit2.so
PLUGIN_LIBRARY_VERSION: 1.14
         PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
    PLUGIN_DESCRIPTION: MariaDB Enterprise Audit
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE_PLUS_PERMANENT
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 2.0.2

Failure Indication

If MariaDB Enterprise Audit is supposed to be enabled but server_audit2.so is not present or if LOAD_OPTION is OFF, this might indicate a failure. The plugin is not active and no audit logging is being performed.

Plugin Active

Detail

Description

How

With sufficient privileges, query the database server

Sample Query

SHOW STATUS LIKE 'Server_audit_active';

Expected Result

If the plugin is active, the value of the Server_audit_active status variable is ON.

Sample Output

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

Failure Indication

If MariaDB Enterprise Audit is supposed to be enabled but Server_audit_active is OFF or if no Server_audit_active variable is returned, this might indicate a failure. The plugin is not active and no audit logging is being performed.

Audit Logging Enabled

Detail

Description

How

With sufficient privileges, query the database server

Sample Query

SHOW VARIABLES LIKE 'server_audit_logging';

Expected Result

If audit logging is enabled, the value of the server_audit_logging system variable is ON.

Sample Output

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| server_audit_logging | ON    |
+----------------------+-------+

Failure Indication

If MariaDB Enterprise Audit is supposed to be enabled but server_audit_logging is OFF or if the server_audit_logging variable is not present, this might indicate a failure. The plugin is not active and no audit logging is being performed.

Enterprise Audit Started

Detail

Description

How

Examine MariaDB Enterprise Server error logs

Prior Analysis

Before examining log data, first determine the time periods subject to review. Consider whether there were periods where database server activity was not subject to audit, such as with new servers not yet housing data subject to controls.

Log Details

  • Error Log data is downloaded from the SkySQL Monitoring Log Archive.

  • Error Logs are split by day. Relevant logs cover the subject time period, and back to the prior point where the server was started.

  • Error Log output may vary based on load options and server version.

Expected Result

After the server start line, a line referencing server_audit will indicate STARTED

Sample Output

  • At each point where the server was started, an Error Log line will appear similar to:

    2022-07-19 13:37:34 0 [Note] mysqld (server 10.6.7-3-MariaDB-enterprise-log) starting as process 14 ...
    
  • At each point where MariaDB Enterprise Audit was started, an Error Log line will appear similar to:

    2022-07-19 13:37:35 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
    

Failure Indication

If MariaDB Enterprise Audit was supposed to be enabled but there is no error log line noting that server_audit has STARTED after the server was started, this might indicate a failure. The plugin was not active and no audit logging was being performed.

Audit Filters Loaded

Detail

Description

How

Examine MariaDB Enterprise Server error logs

Log Details

  • Error Log data is downloaded from the SkySQL Monitoring Log Archive.

  • Error Logs are split by day. Relevant logs cover the subject time period, and back to the prior point where the server was started.

  • Error Log output may vary based on load options, server version, and server events.

Sample Output

  • At each point where MariaDB Enterprise Audit was started, an Error Log line will appear similar to:

    2022-07-19 13:37:35 server_audit: MariaDB Audit Plugin version 2.4.1 STARTED.
    
  • If audit filter loading has failed, the system will fail safe and log everything. If audit filter loading has failed, an Error Log line will appear similar to:

    2022-07-19 14:20:05 server_audit: Filters aren't loaded, logging everything.
    

Expected Result

No line states Filters aren't loaded

Failure Indication

The presence of a Filters aren't loaded line might indicate a failure. If present, the system will have failed safe and logged everything. The result may be more data than expected in the audit logs.

Enterprise Audit Running

Detail

Description

How

Examine MariaDB Enterprise Server error logs

Log Details

  • Error Log data is downloaded from the SkySQL Monitoring Log Archive.

  • Error Logs are split by day. Relevant logs cover the subject time period, and back to the prior point where the server was started.

  • Error Log output may vary based on load options, server version, and server events.

Sample Output

  • At each point where the server began stopping normally, an Error Log line will appear similar to:

    2022-07-19 14:16:27 0 [Note] mysqld (initiated by: root[root] @ localhost []): Normal shutdown
    
  • At any point where the server was running but MariaDB Enterprise Audit was stopped, a line referencing server_audit will indicate STOPPED

Expected Result

Any line indicating server_audit being STOPPED appears only after a shutdown event.

Failure Indication

The presence of an error log line indicating server_audit has been STOPPED at a time when server shutdown was not performed might indicate a failure. The plugin was not active and no audit logging was being performed.

Audit Filter Review

As needed, review Audit Filters and Audit Users:

Detail

Description

How

With sufficient privileges, query the database server

Sample Output

See "Audit Filters and Audit Users" for details on how to query this information.

Expected Result

The expected result is business-specific.

Audit Log Review

As needed, confirm correct filter configuration:

Detail

Description

How

Examine MariaDB Enterprise Audit logs

Log Details

  • Enterprise Audit Log data is downloaded from the SkySQL Monitoring Log Archive.

  • Enterprise Audit Logs are split by day. Relevant logs cover the subject time period.

  • Enterprise Audit log output may vary based on load options, server versions, Audit Filters in use, and database activity.

Expected Result

The expected result is business-specific.