MariaDB Enterprise Audit

Confirm the MariaDB Audit Plugin is Loaded

To confirm that the MariaDB Audit plugin is loaded, query the information_schema.PLUGINS table:

SELECT PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME='SERVER_AUDIT'\G

*************************** 1. row ***************************
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: server_audit.so
PLUGIN_DESCRIPTION: Audit the server activity

If you see the output shown above, then the MariaDB Audit plugin is installed, and it must be uninstalled prior to performing the upgrade or migration. Follow the instructions in the section below.

If you do not see any rows in the output, then the MariaDB Audit plugin is not installed.

Determine Uninstallation Method

The MariaDB Audit plugin has multiple uninstallation methods. You must choose the uninstallation method that corresponds to how the plugin was installed on your system.

To determine the uninstallation method, query the mysql.plugin system table:

SELECT *
FROM mysql.plugin
WHERE name = 'SERVER_AUDIT'\G

*************************** 1. row ***************************
name: SERVER_AUDIT
  dl: server_audit.so

If you see the output shown above, then the MariaDB Audit plugin can be uninstalled with UNINSTALL SONAME.

If you do not see any rows in the output, then the MariaDB Audit plugin call be uninstalled by editing the configuration file.

Uninstall with UNINSTALL SONAME

To uninstall the MariaDB Audit Plugin with UNINSTALL SONAME:

1. Check the plugin load option by querying the information_schema.PLUGINS table:

   SELECT PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION, LOAD_OPTION
   FROM information_schema.PLUGINS
   WHERE PLUGIN_NAME='SERVER_AUDIT'\G
   

   *************************** 1. row ***************************
        PLUGIN_STATUS: ACTIVE
       PLUGIN_LIBRARY: server_audit.so
   PLUGIN_DESCRIPTION: Audit the server activity
          LOAD_OPTION: FORCE_PLUS_PERMANENT
   

   If the LOAD_OPTION column does not contain the value FORCE_PLUS_PERMANENT, then you can skip to step 5, which executes the UNINSTALL SONAME statement.

