MARIADB Operational Audits

MARIADB can enable audit plug-ins to audit the various operations of the database in case of operation and maintenance of the back pot!

Enable the audit plugin for mariadb and adjust the relevant parameters

MariaDB [(None)]> show variables like '%audit% '; Empty Set (0.02 sec)

Installing the MARIADB Audit plugin

MariaDB [(None)]> INSTALL PLUGIN server_audit SONAME ' Server_audit '; Query OK, 0 rows affected (0.41 sec)

To view the variables after installing the audit plugin

mariadb [(none)]> show variables like  '%audit% '; +-------------------------------+- ----------------------+| variable_name                  | Value                  |+-------------------------------+-----------------------+|  server_audit_events           |                         | |  server_audit_excl_users       |                        | |  server_audit_file_path        | server_audit.log        | |  server_audit_file_rotate_now  | OFF                    | |  server_audit_file_rotate_size | 1000000                | |  server_audit_file_rotations   | 9                      | |  server_audit_incl_users       |                        | |  server_audit_loc_info         |                         | |  server_audit_logging          | off                    | |  server_audit_mode             | 0                       | |  server_audit_output_type      | file                   | |  server_audit_query_log_limit  | 1024                   | |  server_audit_syslog_facility  | LOG_USER               | |  server_audit_syslog_ident     | mysql-server_auditing | |  server_audit_syslog_info      |                        | |  server_audit_syslog_priority  | LOG_INFO               |

Set relevant parameters and description:

1. Setting Logged Events

MariaDB [(None)]> set global server_audit_events= ' connect,query,table '; Query OK, 0 rows Affected (0.00 sec)

Note: Connect,query,table can meet all of our audit needs

Refer to the relevant event description: https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

2. Specify that actions for certain users are not audited

MariaDB [(None)]> set global server_audit_excl_users= ' Liuwei '; Query OK, 0 rows affected (0.01 sec)

3. Location of audit log storage

Server_audit_file_path | Server_audit.log

4. Set Log rotation

MariaDB [(None)]> set global server_audit_file_rotate_now=on; Query OK, 0 rows Affected (0.00 sec)

5. Set the audit log size

MariaDB [(None)]> set global server_audit_file_rotate_size=1024*1024*1024; Query OK, 0 rows affected (0.03 sec)

6. Open Audit log

MariaDB [(None)]> set global server_audit_logging=on; Query OK, 0 rows Affected (0.00 sec)

7. Set the number of logs that can be rotated

server_audit_file_rotations | 9

To view the parameters after Setup:

mariadb [(none)]> show variables like  '%audit% '; +-------------------------------+- ----------------------+| variable_name                  | Value                  |+-------------------------------+-----------------------+|  server_audit_events           | query,table            | |  server_audit_excl_users       | liuwei                 | |  server_audit_file_path        | server_audit.log       | |  server_audit_file_rotate_now  | off                   | |  server_audit_file_rotate_size | 1073741824             | |  server_audit_file_rotations   | 9                      | |  server_audit_incl_users       |                        | |  server_audit_loc_info         |                         | |  server_audit_logging          | ON                     | |  server_audit_mode             | 0                       | |  server_audit_output_type      | file                   | |  server_audit_query_log_limit  | 1024                   | |  server_audit_syslog_facility  | LOG_USER               | |  server_audit_syslog_ident     | mysql-server_auditing | |  server_audit_syslog_info      |                       | |  server_audit_syslog_priority  | LOG_INFO               |+-------------------------------+-----------------------+

to see if the audit log is recorded: can record additions and deletions and change all operations

[[Email protected] mysql]# cat server_audit.log 20160705 16:25:24,test2,root, Localhost,9,2544,query,, ' Set global server_audit_logging=on ', 020160705 16:25:38,test2,root, Localhost,9,2545,query,, ' show variables like \ '%audit%\ ', 020160705 16:28:06,test2,root, Localhost,9,2546,query,, ' select database () ', 020160705 16:28:10,test2,root,localhost,9,2548,query,, ' Show databases ', 020160705 16:28:44,test2,root,localhost,9,2549,query,, ' Create database v1 ' , 0[[email protected] mysql]# tailf server_audit.log20160705 16:25:24,test2,root, Localhost,9,2544,query,, ' Set global server_audit_logging=on ', 020160705 16:25:38,test2,root, Localhost,9,2545,query,, ' show variables like \ '%audit%\ ', 020160705 16:28:06,test2,root, Localhost,9,2546,query,, ' select database () ', 020160705 16:28:10,test2,root,localhost,9,2548,query,, ' Show databases ', 020160705&NBsp;16:28:44,test2,root,localhost,9,2549,query,, ' create database v1 ', 020160705 16:31:20,test2, Root,localhost,9,2552,query,, ' set global server_audit_events=\ ' connect,query,table\ ', 020160705  16:31:30,test2,root,localhost,9,2553,query,, ' show variables like \ '%audit%\ ', 020160705  16:31:35,test2,root,localhost,9,0,disconnect,,, 020160705 16:31:51,test2,root,localhost,24,0,connect ,,, 020160705 16:31:51,test2,root,localhost,24,2555,query,, ' select @ @version_comment  limit  1 ', 020160705 16:33:44,test2,root,192.168.10.215,25,0,failed_connect,,, 104520160705 16:33:44,test2, Root,192.168.10.215,25,0,disconnect,,, 020160705 16:33:53,test2,liuwei,192.168.10.215,26,0,connect,,, 020160705 16:35:21,test2,root,localhost,24,2561,query,, ' show variables like \ '%audit%\ ', 020160705 16:40:22,test2,root,localhost,24,2563,write,mysql,user,20160705 16:40:22,test2,root, Localhost,24,2563,write,mysql,db, 20160705 16:40:22,test2,root,localhost,24,2563,query,mysql, ' grant all on *.* to  [email protected]\ '%\ '  identified by ***** ', 0

Configure the audit parameters in the configuration file because the global restart Expires:

[Mysqld]

server_audit_events= ' connect,query,table '

Server_audit_logging=on

server_audit_file_rotate_size=200000

server_audit_file_rotations=10

server_audit_excl_users= ' Liuwei '

Restart takes effect

MARIADB Operational Audits