6.4.5.4 Audit Log File Formats

Some elements appear in every <AUDIT_RECORD> element. Others are optional and may appear depending on the audit record type.

Order of elements within an <AUDIT_RECORD> element is not guaranteed.

Element values are not fixed length. Long values may be truncated as indicated in the element descriptions given later.

The <, >, ", and & characters are encoded as <, >, ", and &, respectively. NUL bytes (U+00) are encoded as the ? character.

Characters not valid as XML characters are encoded using numeric character references. Valid XML characters are:

#x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

<NAME>

A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

Example:

<NAME>Query</NAME>

Some common <NAME> values:

Audit    When auditing starts, which may be server startup time
Connect  When a client connects, also known as logging in
Query    An SQL statement (executed directly)
Prepare  Preparation of an SQL statement; usually followed by Execute
Execute  Execution of an SQL statement; usually follows Prepare
Shutdown Server shutdown
Quit     When a client disconnects
NoAudit  Auditing has been turned off

The possible values are Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.

With the exception of Audit and NoAudit, these values correspond to the COM_xxx command values listed in the my_command.h header file. For example, Create DB and Change user correspond to COM_CREATE_DB and COM_CHANGE_USER, respectively.

<RECORD_ID>

A unique identifier for the audit record. The value is composed from a sequence number and timestamp, in the format SEQ_TIMESTAMP. When the audit log plugin opens the audit log file, it initializes the sequence number to the size of the audit log file, then increments the sequence by 1 for each record logged. The timestamp is a UTC value in YYYY-MM-DDThh:mm:ss format indicating the date and time when the audit log plugin opened the file.

Example:

<RECORD_ID>12_2017-10-16T14:06:33</RECORD_ID>

<TIMESTAMP>

A string representing a UTC value in YYYY-MM-DDThh:mm:ss UTC format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a <TIMESTAMP> value occurring after the statement finishes, not when it was received.

Example:

<TIMESTAMP>2017-10-16T14:09:45 UTC</TIMESTAMP>

<COMMAND_CLASS>

A string that indicates the type of action performed.

Example:

<COMMAND_CLASS>drop_table</COMMAND_CLASS>

The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

<CONNECTION_ID>

An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

Example:

<CONNECTION_ID>127</CONNECTION_ID>

<CONNECTION_TYPE>

The security state of the connection to the server. Permitted values are TCP/IP (TCP/IP connection established without encryption), SSL/TLS (TCP/IP connection established with encryption), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), and Shared Memory (Windows shared memory connection).

Example:

<CONNECTION_TYPE>SSL/TLS</CONNECTION_TYPE>

<DB>

A string representing the default database name.

Example:

<DB>test</DB>

<HOST>

A string representing the client host name.

Example:

<HOST>localhost</HOST>

<IP>

A string representing the client IP address.

Example:

<IP>127.0.0.1</IP>

<MYSQL_VERSION>

A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

Example:

<MYSQL_VERSION>5.7.21-log</MYSQL_VERSION>

<OS_LOGIN>

A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this element is empty. The value is the same as that of the external_user system variable (see Section 6.2.18, “Proxy Users”).

Example:

<OS_LOGIN>jeffrey</OS_LOGIN>

<OS_VERSION>

A string representing the operating system on which the server was built or is running.

Example:

<OS_VERSION>x86_64-Linux</OS_VERSION>

<PRIV_USER>

A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and may differ from the <USER> value.

Example:

<PRIV_USER>jeffrey</PRIV_USER>

<PROXY_USER>

A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

Example:

<PROXY_USER>developer</PROXY_USER>

<SERVER_ID>

An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

Example:

<SERVER_ID>1</SERVER_ID>

<SQLTEXT>

A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

Example:

<SQLTEXT>DELETE FROM t1</SQLTEXT>

<STARTUP_OPTIONS>

A string representing the options that were given on the command line or in option files when the MySQL server was started. The first option is the path to the server executable.

Example:

<STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld
  --port=3306 --log_output=FILE</STARTUP_OPTIONS>

<STATUS>

An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function. See the description for <STATUS_CODE> for information about how it differs from <STATUS>.

The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

Warnings are not logged.

Example:

<STATUS>1051</STATUS>

<STATUS_CODE>

An unsigned integer representing the command status: 0 for success, 1 if an error occurred.

The STATUS_CODE value differs from the STATUS value: STATUS_CODE is 0 for success and 1 for error, which is compatible with the EZ_collector consumer for Audit Vault. STATUS is the value of the mysql_errno() C API function. This is 0 for success and nonzero for error, and thus is not necessarily 1 for error.

Example:

<STATUS_CODE>0</STATUS_CODE>

<USER>

A string representing the user name sent by the client. This may differ from the <PRIV_USER> value.

Example:

<USER>root[root] @ localhost [127.0.0.1]</USER>

<VERSION>

An unsigned integer representing the version of the audit log file format.

Example:

