Audit log plugin¶

Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server. Information about the activity is stored in a log file. This implementation is alternative to the MySQL Enterprise Audit Log Plugin

Version specific information¶

• 8.0.12-1: The feature was ported from Percona Server for MySQL 5.7.

• Percona Server for MySQL 8.0.15-6: The Audit_log_buffer_size_overflow variable was implemented.

Install the plugin¶

The audit Log plugin is installed, but, by default, is not enabled when you install Percona Server for MySQL. To check if the plugin is enabled run the following commands:

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';

Expected output

Empty set (0.00 sec)

mysql> SHOW variables LIKE 'audit%';

Expected output

Empty set (0.01 sec)

mysql> SHOW variables LIKE 'plugin%';

Expected output

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

The following command enables the plugin:

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Run the following command to verify if the plugin was installed correctly:

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G

Expected output

*************************** 1. row ***************************
        PLUGIN_NAME: audit_log
        PLUGIN_VERSION: 0.2
        PLUGIN_STATUS: ACTIVE
        PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
        PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.7
        PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
    PLUGIN_DESCRIPTION: Audit log
        PLUGIN_LICENSE: GPL
        LOAD_OPTION: ON
1 row in set (0.00 sec)

You can review the audit log variables with the following command:

mysql> SHOW variables LIKE 'audit%';

Expected output

+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | audit.log     |
| audit_log_flush             | OFF           |
| audit_log_format            | OLD           |
| audit_log_handler           | FILE          |
| audit_log_include_accounts  |               |
| audit_log_include_commands  |               |
| audit_log_include_databases |               |
| audit_log_policy            | ALL           |
| audit_log_rotate_on_size    | 0             |
| audit_log_rotations         | 0             |
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+
18 rows in set (0.00 sec)

Log format¶

The plugin supports the following log formats: OLD, NEW, JSON, and CSV. The OLD\`format and the``NEWformat are based on XML. TheOLDformat defines each log record with XML attributes. TheNEW` format defines each log record with XML tags. The information logged is the same for all four formats. The audit_log_format variable controls the log format choice.

Format examples¶

The following formats are available:

<AUDIT_RECORD
NAME="Query"
RECORD="3_2021-06-30T11:56:53"
TIMESTAMP="2021-06-30T11:57:14 UTC"
COMMAND_CLASS="select"
CONNECTION_ID="3"
STATUS="0"
SQLTEXT="select * from information_schema.PLUGINS where PLUGIN_NAME like '%audit%'"
USER="root[root] @ localhost []"
HOST="localhost"
OS_USER=""
IP=""
DB=""
/>

Audit log events¶

The audit Log plugin generates a log of following events.

??? example "Audit event"

    ```text
    <AUDIT_RECORD
    NAME="Audit"
    RECORD="1_2021-06-30T11:56:53"
    TIMESTAMP="2021-06-30T11:56:53 UTC"
    MYSQL_VERSION="5.7.34-37"
    STARTUP_OPTIONS="--daemonize --pid-file=/var/run/mysqld/mysqld.pid"
    OS_VERSION="x86_64-debian-linux-gnu"
    />
    ```

Stream the audit log to syslog¶

To stream the audit log to syslog you’ll need to set audit_log_handler variable to SYSLOG. To control the syslog file handler, the following variables can be used: audit_log_syslog_ident, audit_log_syslog_facility, and audit_log_syslog_priority These variables have the same meaning as appropriate parameters described in the syslog(3) manual.

Filter methods¶

You can filter the results by the following methods.

Filter examples¶

The following are examples of the different filters.

mysql> SET GLOBAL audit_log_include_accounts = 'user1@localhost,root@localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';

ERROR 1231 (42000): Variable 'audit_log_exclude_accounts' can't be set to the value of 'user1@localhost,root@localhost'

mysql> SET GLOBAL audit_log_include_accounts = NULL;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = "'user'@'host'";

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = '''user''@''host''';

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = '\'user\'@\'host\'';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@audit_log_exclude_accounts;

