Installing Audit Log Plugin on MySQL to track user activity

Auditing is the monitoring and recording user activity. It can be based on individual actions, such and login to database, SQL statement execution, failed login, successful login etc. It records combined factors like login name, application,time and so on.
Enabling Auditing features on database is very essential from security consideration. Whenever un-usual action happens as table drop, database drop, Audit log is very helpful to find the user who executed the SQL command. In the case of MySQL, version 5.5 or higher is required to configure audit plugins.

Download mariadb-audit-plugin from URL below

Go to URL https://mariadb.com/

Click on Products >> Connectors and Plugins

Click on Download MariaDB Audit Plugins

Download server_audit-1.2.0.tar.gz

Extract downloaded file

tar xzvf server_audit-1.2.0.tar.gz

Login to MySQL as Administrative account. You need to login as user root. In my case I have rename user root to Admin. Execute show variable command to find the plugin directory.

mysql -u Admin -p
show variables like '%plugin%';

Change directory to according to your OS and architecture. In my case, I am using Linux x86_64.

cd linux-64

Copy server_audit.so plugin file the the plugin directory of MySQL fetched above.

cp server_audit.so /usr/lib64/mysql/plugin

Execute Install plugin command to install plugin file. Make sure server_audit.so file must present on MySQL plugin directory before installing the plugin.

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Verify the plugin is installed properly with select command below.

SELECT * from information_schema.plugins where plugin_name='server_audit'\G

Start server auditing with the set global command

SET GLOBAL server_audit_logging=ON;

Verify server audit has been enabled and working. Use tail command to view audit.log

tail -f /var/lib/mysql/server_audit.log

Below is the details on MySQL server audit parameters. These parameters and its descriptions are copied from url https://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin-options-variables.html We can view the server_audit variables and modify as per needed.

SHOW GLOBAL VARIABLES LIKE 'server_audit%';

mysql> SHOW VARIABLES LIKE 'audit_log%';
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| audit_log_current_session   | ON           |
| audit_log_exclude_accounts  |              |
| audit_log_file              | audit.log    |
| audit_log_flush             | OFF          |
| audit_log_format            | OLD          |
| audit_log_include_accounts  |              |
| audit_log_policy            | ALL          |
| audit_log_rotate_on_size    | 0            |
| audit_log_statement_policy  | ALL          |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+

audit_log_buffer_size
When the audit log plugin writes events to the log asynchronously, it uses a buffer to store event contents prior to writing them. This variable controls the size of that buffer, in bytes. The server adjusts the value to a multiple of 4096. The plugin uses a single buffer, which it allocates when it initializes and removes when it terminates. The plugin allocates this buffer only if logging is asynchronous.


audit_log_connection_policy
The policy controlling how the audit log plugin writes connection events to its log file. The following table shows the permitted values.
Value Description
ALL Log all connection events
ERRORS Log only failed connection events
NONE Do not log connection events

audit_log_current_session
Whether audit logging is enabled for the current session. The session value of this variable is read only. It is set when the session begins based on the values of the audit_log_include_accounts and audit_log_exclude_accounts system variables. The audit log plugin uses the session value to determine whether to audit events for the session. (There is a global value, but the plugin does not use it.)

audit_log_exclude_accounts
The accounts for which events should not be logged. The value should be NULL or a string containing a list of one or more comma-separated account names. For more information, see Section 6.3.13.4, “Audit Log Plugin Logging Control”.
Modifications to audit_log_exclude_accounts affect only connections created subsequent to the modification, not existing connections.

audit_log_file
The name of the file to which the audit log plugin writes events. The default value is audit.log. If the file name is a relative path, the server interprets it relative to the data directory. For security reasons, the audit log file should be written to a directory accessible only to the MySQL server and users with a legitimate reason to view the log.

audit_log_flush
When this variable is set to enabled (1 or ON), the audit log plugin closes and reopens its log file to flush it. (The value remains OFF so that you need not disable it explicitly before enabling it again to perform another flush.) Enabling this variable has no effect unless audit_log_rotate_on_size is 0.

audit_log_format
The audit log file format. Permitted values are OLD and NEW (default OLD).

audit_log_policy
The policy controlling the information written by the audit log plugin to its log file. The following table shows the permitted values.


audit_log_rotate_on_size
If the audit_log_rotate_on_size value is 0, the plugin does not close and reopen its log based on size. Instead, use audit_log_flush to close and reopen the log on demand. In this case, rename the file externally to the server before flushing it.


audit_log_strategy
The logging method used by the audit log plugin. The following table describes the permitted values.

ASYNCHRONOUS Log asynchronously, wait for space in output buffer
PERFORMANCE Log asynchronously, drop request if insufficient space in output buffer
SEMISYNCHRONOUS Log synchronously, permit caching by operating system
SYNCHRONOUS Log synchronously, call sync() after each request