<VERSION>1</VERSION>

Some attributes appear in every <AUDIT_RECORD> element. Others are optional and may appear depending on the audit record type.

Order of attributes within an <AUDIT_RECORD> element is not guaranteed.

Attribute values are not fixed length. Long values may be truncated as indicated in the attribute descriptions given later.

The <, >, ", and & characters are encoded as <, >, ", and &, respectively. NUL bytes (U+00) are encoded as the ? character.

Characters not valid as XML characters are encoded using numeric character references. Valid XML characters are:

#x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

NAME

A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

Example: NAME="Query"

Some common NAME values:

Audit    When auditing starts, which may be server startup time
Connect  When a client connects, also known as logging in
Query    An SQL statement (executed directly)
Prepare  Preparation of an SQL statement; usually followed by Execute
Execute  Execution of an SQL statement; usually follows Prepare
Shutdown Server shutdown
Quit     When a client disconnects
NoAudit  Auditing has been turned off

The possible values are Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.

With the exception of "Audit" and "NoAudit", these values correspond to the COM_xxx command values listed in the my_command.h header file. For example, "Create DB" and "Change user" correspond to COM_CREATE_DB and COM_CHANGE_USER, respectively.

RECORD_ID

A unique identifier for the audit record. The value is composed from a sequence number and timestamp, in the format SEQ_TIMESTAMP. When the audit log plugin opens the audit log file, it initializes the sequence number to the size of the audit log file, then increments the sequence by 1 for each record logged. The timestamp is a UTC value in YYYY-MM-DDThh:mm:ss format indicating the date and time when the audit log plugin opened the file.

Example: RECORD_ID="12_2017-10-16T14:25:00"

TIMESTAMP

A string representing a UTC value in YYYY-MM-DDThh:mm:ss UTC format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a TIMESTAMP value occurring after the statement finishes, not when it was received.

Example: TIMESTAMP="2017-10-16T14:25:32 UTC"

COMMAND_CLASS

A string that indicates the type of action performed.

Example: COMMAND_CLASS="drop_table"

The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

CONNECTION_ID

An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

Example: CONNECTION_ID="127"

CONNECTION_TYPE

The security state of the connection to the server. Permitted values are TCP/IP (TCP/IP connection established without encryption), SSL/TLS (TCP/IP connection established with encryption), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), and Shared Memory (Windows shared memory connection).

Example: CONNECTION_TYPE="SSL/TLS"

DB

A string representing the default database name.

Example: DB="test"

HOST

A string representing the client host name.

Example: HOST="localhost"

IP

A string representing the client IP address.

Example: IP="127.0.0.1"

MYSQL_VERSION

A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

Example: MYSQL_VERSION="5.7.21-log"

OS_LOGIN

A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this attribute is empty. The value is the same as that of the external_user system variable (see Section 6.2.18, “Proxy Users”).

Example: OS_LOGIN="jeffrey"

OS_VERSION

A string representing the operating system on which the server was built or is running.

Example: OS_VERSION="x86_64-Linux"

PRIV_USER

A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and it may differ from the USER value.

Example: PRIV_USER="jeffrey"

PROXY_USER

A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

Example: PROXY_USER="developer"

SERVER_ID

An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

Example: SERVER_ID="1"

SQLTEXT

A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

Example: SQLTEXT="DELETE FROM t1"

STARTUP_OPTIONS

A string representing the options that were given on the command line or in option files when the MySQL server was started.

Example: STARTUP_OPTIONS="--port=3306 --log_output=FILE"

STATUS

An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function. See the description for STATUS_CODE for information about how it differs from STATUS.

The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

Warnings are not logged.

Example: STATUS="1051"

STATUS_CODE

An unsigned integer representing the command status: 0 for success, 1 if an error occurred.

The STATUS_CODE value differs from the STATUS value: STATUS_CODE is 0 for success and 1 for error, which is compatible with the EZ_collector consumer for Audit Vault. STATUS is the value of the mysql_errno() C API function. This is 0 for success and nonzero for error, and thus is not necessarily 1 for error.

Example: STATUS_CODE="0"

USER

A string representing the user name sent by the client. This may differ from the PRIV_USER value.

VERSION

An unsigned integer representing the version of the audit log file format.

Example: VERSION="1"

Some items appear in every audit record. Others are optional and may appear depending on the audit record type.

Order of items within an audit record is not guaranteed.

Item values are not fixed length. Long values may be truncated as indicated in the item descriptions given later.

The " and \ characters are encoded as \" and \\, respectively.

account

The MySQL account associated with the event. The value is a hash containing these items equivalent to the value of the CURRENT_USER() function within the section: user, host.

Example:

"account": { "user": "root", "host": "localhost" }

class

A string representing the event class. The class defines the type of event, when taken together with the event item that specifies the event subclass.

Example:

"class": "connection"

The following table shows the permitted combinations of class and event values.

connection_data