+------------------------------+
| @@audit_log_exclude_accounts |
+------------------------------+
| 'user'@'host'                |
+------------------------------+
1 row in set (0.00 sec)

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY '111';

Query OK, 0 rows affected (0.00 sec)

<AUDIT_RECORD
NAME="Connect"
RECORD="2_2021-06-30T11:56:53"
TIMESTAMP="2021-06-30T11:56:53 UTC"
CONNECTION_ID="6"
STATUS="0"
USER="user1" ;; this is a 'user' part of account in 8.0
PRIV_USER="user1"
OS_LOGIN=""
PROXY_USER=""
HOST="localhost" ;; this is a 'host' part of account in 8.0
IP=""
DB=""
/>

SET GLOBAL audit_log_exclude_accounts = 'user1@%';

System variables¶

audit_log_strategy¶

This variable is used to specify the audit log strategy, possible values are:

• ASYNCHRONOUS - (default) log using memory buffer, do not drop messages if buffer is full

• PERFORMANCE - log using memory buffer, drop messages if buffer is full

• SEMISYNCHRONOUS - log directly to file, do not flush and sync every event

• SYNCHRONOUS - log directly to file, flush and sync every event

This variable has effect only when audit_log_handler is set to FILE.

audit_log_file¶

This variable is used to specify the filename that’s going to store the audit log. It can contain the path relative to the datadir or absolute path.

audit_log_flush¶

When this variable is set to ON log file will be closed and reopened. This can be used for manual log rotation.

audit_log_buffer_size¶

This variable can be used to specify the size of memory buffer used for logging, used when audit_log_strategy variable is set to ASYNCHRONOUS or PERFORMANCE values. This variable has effect only when audit_log_handler is set to FILE.

audit_log_exclude_accounts¶

This variable is used to specify the list of users for which Filtering by user is applied. The value can be NULL or comma separated list of accounts in form user@host or 'user'@'host' (if user or host contains comma). If this variable is set, then audit_log_include_accounts must be unset, and vice versa.

audit_log_exclude_commands¶

This variable is used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_include_commands must be unset, and vice versa.

audit_log_exclude_databases¶

This variable is used to specify the list of commands for which Filtering by database is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_include_databases must be unset, and vice versa.

audit_log_format¶

This variable is used to specify the audit log format. The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV. OLD and NEW formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats.

audit_log_include_accounts¶

This variable is used to specify the list of users for which Filtering by user is applied. The value can be NULL or comma separated list of accounts in form user@host or 'user'@'host' (if user or host contains comma). If this variable is set, then audit_log_exclude_accounts must be unset, and vice versa.

audit_log_include_commands¶

This variable is used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_exclude_commands must be unset, and vice versa.

audit_log_include_databases¶

This variable is used to specify the list of commands for which Filtering by database is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_exclude_databases must be unset, and vice versa.

audit_log_policy¶

This variable is used to specify which events should be logged. Possible values are:

• ALL - all events will be logged

• LOGINS - only logins will be logged

• QUERIES - only queries will be logged

• NONE - no events will be logged

audit_log_rotate_on_size¶

This variable is measured in bytes and specifies the maximum size of the audit log file. Upon reaching this size, the audit log will be rotated. The rotated log files are present in the same directory as the current log file. The sequence number is appended to the log file name upon rotation. For this variable to take effect, set the audit_log_handler variable to FILE.

audit_log_rotations¶

This variable is used to specify how many log files should be kept when audit_log_rotate_on_size variable is set to non-zero value. This variable has effect only when audit_log_handler is set to FILE.

audit_log_handler¶

This variable is used to configure where the audit log will be written. If it is set to FILE, the log will be written into a file specified by audit_log_file variable. If it is set to SYSLOG, the audit log will be written to syslog.

audit_log_syslog_ident¶

This variable is used to specify the ident value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

audit_log_syslog_facility¶

This variable is used to specify the facility value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

audit_log_syslog_priority¶

This variable is used to specify the priority value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

Status Variables¶

Audit_log_buffer_size_overflow¶

The number of times an audit log entry was either dropped or written directly to the file due to its size being bigger than audit_log_buffer_size variable.