Mysql database audit

Mysql database audit

profile

Database Audit (DBAudit) can record database activities on the network in real time, conduct fine-grained audit compliance management on database operations, warn database of risk behaviors, and block attack behaviors. It records, analyzes and reports user access to the database to help users generate compliance reports afterwards, trace accidents, and strengthen internal and external database network behavior records to improve data asset security.

In MySQL Database (version 5.5), a new plug-in has been added: Audit plugin for auditing database connections and database operations. The plug-in was perfected in mysql 5.6.20 Enterprise Edition and later versions.

In production we use MySQL for the community free version, can not use the enterprise version of the plug-in, for this we use the audit plug-in provided by the Mariadb community.

Note: percona, mcafee, mariadb all provide audit plug-ins. The three plugins are installed in the same way.

Database Version

Mysql Version: Mysql 5.6.26

Installation: binary

Plugin source: mariadb

Server_audit.so file obtained from mariadb version

Download address: mariadb.com/resources/downloads

plug-in installation

Plugins can be loaded in two ways:

[mysqld]

plugin-load=server_audit.so

If you want the plugin not to be removed during runtime, configure it as follows

1,.my.cnf file configuration

[mysqld]

plugin-load=server_audit.so

audit-log=FORCE_PLUS_PERMANENT

2: Dynamic plug-in installation process

###Query MySQL plugin installation location 1. show global variables like '%plugin_dir%'; 2. Copy the server_audit.so file to the path above 3. Load server_audit.so plugin mysql> install plugin server_audit soname 'server_audit.so'; ####Query whether the plug-in is loaded successfully mysql> show plugins ###Configuration Parameters 4. mysql > show global variables like '%server_audit%'; ###Audit query: [CONNECTION,QUERY,TABLE,QUERY_DDL,QUERY_DML] 4. mysql > set global server_audit_events='QUERY'; 5. #Open audit function 6. set global server_audit_LOGGING='ON'; ###Testing 1. mysql >select count(*) from serviceCount; 2. mysql >select count(*) from serviceCo; 3. ERROR 1146 (42S02): Table 'test.serviceCo' doesn't exist 4. more server_audit.log 5. 20160523 10:24:26,10-0-10-70,root,localhost,615,172,QUERY,test,'select count(*) from serviceCount',0 6. 20160523 10:29:11,10-0-10-70,root,localhost,615,174,QUERY,test,'select count(*) from serviceCo',1146 3. View all loaded plugins select * from INFORMATION_SCHEMA.PLUGINS\G

4. Important configuration parameters:

server_audit = FORCE_PLUS_PERMANENT

server_audit_events ='CONNECT,QUERY,TABLE'

server_audit_logging =ON

server_audit_incl_users =root

server_audit_file_rotate_size = 10G

server_audit_file_path = /data/mysql/server_audit.log

All configuration parameters:

Reference address:

https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

server_audit_events

server_audit_excl_users

server_audit_file_path

server_audit_file_rotate_now

server_audit_file_rotate_size

server_audit_file_rotations

server_audit_incl_users

server_audit_logging

server_audit_mode

server_audit_output_type

server_audit_query_log_limit

server_audit_syslog_facility

server_audit_syslog_ident

server_audit_syslog_info

server_audit_syslog_priority