Mysql5.6 audit function, mysql5.6 Audit
1. Preface for security and operation traceability considerations, more and more companies have added audit functions. Mysql5.5 has released related audit functions. The 5.6.20 function is barely available. Although the fine-grained aspect is not very good, you can still look forward to it in subsequent versions. This section describes the related functions and features.
2. Enable audit 2.1 The audit file in mysql5.6 is loaded using the audit_log plug-in. We can load this plug-in the configuration file to enable it.
[mysqld] plugin-load=audit_log.so
If you want the database to enable the audit function forcibly, if it is not enabled, the server will not start, or if the audit function cannot be enabled, the server will be suspended and added
[mysqld] plugin-load=audit_log.so audit-log=FORCE_PLUS_PERMANENT
2.2 to enable the agent list audit function, install the audit agent in the command line. Make sure that the database plug-in directory contains audit_log.so. [The default directory of the i686 database plug-in is/usr/lib/mysql/plugin. You can also specify the plugin_dir parameter]
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
3. Parameter introduction audit parameters are as follows:
3.1 audit_log_buffer_size: Audit cache. It is recommended to set this parameter to a multiple of 4096. This parameter takes effect only when audit_log_strategy is ASYNCHRONOUS.
3.2 audit_log_connection_policyaudit_log_connection_policy: records the Connection audit information. Contains three parameters
| Value |
Description |
ALL |
Log all connection events |
ERRORS |
Log only failed connection events |
NONE |
Do not log connection events |
If audit_log_policy is set, it may be overwritten. 3.3 audit_log_current_sessionaudit_log_current_session: indicates whether the current session is audited. It is a read-only parameter and can only be controlled through audit_log_exclude_accounts and audit_log_include_accounts.
3.4 audit_log_exclude_accounts/audit_log_include_accountsaudit_log_exclude_accounts: controls where users do not enter audit. String types can be separated by commas by default.
Audit_log_include_accounts: controls where users can enter audit. String types can be separated by commas by default.
Exclude and include have only one parameter. 3.5 audit_log_fileaudit_log_file: used to control the name and path of audit logs.
3.6 audit_log_flushaudit_log_flush: Controls audit log archiving only when audit_log_rotate_on_size = 0. After manually renaming audit log archiving, you can specify audit_log_flush = 1 to generate new audit logs.
3.7 audit_log_formataudit_log_format: audit log format, which can be OLD or NEW (NEW format is 5.6.14 ). To change the format, perform the following three steps:
1: Disable database 2: Rename the current audit. log File
3: Change the audit_log_format parameter and restart mysql. A new audit. log file is automatically generated after the restart.
Prevent the NEW format and OLD format from being in the same audit log, resulting in an audit function error. Audit_log_policy: record the audit log control policy:
| Value |
Description |
ALL |
Log all events |
LOGINS |
Log only login events |
QUERIES |
Log only query events |
NONE |
Log nothing (disable the audit stream |
3.8 audit_log_statement_policyaudit_log_statement_policy: indicates the Audit Policy of the statement, which may be overwritten by audit_log_policy:
| Value |
Description |
ALL |
Log all statement events |
ERRORS |
Log only failed statement events |
NONE |
Do not log statement events |
3.9 audit_log_rotate_on_sizeaudit_log_rotate_on_size: size of the audit log file. When the parameter is greater than 0, when the audit log exceeds the limit, it is automatically renamed as a log file with a timestamp suffix. Create a new audit log at the same time.
3.10 audit_log_strategyaudit_log_strategy: Audit Log refresh policies are divided:
| Value |
Meaning |
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, callsync()After each request |
4. log format audit log format is XML, and NEW is more detailed than OLD labels. The specific tag information is as follows: instance:
<?xml version="1.0" encoding="UTF-8"?><AUDIT> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:24 UTC</TIMESTAMP> <RECORD_ID>1_2013-09-17T15:03:24</RECORD_ID> <NAME>Audit</NAME> <SERVER_ID>1</SERVER_ID> <VERSION>1</VERSION> <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld --socket=/usr/local/mysql/mysql.sock --port=3306</STARTUP_OPTIONS> <OS_VERSION>x86_64-osx10.6</OS_VERSION> <MYSQL_VERSION>5.7.2-m12-log</MYSQL_VERSION> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:40 UTC</TIMESTAMP> <RECORD_ID>2_2013-09-17T15:03:24</RECORD_ID> <NAME>Connect</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>connect</COMMAND_CLASS> <PRIV_USER>root</PRIV_USER> <PROXY_USER></PROXY_USER> <DB>test</DB> </AUDIT_RECORD>... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>4_2013-09-17T15:03:24</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>drop_table</COMMAND_CLASS> <SQLTEXT>DROP TABLE IF EXISTS t</SQLTEXT> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>5_2013-09-17T15:03:24</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>create_table</COMMAND_CLASS> <SQLTEXT>CREATE TABLE t (i INT)</SQLTEXT> </AUDIT_RECORD>... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP> <RECORD_ID>7_2013-09-17T15:03:24</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>2</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER></USER> <OS_LOGIN></OS_LOGIN> <HOST></HOST> <IP></IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD>... <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP> <RECORD_ID>9_2013-09-17T15:03:24</RECORD_ID> <NAME>Shutdown</NAME> <CONNECTION_ID>3</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS></COMMAND_CLASS> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP> <RECORD_ID>10_2013-09-17T15:03:24</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>3</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER></USER> <OS_LOGIN></OS_LOGIN> <HOST></HOST> <IP></IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2013-09-17T15:03:49 UTC</TIMESTAMP> <RECORD_ID>11_2013-09-17T15:03:24</RECORD_ID> <NAME>NoAudit</NAME> <SERVER_ID>1</SERVER_ID> </AUDIT_RECORD></AUDIT>
<Audit>: the root tag of the file is <AUDIT> and the end tag is </AUDIT>.
<AUDIT_RECORD>: contains a series of required and optional tags. Whether or not the optional tags appear depends on the audit record type.
<NAME>: required, for example, <NAME> Query </NAME>. The possible values include 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.
<RECORD_ID>: required. For example, <RECORD_ID> 28743_2013-09-18T21: 03: 24 </RECORD_ID> contains column numbers and timestamps. numbers represent the number of records. Each time a record is added, number plus 1.
<TIMESTAMP>: required. For example, <TIMESTAMP> 2013-09-17T15: 03: 49 UTC </TIMESTAMP> contains the TIMESTAMP and time zone, the time at which the SQL statements received from the client are executed is recorded.
The following tag audit record type determines whether or not it appears
<COMMAND_CLASS>: Command type. For example, <COMMAND_CLASS> drop_table </COMMAND_CLASS>.
<CONNECTION_ID>: for example, <CONNECTION_ID> 127 </CONNECTION_ID> represents the unsigned integer number of the Client Connection identifier.
<DB>: the default database NAME for mysql connection. This label only appears when the <NAME> value is Connect or Change user.
<HOST>: host name of the client. This label only appears when the <NAME> value is Connect, Change user, or Query, for example, <HOST> localhost </HOST>.
<IP>: the IP address of the client. This label only appears when the <NAME> value is Connect, Change user, or Query, for example, <IP> 127.0.0.1 </IP>.
<MYSQL_VERSION>: mysql version number that appears only when the <NAME> value is Audit, for example, <MYSQL_VERSION> 5.7.1-m11-log </MYSQL_VERSION>
<OS _LOGIN>: external user. This label only appears when the <NAME> value is Connect, Change user, or Query.
<OS _VERSION>: indicates the operating system of the server on which the database is running, only when the <NAME> value is Audit, for example, <OS _VERSION> x86_64-Linux </OS _VERSION>.
<PRIV_USER>: the name of the server-authenticated client. This label only appears when the <NAME> value is Connect or Change user. For example, <PRIV_USER> root </PRIV_USER>.
<PROXY_USER>: the user who connects to mysql through proxy. This label only appears when the <NAME> value is Connect or Change user.
<SERVER_ID>: ID of the mysql database server. This label only appears when the <NAME> value is Audit or No Audit. For example, <SERVER_ID> 1 </SERVER_ID>.
<SQLTEXT>: the SQL statement actually executed. This label only appears when the <NAME> value is Query or Execute. For example, <SQLTEXT> delete from t1 </SQLTEXT>.
<STARTUP_OPTIONS>: mysql database startup option. This label only appears when the <NAME> value is Audit, for example, <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld -- port = 3306 -- log-output = FILE </STARTUP_OPTIONS>
<STATUS>: indicates the execution STATUS of the SQL command. 0 indicates that the SQL command is successful, and the other indicates that the SQL command is incorrect. Example: <STATUS> 1051 </STATUS>.
<STATUS_CODE>: indicates the execution status of SQL commands. 0 indicates successful, and 1 indicates an error. For example, <STATUS_CODE> 0 </STATUS_CODE>.
<USER>: the USER name used to connect the client to the mysql server. For example, <USER> root [root] @ localhost [127.0.0.1] </USER>.
<VERSION>: indicates the VERSION number of the log file format. This label only appears when the <NAME> value is Audit. For example, <VERSION> 1 </VERSION>.
5. Audit restrictions audit logs are stored in the data path by default. Because the XML file is not encrypted, we recommend that you specify a special path through parameters on the official website and set the corresponding personnel permissions for security control. In addition, the audit function cannot be recorded in the following situations: 1) only top-level statements can be audited. stored programs such as stored procedures, triggers, and functions are not audited; 2) Statements involving external files cannot be audited, such as load data infile.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