Information about a client connection. The value is a hash containing these items: connection_type, status, db. This item occurs only for audit records with a class value of connection.

Example:

"connection_data": { "connection_type": "ssl",
                     "status": 0,
                     "db": "test" }

connection_id

An unsigned integer representing the client connection identifier. This is the same as the value returned by the CONNECTION_ID() function within the session.

Example:

"connection_id": 5

event

A string representing the subclass of the event class. The subclass defines the type of event, when taken together with the class item that specifies the event class. For more information, see the class item description.

Example:

"event": "connect"

general_data

Information about an executed statement or command. The value is a hash containing these items: command, sql_command, query, status. This item occurs only for audit records with a class value of general.

Example:

"general_data": { "command": "Query",
                  "sql_command": "show_variables",
                  "query": "SHOW VARIABLES",
                  "status": 0 }

id

An unsigned integer representing an event ID.

Example:

"id": 2

For audit records that have the same timestamp value, their id values distinguish them and form a sequence. Within the audit log, timestamp/id pairs are unique. These pairs are bookmarks that identify event locations within the log.

login

Information indicating how a client connected to the server. The value is a hash containing these items: user, os, ip, proxy.

Example:

"login": { "user": "root", "os": "", "ip": "::1", "proxy": "" }

shutdown_data

Information pertaining to audit log plugin termination. The value is a hash containing these items: server_id This item occurs only for audit records with class and event values of audit and shutdown, respectively.

Example:

"shutdown_data": { "server_id": 1 }

startup_data

Information pertaining to audit log plugin initialization. The value is a hash containing these items: server_id, os_version, mysql_version, args. This item occurs only for audit records with class and event values of audit and startup, respectively.

Example:

"startup_data": { "server_id": 1,
                  "os_version": "i686-Linux",
                  "mysql_version": "5.7.21-log",
                  "args": ["/usr/local/mysql/bin/mysqld",
                           "--loose-audit-log-format=JSON",
                           "--log-error=log.err",
                           "--pid-file=mysqld.pid",
                           "--port=3306" ] }

table_access_data

Information about an access to a table. The value is a hash containing these items: db, table, query, sql_command, This item occurs only for audit records with a class value of table_access.

Example:

"table_access_data": { "db": "test",
                       "table": "t1",
                       "query": "INSERT INTO t1 (i) VALUES(1),(2),(3)",
                       "sql_command": "insert" }

timestamp

A string representing a UTC value in YYYY-MM-DD hh:mm:ss format indicating the date and time when the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a timestamp value occurring after the statement finishes, not when it was received.

Example:

"timestamp": "2018-01-15 13:50:01"

For audit records that have the same timestamp value, their id values distinguish them and form a sequence. Within the audit log, timestamp/id pairs are unique. These pairs are bookmarks that identify event locations within the log.

args

An array of options that were given on the command line or in option files when the MySQL server was started. The first option is the path to the server executable.

Example:

"args": ["/usr/local/mysql/bin/mysqld",
         "--loose-audit-log-format=JSON",
         "--log-error=log.err",
         "--pid-file=mysqld.pid",
         "--port=3306" ]

command

A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.

Example:

"command": "Query"

connection_type

The security state of the connection to the server. Permitted values are tcp/ip (TCP/IP connection established without encryption), ssl (TCP/IP connection established with encryption), socket (Unix socket file connection), named_pipe (Windows named pipe connection), and shared_memory (Windows shared memory connection).

Example:

"connection_type": "tcp/tcp"

db

A string representing a database name. For connection_data, it is the default database. For table_access_data, it is the table database.

Example:

"db": "test"

host

A string representing the client host name.

Example:

"host": "localhost"

ip

A string representing the client IP address.

Example:

"ip": "::1"

mysql_version

A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable.

Example:

"mysql_version": "5.7.21-log"

os

A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this attribute is empty. The value is the same as that of the external_user system variable. See Section 6.2.18, “Proxy Users”.

Example:

"os": "jeffrey"

os_version

A string representing the operating system on which the server was built or is running.

Example:

"os_version": "i686-Linux"

proxy

A string representing the proxy user (see Section 6.2.18, “Proxy Users”). The value is empty if user proxying is not in effect.

Example:

"proxy": "developer"

query

A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.

Example:

"query": "DELETE FROM t1"

server_id

An unsigned integer representing the server ID. This is the same as the value of the server_id system variable.

Example:

"server_id": 1

sql_command

A string that indicates the SQL statement type.

Example:

"sql_command": "insert"

The values correspond to the statement/sql/xxx command counters. For example, xxx is drop_table and select for DROP TABLE and SELECT statements, respectively. The following statement displays the possible names:

status

An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function.

The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3.1, “Server Error Message Reference”.

Warnings are not logged.

Example:

"status": 1051

table

A string representing a table name.

Example:

"table": "t1"

user

A string representing a user name. The meaning differs depending on the item within which user occurs:

Example:

"user": "root"