MariaDB Enterprise Audit

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

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

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.

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"
            ]
        }
    ]
}

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.

Audit Config Events

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

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

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

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,

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:

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"
                             ]
                         }
                     ]
                  }
              ]
          }'
       )
    );

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:

• MariaDB Enterprise Audit plugin is active (Server_audit_active status variable)

• Audit Logging is enabled (server_audit_logging system variable)

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.

Plugin Loaded

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

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

Plugin Active

SHOW STATUS LIKE 'Server_audit_active';

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

Audit Logging Enabled

SHOW VARIABLES LIKE 'server_audit_logging';

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

Enterprise Audit Started

Audit Filters Loaded

Enterprise Audit Running

Audit Filter Review

Audit Log Review