Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server. Information about the activity will be stored in the XML log file where each event will have its NAME field, it’s own unique RECORD_ID field and a TIMESTAMP field. This implementation is alternative to the MySQL Enterprise Audit Log Plugin
Audit Log plugin produces the log of following events:
NAME field will be Audit when logging started and NoAudit when logging finished. Audit record also includes server version and command-line arguments.Example of the Audit event:
<AUDIT_RECORD
"NAME"="Audit"
"RECORD"="1_2014-04-29T09:29:40"
"TIMESTAMP"="2014-04-29T09:29:40 UTC"
"MYSQL_VERSION"="5.5.37-35.0-655.trusty"
"STARTUP_OPTIONS"="--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306"
"OS_VERSION"="x86_64-debian-linux-gnu",
/>
NAME field Connect when user logged in or login failed, or Quit when connection is closed. Additional fields for this event are CONNECTION_ID, STATUS, USER, PRIV_USER, OS_LOGIN, PROXY_USER, HOST, and IP. STATUS will be 0 for successful logins and non-zero for failed logins.Example of the Disconnect event:
<AUDIT_RECORD
"NAME"="Quit"
"RECORD"="24_2014-04-29T09:29:40"
"TIMESTAMP"="2014-04-29T10:20:13 UTC"
"CONNECTION_ID"="49"
"STATUS"="0"
"USER"=""
"PRIV_USER"=""
"OS_LOGIN"=""
"PROXY_USER"=""
"HOST"=""
"IP"=""
"DB"=""
/>
COMMAND_CLASS (values come from the com_status_vars array in the sql/mysqld.cc` file in a MySQL source distribution. Examples are select, alter_table", "create_table", etc.), ``CONNECTION_ID, STATUS (indicates error when non-zero), SQLTEXT (text of SQL-statement), USER, HOST, OS_USER, IP. Possible values for the NAME name field for this event are Query, Prepare, Execute, Change user, etc.Example of the Query event:
<AUDIT_RECORD
"NAME"="Query"
"RECORD"="23_2014-04-29T09:29:40"
"TIMESTAMP"="2014-04-29T10:20:10 UTC"
"COMMAND_CLASS"="select"
"CONNECTION_ID"="49"
"STATUS"="0"
"SQLTEXT"="SELECT * from mysql.user"
"USER"="root[root] @ localhost []"
"HOST"="localhost"
"OS_USER"=""
"IP"=""
/>
Audit Log plugin is shipped with Percona Server, but it is not installed by default. To enable the plugin you must run the following command:
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
You can check if the plugin is loaded correctly by running:
SHOW PLUGINS;
Audit log should be listed in the output:
+--------------------------------+----------+--------------------+--------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------+---------+
...
| audit_log | ACTIVE | AUDIT | audit_log.so | GPL |
+--------------------------------+----------+--------------------+--------------+---------+
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. The log format choice is controlled by audit_log_format variable.
Example of the OLD format:
<AUDIT_RECORD
"NAME"="Query"
"RECORD"="2_2014-04-28T09:29:40"
"TIMESTAMP"="2014-04-28T09:29:40 UTC"
"COMMAND_CLASS"="install_plugin"
"CONNECTION_ID"="47"
"STATUS"="0"
"SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'"
"USER"="root[root] @ localhost []"
"HOST"="localhost"
"OS_USER"=""
"IP"=""
/>
Example of the NEW format:
<AUDIT_RECORD>
<NAME>Quit</NAME>
<RECORD>10902_2014-04-28T11:02:54</RECORD>
<TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP>
<CONNECTION_ID>36</CONNECTION_ID>
<STATUS>0</STATUS>
<USER></USER>
<PRIV_USER></PRIV_USER>
<OS_LOGIN></OS_LOGIN>
<PROXY_USER></PROXY_USER>
<HOST></HOST>
<IP></IP>
<DB></DB>
</AUDIT_RECORD>
Example of the JSON format:
{"audit_record":{"name":"Query","record":"4707_2014-08-27T10:43:52","timestamp":"2014-08-27T10:44:19 UTC","command_class":"show_databases","connection_id":"37","status":0,"sqltext":"show databases","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":""}}
Example of the CSV format:
"Query","49284_2014-08-27T10:47:11","2014-08-27T10:47:23 UTC","show_databases","37",0,"show databases","root[root] @ localhost []","localhost","",""
Ability to stream the audit log to syslog was implemented in Percona Server 5.5.39-36.0. 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.
Note
Variables: audit_log_strategy, audit_log_buffer_size, audit_log_rotate_on_size, audit_log_rotations have effect only with FILE handler.
audit_log_strategy¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | ASYNCHRONOUS |
| Allowed values: |
|
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 fullPERFORMANCE - log using memory buffer, drop messages if buffer is fullSEMISYNCHRONOUS - log directly to file, do not flush and sync every eventSYNCHRONOUS - log directly to file, flush and sync every eventThis variable has effect only when audit_log_handler is set to FILE.
audit_log_file¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | audit.log |
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | Yes |
| Variable Type: | String |
| Default Value: | OFF |
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | Numeric |
| Default Value: | 4096 |
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_format¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | OLD |
| Allowed values: |
|
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_policy¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | Yes |
| Variable Type: | String |
| Default Value: | ALL |
| Allowed values: |
|
This variable is used to specify which events should be logged. Possible values are:
ALL - all events will be loggedLOGINS - only logins will be loggedQUERIES - only queries will be loggedNONE - no events will be loggedaudit_log_rotate_on_size¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | Numeric |
| Default Value: | 0 (don’t rotate the log file) |
This variable is used to specify the maximum audit log file size. Upon reaching this size the log will be rotated. The rotated log files will be present in the same same directory as the current log file. A sequence number will be appended to the log file name upon rotation. This variable has effect only when audit_log_handler is set to FILE.
audit_log_rotations¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | Numeric |
| Default Value: | 0 |
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | FILE |
| Allowed values: |
|
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | percona-audit |
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | LOG_USER |
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¶| Version Info: |
|
|---|---|
| Command Line: | Yes |
| Scope: | Global |
| Dynamic: | No |
| Variable Type: | String |
| Default Value: | LOG_INFO |
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.
5.5.37-35.0Audit Log plugin has been implemented in Percona Server.5.5.39-36.0Percona Server Audit Log Plugin now supportsJSONandCSVlog formats.5.5.39-36.0Percona Server Audit Log Plugin now supports streaming the audit log to syslog.
For general inquiries, please send us your question and someone will contact you.