2. If the LOAD_OPTION column contains the value FORCE_PLUS_PERMANENT, then check your configuration files for the server-audit option:

   $ grep --extended-regexp --with-filename \
      'server[-_]audit[[:blank:]]*=' \
      /etc/mysql/my.cnf \
      /etc/mysql/mariadb.conf.d/*
   

   /etc/mysql/mariadb.conf.d/enable-audit.cnf:server_audit=FORCE_PLUS_PERMANENT
   

3. If the server-audit option was found in a configuration file, then remove or comment the option out:

   [mariadb]
   # server_audit=FORCE_PLUS_PERMANENT
   

4. If the configuration file was changed, then restart the server:

   $ sudo systemctl restart mariadb
   

5. Uninstall the plugin by executing the UNINSTALL SONAME statement:

   UNINSTALL SONAME 'server_audit';
   

6. Confirm the plugin is uninstalled by querying the mysql.plugin system table:

   SELECT *
   FROM mysql.plugin
   WHERE name = 'SERVER_AUDIT'\G
   

   If the query returns no results, then the plugin has been uninstalled.

Uninstall with Configuration File

To uninstall the MariaDB Audit plugin with a configuration file:

1. Check your configuration files for the plugin_load_add option:

   $ grep --extended-regexp --with-filename \
      'plugin[-_]load[-_]add[[:blank:]]*=[[:blank:]]*server_audit' \
      /etc/mysql/my.cnf \
      /etc/mysql/mariadb.conf.d/*
   

   /etc/mysql/mariadb.conf.d/enable-audit.cnf:plugin_load_add=server_audit
   

2. Remove or comment out the plugin_load_add option from the configuration file:

   [mariadb]
   # plugin_load_add=server_audit
   

Confirm the Audit Plugin is Loaded

To confirm that MariaDB Enterprise Audit is installed, query the information_schema.PLUGINS table:

SELECT PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION, LOAD_OPTION
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME='SERVER_AUDIT'\G

   *************************** 1. row ***************************
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: server_audit2.so
PLUGIN_DESCRIPTION: MariaDB Enterprise Audit
       LOAD_OPTION: FORCE_PLUS_PERMANENT

MariaDB Enterprise Audit is loaded by the mariadb-enterprise.cnf configuration file included by default in MariaDB Enterprise Server 10.4 and later. If your output does not match the example output shown above, confirm that the mariadb-enterprise.cnf configuration file sets the plugin-load-add and server-audit options.

Start Audit Logging in Configuration File

Audit logging with MariaDB Enterprise Audit can be started by setting the server_audit_logging system variable in a configuration file. Alternatively, audit logging can be started using SET GLOBAL, which does not require the server to be restarted.

1. Set the server_audit_logging system variable in a configuration file:

   [mariadb]
   server_audit_logging = ON
   

2. Restart MariaDB Enterprise Server:

   $ sudo systemctl restart mariadb
   

   If the server fails to start, check the messages in the error log.

3. Confirm that audit logging is started by querying the Server_audit_active status variable with the SHOW GLOBAL STATUS statement:

   SHOW GLOBAL STATUS
      LIKE 'Server_audit_active';
   

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

Start Audit Logging with SET GLOBAL

Audit logging with MariaDB Enterprise Audit can be started by setting the server_audit_logging system variable with the SET GLOBAL statement, which requires the SUPER privilege.

1. Set the server_audit_logging system variable with the SET GLOBAL statement:

   SET GLOBAL server_audit_logging=ON;
   

2. Confirm that audit logging is started by querying the Server_audit_active status variable with the SHOW GLOBAL STATUS statement:

   SHOW GLOBAL STATUS
      LIKE 'Server_audit_active';
   

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

3. When a system variable is dynamically changed with the SET GLOBAL statement, the change does not survive server restarts. To ensure that audit logging is started when the server restarts, set the server_audit_logging system variable in a configuration file too:

   [mariadb]
   server_audit_logging = ON
   

Confirm Audit Logging is Started

Confirm that audit logging is started by querying the Server_audit_active status variable with the SHOW GLOBAL STATUS statement:

SHOW GLOBAL STATUS
   LIKE 'Server_audit_active';

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

Confirm that Uninstallation is Forbidden

To confirm that MariaDB Enterprise Audit is configured to forbid uninstallation, query the information_schema.PLUGINS table:

SELECT PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION, LOAD_OPTION
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME='SERVER_AUDIT'\G

   *************************** 1. row ***************************
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: server_audit2.so
PLUGIN_DESCRIPTION: MariaDB Enterprise Audit
       LOAD_OPTION: FORCE_PLUS_PERMANENT

If your output does not match the example output shown above, confirm that the mariadb-enterprise.cnf configuration file sets the server-audit option to FORCE_PLUS_PERMANENT.

Enterprise Audit Not Loaded

MariaDB Enterprise Server can startup and handle traffic even if MariaDB Enterprise Audit is not installed or loaded. However, the specific behavior can be configured.

When the server-audit option is set to FORCE or FORCE_PLUS_PERMANENT, MariaDB Enterprise Server will fail to start if MariaDB Enterprise Audit can't be loaded.

The mariadb-enterprise.cnf configuration file included by default in MariaDB Enterprise Server 10.4 and later sets the server-audit option to FORCE_PLUS_PERMANENT. As a consequence, MariaDB Enterprise Server 10.4 and later forbids MariaDB Enterprise Audit from being uninstalled by default.

Invalid Filter Definitions

MariaDB Enterprise Audit attempts to load the Audit Filters during startup.

When MariaDB Enterprise Audit encounters errors in the Audit Filter definitions, MariaDB Enterprise Server will still start and load MariaDB Enterprise Audit. In this situation, an error will be written to the MariaDB error log and all Events for all objects will be audit logged.

Create a Default Audit Filter

If you want to create a Default Audit Filter, you need to insert the details into the mysql.server_audit_filters system table. The name for the Default Audit Filter must be default and the rule should be designed to meet your audit logging requirements.

To create a Default Audit Filter:

1. Confirm that a Default Audit Filter does not already exist:

   SELECT * FROM mysql.server_audit_filters
       WHERE filtername = 'default';
   

2. If a Default Audit Filter already exists, remove it:

   DELETE FROM mysql.server_audit_filters
       WHERE filtername = 'default';
   

3. Insert the details for the new Default Audit Filter into the mysql.server_audit_filters system table:

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

   This example Audit Filter configures audit logging for all Connection Events and Write Table Events.

   The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

4. Reload the Audit Filters by setting the server_audit_reload_filters system variable to ON:

   SET GLOBAL server_audit_reload_filters=ON;
   

Create a Named Audit Filter

To create a Named Audit Filter, insert a row with the Audit Filter's name and the rule into the mysql.server_audit_filters system table:

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

This example Audit Filter is defined with the name reporting, and it configures audit logging for Connection Events and Disconnection Events and for several types of Table Events. This example Audit Filter can be useful for read-only users since it does not log table reads, but it does log table writes and schema changes which are illegitimate activities for a read-only user.

The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

For additional information on how to assign the Audit Filter to a user account, see "Assign a Named Audit Filter".

Assign a Named Audit Filter

Named Audit Filters are only active when they are assigned to a specific user account.

To assign a Named Audit Filter to a user account:

1. Insert a row with the user account details and the Audit Filter's name into the mysql.server_audit_users system table:

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

   This example statement assigns the reporting Audit Filter created in "Create a Named Audit Filter" to the reader@% user account.

2. Reload the Audit Filters by setting the server_audit_reload_filters system variable to ON:

   SET GLOBAL server_audit_reload_filters=ON;
   

MariaDB Enterprise Audit does not track changes to user accounts. If you delete or rename a user account, the change doesn't cascade to the Audit Filter. The Audit Filters must be updated manually.

Query Audit Filters

You can query Audit Filters by querying the mysql.server_audit_filters system table.

The JSON objects can be made more human-readable by passing them to the JSON_DETAILED() or JSON_LOOSE() functions:

SELECT filtername,
   JSON_DETAILED(rule)
FROM mysql.server_audit_filters\G

*************************** 1. row ***************************
         filtername: reporting
JSON_DETAILED(rule): {
    "table_event":
    [
        "WRITE",
        "CREATE",
        "DROP",
        "RENAME",
        "ALTER",

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

Query User Assignments for Named Audit Filters

You can query user assignments for Named Audit Filters by joining the mysql.server_audit_filters and mysql.server_audit_users system tables.

The JSON objects can be made more human-readable by passing them to the JSON_DETAILED() or JSON_LOOSE() functions:

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

Reload Audit Filters and Assignments

MariaDB Enterprise Audit caches its Audit Filters to improve performance. When you change an Audit Filter or an Audit Filter assignment in the system tables, you need to reload the Audit Filters for the changes to take effect.

To reload Audit Filters, set the server_audit_reload_filters system variable to ON with the SET GLOBAL statement, which requires the SUPER privilege:

SET GLOBAL server_audit_reload_filters=ON;

When you set the server_audit_reload_filters system variable to ON, MariaDB Enterprise Audit reloads all Audit Filters and assignments to ensure that it is using the latest definitions. Afterward, it sets server_audit_reload_filters back to OFF.

For additional information on how to use the server_audit_reload_filters system variable, see "Default Audit Filter" and "Named Audit Filters".

Audit Config Events

MariaDB Enterprise Audit implements Audit Config Events to help keep track of changes to the audit log configuration.

MariaDB Enterprise Audit logs Audit Config (AUDIT_CONFIG) Events in the following situations:

• 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.

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

Audit Config Events are always logged, so no configuration is required when audit logging is started.

Connection Events

MariaDB Enterprise Audit implements Connection Events to audit connection attempts, authentication failures, and user account changes that occur due to certain authentication plugins, such as pam.

MariaDB Enterprise Audit logs Connection Events in the following situations:

• 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.

In the following example output, multiple sub-classes of 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

An Event Filter for Connection Events can be added to an Audit Filter with the connect_event key, which supports the following values:

For example, the following query defines a Named Audit Filter that specifies Connection Events:

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

The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

Query Events

MariaDB Enterprise Audit implements Query Events to audit the execution of a specific subset of SQL statements.

MariaDB Enterprise Audit logs Query Events in the following situations:

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

MariaDB Enterprise Audit does not log Query Events 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.

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

An Event Filter for Query Events can be added to an Audit Filter with the query_event key, which supports the following values:

For example, the following query defines a Named Audit Filter that specifies Query Events:

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

The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

MariaDB Enterprise Audit also supports Object Filters for Query Events. Support for Object Filters was added in MariaDB Enterprise Server 10.6. Support for Object Filters was backported to ES 10.4.21-13 and ES 10.5.12-8.

Table Events

MariaDB Enterprise Audit implements Table Events to audit when a table is accessed or modified.

MariaDB Enterprise Audit logs Table Events in the following situations:

• 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.

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,

When a query uses the DELAYED keyword, it is executed by a system user. In this case, any Table Event associated with the query is written to the audit log with the user set to DELAYED. However, the Query Event associated with the query is written to the audit log with the original user:

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,

When your application executes queries with the DELAYED keyword, it is recommended to enable audit logging for Query Events in addition to Table Events to ensure that the full details are logged.

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.

An Event Filter for Table Events can be added to an Audit Filter with the table_event key, which supports the following values:

For example, the following query defines a Named Audit Filter that specifies Table Events:

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

The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

MariaDB Enterprise Audit also supports Object Filters for Table Events. Support for Object Filters was added in MariaDB Enterprise Server 10.6. Support for Object Filters was backported to ES 10.4.21-13 and ES 10.5.12-8.

Object Filter Format

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

For Object Filters, the key in the key-value pair refers to the specific type of Object Filter. The following types of Object Filters are supported:

The values in the key-value pair refer to 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"}

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

In the following example, the Object Filter is designed to audit log the production and reporting databases and to skip audit logging for all other databases and tables:

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

And in the following example, the Object Filter is designed to skip audit logging for the production.app_log table:

{"ignore_tables": "production.app_log"}

The Object Filter keys must be specified in different locations in the Audit Filter's JSON object, depending on the desired scope of the Object Filter. The following sections describe how to set Object Filters in more detail.

Set Object Filters at Event Scope

An Object Filter can be specified for a single Event Filter within an Audit Filter.

Object Filters are supported by the following Events:

• Query Events

• Table Events

To create an Object Filter for a single Event Filter, specify the Object Filter's JSON object as part of the JSON object for the Event Filter.

The examples below pass the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

In the following example, the reporting Audit Filter specifies an Event Filter for Table Events with an embedded Object Filter that includes all tables in 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

An Object Filter can be specified for an entire Audit Filter.

To create an Object Filter at Audit Filter scope, specify the Object Filter's JSON object as part of the root JSON object for the Audit Filter.

The examples below pass the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

In the following example, the reporting Audit Filter specifies an Object Filter that includes all tables in the production and reporting databases:

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

When an Object Filter is specified at Audit Filter scope, it can contain embedded Event Filters. The following Event types support Object Filters:

• Query Events

• Table Events

In the following example, the reporting Audit Filter has been modified 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.

The examples below pass the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

In the following example, the reporting Audit Filter specifies 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"
                             ]
                         }
                     ]
                  }
              ]
          }'
       )
    );

Audit Log Path

The path to the dedicated audit log file is configured with the server_audit_file_path system variable. The path can be a relative or absolute path. If it is a relative path, then it will be relative to the datadir. For example, to set the path to mariadb-enterprise-audit.log with the SET GLOBAL statement:

SET GLOBAL server_audit_file_path = 'mariadb-enterprise-audit.log'

When a system variable is dynamically changed with the SET GLOBAL statement, the change does not survive server restarts. To ensure that the new path is used when the server restarts, set the server_audit_file_path system variable in a configuration file:

[mariadb]
server_audit_file_path=mariadb-enterprise-audit.log

Audit Log Rotation

When MariaDB Enterprise Audit is configured to use the dedicated audit log file, it can rotate the file.

The file is rotated when its size exceeds the size specified by the server_audit_file_rotate_size system variable. For example, to set the maximum log size to 2 GB with the SET GLOBAL statement:

SET GLOBAL server_audit_file_rotate_size = 2 * (1024 * 1024 * 1024);

When a system variable is dynamically changed with the SET GLOBAL statement, the change does not survive server restarts. To ensure that the new file rotation size is used when the server restarts, set the server_audit_file_rotate_size system variable in a configuration file:

[mariadb]
...
server_audit_file_rotate_size=2147483648

The file can also be rotated manually by setting the server_audit_file_rotate_now system variable to ON. For example, to rotate the log with the SET GLOBAL statement:

SET GLOBAL server_audit_file_rotate_now = ON;

System Log Parameters

Several syslog parameters can be changed for MariaDB Enterprise Audit by setting the following system variables:

• server_audit_syslog_facility

• server_audit_syslog_ident

• server_audit_syslog_info

• server_audit_syslog_priority

Audit Log Format with File

When MariaDB Enterprise Audit is configured to use a dedicated audit log file, it uses the following format for each line:

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

Audit Log Format with Syslog

When MariaDB Enterprise Audit is configured to use the syslog, it uses the following format for each line:

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

Load Plugin

When MariaDB Enterprise Server loads the plugin for MariaDB Enterprise Audit, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 21:07:03 server_audit: MariaDB Audit Plugin version 2.0.3 STARTED.

For additional information, see "Load the Audit Plugin".

Unload Plugin

When MariaDB Enterprise Server unloads the plugin for MariaDB Enterprise Audit, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-02 23:54:59 server_audit: STOPPED

The plugin is unloaded when MariaDB Enterprise Server is shutdown, so this message is most commonly written to the MariaDB error log during the shutdown process.

Start Audit Logging to File

When audit logging is started and it is directed to a file, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 21:39:42 server_audit: logging started to the file server_audit.log.

If a custom audit log path is configured, then the message will refer to the custom path.

For additional information, see "Start Audit Logging" and "Audit Logging to File".

Start Audit Logging to Syslog

When audit logging is started and it is directed to syslog, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 22:02:45 server_audit: logging started to the syslog.

For additional information, see "Start Audit Logging" and "Audit Logging to Syslog".

Stop Audit Logging

When audit logging is stopped, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 21:39:50 server_audit: logging was stopped.

Change Audit Logging to File

When audit logging is changed to a file, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 22:03:31 server_audit: Output was redirected to 'file'

For additional information, see "Audit Logging to File".

Change Audit Logging to Syslog

When audit logging is changed to syslog, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 22:01:22 server_audit: Output was redirected to 'syslog'

For additional information, see "Audit Logging to Syslog".

Change File Name for Audit Logging

When the file name for audit logging is changed, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 22:05:17 server_audit: Log file name was changed to 'mariadb-enterprise-audit.log'.

For additional information, see "Audit Log Path".

Reload Invalid Audit Filters

When the Audit Filters are reloaded and one or more of the Audit Filters are invalid, MariaDB Enterprise Audit writes the following message in the MariaDB error log:

2021-08-03 21:51:55 server_audit: Unknown filter function tabels.
2021-08-03 21:51:55 server_audit: Can't parse filter's 'production' definition { "tabels": "production.*" }.
2021-08-03 21:51:55 server_audit: can't load filters - old filters are saved.

For additional information, see "Reload Audit Filters and Assignments".

Conflict with the Query Cache

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

2021-08-03 21:07:03 server_audit: Query cache is enabled with the TABLE events. Some table reads can be veiled.

Update System Tables

After upgrading to MariaDB Enterprise Server 10.4 or later, execute mariadb-upgrade to create the System Tables for Audit Filters.

Migrate Audit Filters

The MariaDB Audit Plugin defines Audit Filters using the server_audit_events system variable, but MariaDB Enterprise Audit defines Audit Filters using the mysql.server_audit_filters system table.

If you are upgrading from the MariaDB Audit Plugin to MariaDB Enterprise Audit, perform the following procedure:

1. Remove or comment out lines involving the server_audit_events system variable from the configuration file:

   [mariadb]
   ...
   # server_audit_events=CONNECT,QUERY
   

2. Insert a replacement Audit Filter into the mysql.server_audit_filters system table:

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

   The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

Migrate Users

The MariaDB Audit Plugin enables or disable audit logging for specific user accounts using the server_audit_incl_users and server_audit_excl_users system variables, but MariaDB Enterprise Audit uses the mysql.server_audit_users system table.

If you are upgrading from the MariaDB Audit Plugin to MariaDB Enterprise Audit, perform the following procedure:

1. Remove or comment out lines involving the server_audit_incl_users and server_audit_excl_users system variables from the configuration file:

   [mariadb]
   ...
   # server_audit_incl_users = root,app
   # server_audit_excl_users = backup_user,monitor_user
   

2. For any user account previously mentioned in the server_audit_incl_users system variable, determine if the user account can use the Default Audit Filter or if the user account requires a Named Audit Filter.

   Insert the relevant Audit Filters into the the mysql.server_audit_filters system table, and insert the user assignments into the mysql.server_audit_users system table:

   INSERT INTO mysql.server_audit_filters
      VALUES
      ('default',
         JSON_COMPACT(
            '{
               "connect_event": [
                  "CONNECT",
                  "DISCONNECT"
               ],
               "query_event": [
                  "DML",
                  "DDL"
               ]
            }'
      )),
      ('root_filter',
         JSON_COMPACT(
            '{
               "logging": "ON"
            }'
      ));
   
   INSERT INTO mysql.server_audit_users (host, user, filtername)
      VALUES ('%', 'root', 'root_filter');
   

   The example passes the JSON object to the JSON_COMPACT() function, so that the JSON object is compacted prior to being inserted into the system table. This step is recommended, but not required.

3. For any user account previously mentioned in the server_audit_excl_users system variable, create a Named Audit Filter that acts as an exclusion filter.

   Insert the relevant Audit Filters into the the mysql.server_audit_filters system table, and insert the user assignments into the mysql.server_audit_users system table:

   INSERT INTO mysql.server_audit_filters
      VALUES ('exclusion_filter',
         JSON_COMPACT(
            '{
               "logging": "OFF"
            }'
         ));
   
   INSERT INTO mysql.server_audit_users (host, user, filtername)
      VALUES
      ('%', 'backup_user', 'exclusion_filter'),
      ('%', 'monitor_user', 'exclusion_filter');