First to provide you a way to track user accessing sensible data
\nSecondly to investigate on suspicious queries in all your critical databases
\nThirdly to comply with law and industry standards<\/p>\n
The MariaDB Audit Plugin can help you to log all or part of the server activity as:
\n– who was connected and at which time
\n– which databases and tables were accessed
\n– which action\/event (CONNECT, TABLE,…)
\n– which queries were run
\nAll of them can be stored in a dedicated audit log file<\/p>\n
This Plugin provides auditing not only for MariaDB where it is included by default, but also for Percona Server and
\neven for Oracle MySQL when using the community version<\/p>\n
The MariaDB Audit Plugin library is shipped with the MariaDB server You can either set them manually with SET GLOBAL but I recommend to define them in the option file (my.cnf) In the Audit log file directory, you will find one current audit file and 9 archived audit log file as defined by the parameter server_audit_file_rotations<\/strong> You can check the latest records in the current one It is quite the same as on MariaDB You can take exactly the same audit system variables defined for MariaDB<\/p>\n The MariaDB Auditing Plugin is really quick and easy to install Why should you Audit your MySQL Instances? First to provide you a way to track user accessing sensible data Secondly to investigate on suspicious queries in all your critical databases Thirdly to comply with law and industry standards The MariaDB Audit Plugin can help you to log all or part of the server activity as: […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-9102","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\n
\nOnce the server is installed, you need still to locate your plugin directory and install\/load it
\nmysqld7-[MariaDB]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
\n+---------------+-----------------------------------------------------------------+
\n| Variable_name | Value |
\n+---------------+-----------------------------------------------------------------+
\n| plugin_dir | \/u00\/app\/mysql\/product\/mariadb-10.1.16-linux-x86_64\/lib\/plugin\/ |
\n+---------------+-----------------------------------------------------------------+
\nmysqld7-[MariaDB]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';
\n<\/code>
\nCheck then if it has been loaded
\nmysqld7-[MariaDB]>SELECT * from information_schema.plugins where plugin_name='server_audit'G
\n*************************** 1. row ***************************
\n PLUGIN_NAME: SERVER_AUDIT
\n PLUGIN_VERSION: 1.4
\n PLUGIN_STATUS: ACTIVE
\n PLUGIN_TYPE: AUDIT
\n PLUGIN_TYPE_VERSION: 3.2
\n PLUGIN_LIBRARY: server_audit.so
\nPLUGIN_LIBRARY_VERSION: 1.11
\n PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
\n PLUGIN_DESCRIPTION: Audit the server activity
\n PLUGIN_LICENSE: GPL
\n LOAD_OPTION: FORCE_PLUS_PERMANENT
\n PLUGIN_MATURITY: Stable
\n PLUGIN_AUTH_VERSION: 1.4.0<\/code><\/p>\nConfiguration of the important audit system variables<\/h3>\n
\n#
\n## Audit Plugin MariaDB
\n#
\nserver_audit_events = CONNECT,QUERY,TABLE # specifies the types of events to log
\nserver_audit_logging = ON # Enable logging
\nserver_audit = FORCE_PLUS_PERMANENT # Load the plugin at startup & prevent it from beeing removed
\nserver_audit_file_path = \/u00\/app\/mysql\/admin\/mysqld8\/log\/mysqld8-audit.log # Path & log name
\nserver_audit_output_type = FILE # separate log file
\nserver_audit_file_rotate_size = 100000 # Limit of the log size in Bytes before rotation
\nserver_audit_file_rotations = 9 # Number of audit files before the first will be overwritten<\/strong>
\nserver_audit_excl_users = root # User(s) not audited<\/code>
\nRestart the server and check the audit system variables
\nmysqld7-[MariaDB]>show global variables like \"server_audit%\";
\n+-------------------------------+----------------------------------------------------+
\n| Variable_name | Value |
\n+-------------------------------+----------------------------------------------------+
\n| server_audit_events | CONNECT,QUERY,TABLE |
\n| server_audit_excl_users | root |
\n| server_audit_file_path | \/u00\/app\/mysql\/admin\/mysqld7\/log\/mysqld7-audit.log |
\n| server_audit_file_rotate_now | OFF |
\n| server_audit_file_rotate_size | 10000 |
\n| server_audit_file_rotations | 9 |
\n| server_audit_incl_users | sme |
\n| server_audit_loc_info | |
\n| server_audit_logging | ON |
\n| server_audit_mode | 0 |
\n| server_audit_output_type | file |
\n| server_audit_query_log_limit | 1024 |
\n| server_audit_syslog_facility | LOG_USER |
\n| server_audit_syslog_ident | mysql-server_auditing |
\n| server_audit_syslog_info | |
\n| server_audit_syslog_priority | LOG_INFO |
\n+-------------------------------+----------------------------------------------------+
\n16 rows in set (0.00 sec)<\/code><\/p>\nAudit log file<\/h3>\n
\nmysql@MariaDB:\/u00\/app\/mysql\/admin\/mysqld7\/log\/ [mysqld7] ll
\ntotal 344
\n-rw-rw----. 1 mysql mysql 242 Oct 13 10:35
\n-rw-rw----. 1 mysql mysql 556 Oct 13 10:35 mysqld7-audit.log
\n-rw-rw----. 1 mysql mysql 10009 Oct 13 10:35 mysqld7-audit.log.1
\n-rw-rw----. 1 mysql mysql 10001 Oct 12 14:22 mysqld7-audit.log.2
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:47 mysqld7-audit.log.3
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:41 mysqld7-audit.log.4
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:34 mysqld7-audit.log.5
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:28 mysqld7-audit.log.6
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:22 mysqld7-audit.log.7
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:15 mysqld7-audit.log.8
\n-rw-rw----. 1 mysql mysql 10033 Oct 12 13:09 mysqld7-audit.log.9<\/code><\/p>\n
\nmysqld7-[MariaDB]>tail -f mysqld7-audit.log
\n20161013 14:22:57,MYSQL,sme,localhost,31,179,QUERY,employees,'create tables tst(name varchar(20))',1064
\n20161013 14:23:05,MYSQL,sme,localhost,31,180,CREATE,employees,tst,
\n20161013 14:23:05,MYSQL,sme,localhost,31,180,QUERY,employees,'create table tst(name varchar(20))',0
\n20161013 14:23:41,MYSQL,sme,localhost,31,181,WRITE,employees,tst,
\n20161013 14:23:41,MYSQL,sme,localhost,31,181,QUERY,employees,'insert into tst values('toto')',0
\n20161013 14:24:26,MYSQL,sme,localhost,31,182,WRITE,employees,tst,
\n20161013 14:24:26,MYSQL,sme,localhost,31,182,QUERY,employees,'update tst set name='titi'',0
\n20161013 14:24:53,MYSQL,sme,localhost,31,183,QUERY,employees,'delete from tst',1142
\n20161013 14:48:34,MYSQL,sme,localhost,33,207,READ,employees,tst,
\n20161013 14:48:34,MYSQL,sme,localhost,33,207,QUERY,employees,'select * from tst',0
\n20161013 15:35:16,MYSQL,sme,localhost,34,0,FAILED_CONNECT,,,1045
\n20161013 15:35:16,MYSQL,sme,localhost,34,0,DISCONNECT,,,0
\n20161013 15:35:56,MYSQL,sme,localhost,35,0,CONNECT,,,0
\n20161013 15:35:56,MYSQL,sme,localhost,35,210,QUERY,,'select @@version_comment limit 1',0
\n20161013 15:36:03,MYSQL,sme,localhost,35,0,DISCONNECT,,,0<\/code>
\nThe audit log file which is a plain-text format contains the following commas separated fields
\ntimestamp : 20161012 10:33:58
\nserverhost : MYSQL
\nuser : sme
\nhost : localhost
\nconnection id: 9
\nquery id : 77
\noperation : QUERY
\ndatabase : employees
\nobject : ‘show databases’ # Executed query if QUERY or table name if TABLE operation.
\nreturn code : 0<\/p>\nInstallation on Percona or MySQL<\/h3>\n
\nOnce your server is installed, you have to look after your plugin directory
\nmysqld8-[Percona]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
\n+---------------+--------------------------------------------------------------------------------------+
\n| Variable_name | Value |
\n+---------------+--------------------------------------------------------------------------------------+
\n| plugin_dir | \/u00\/app\/mysql\/product\/Percona-Server-5.7.14-7-Linux.x86_64.ssl101\/lib\/mysql\/plugin\/ |
\n+---------------+--------------------------------------------------------------------------------------+
\n
\nmysqld10-[MySQL]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
\n+---------------+-----------------------------------------------------------------------+
\n| Variable_name | Value |
\n+---------------+-----------------------------------------------------------------------+
\n| plugin_dir | \/u00\/app\/mysql\/product\/mysql-5.6.14-linux-glibc2.5-x86_64\/lib\/plugin\/ |
\n+---------------+-----------------------------------------------------------------------+<\/code>
\nthen copy the MariaDB plugin server_audit.so in the Percona\/MySQL plugin directory
\nmysql@Percona:[mysqld8] cp \/u00\/app\/mysql\/product\/mariadb-10.1.16-linux-x86_64\/lib\/plugin\/server_audit.so
\n\/u00\/app\/mysql\/product\/Percona-Server-5.7.14-7-Linux.x86_64.ssl101\/lib\/mysql\/plugin\/
\n
\nmysql@MySQL:[mysqld10] cp \/u00\/app\/mysql\/product\/mariadb-10.1.16-linux-x86_64\/lib\/plugin\/server_audit.so
\n\/u00\/app\/mysql\/product\/mysql-5.6.14-linux-glibc2.5-x86_64\/lib\/plugin\/<\/code>
\nInstall\/load the plugin & check
\nmysqld8-[(Percona)]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';
\nmysqld8-[Percona]>SELECT * from information_schema.plugins where plugin_name='server_audit'G
\n*************************** 1. row ***************************
\n PLUGIN_NAME: SERVER_AUDIT
\n PLUGIN_VERSION: 1.4
\n PLUGIN_STATUS: ACTIVE
\n PLUGIN_TYPE: AUDIT
\n PLUGIN_TYPE_VERSION: 4.1
\n PLUGIN_LIBRARY: server_audit.so
\nPLUGIN_LIBRARY_VERSION: 1.4
\n PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
\n PLUGIN_DESCRIPTION: Audit the server activity
\n PLUGIN_LICENSE: GPL
\n LOAD_OPTION: FORCE_PLUS_PERMANENT
\n
\nmysqld10-[MySQL]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';
\nmysqld10-[MySQL]>SELECT * from information_schema.plugins where plugin_name='server_audit'G
\n**************************** 1. row ***************************
\n PLUGIN_NAME: SERVER_AUDIT
\n PLUGIN_VERSION: 1.4
\n PLUGIN_STATUS: ACTIVE
\n PLUGIN_TYPE: AUDIT
\n PLUGIN_TYPE_VERSION: 3.2
\n PLUGIN_LIBRARY: server_audit.so
\nPLUGIN_LIBRARY_VERSION: 1.4
\n PLUGIN_AUTHOR: Alexey Botchkov (MariaDB Corporation)
\n PLUGIN_DESCRIPTION: Audit the server activity
\n PLUGIN_LICENSE: GPL
\n LOAD_OPTION: FORCE_PLUS_PERMANENT<\/code><\/p>\nConfiguration of the important audit system variables<\/h3>\n
Conclusion<\/h3>\n
\nIt is a good and cheap auditing solution and can be installed on different distributions
\nIt lets you see exactly what SQL queries are being processed
\nAuditing information can really help you to track suspicious queries, detect mistakes and overall troubleshoot abnormal activity<\/p>\n","protected":false},"excerpt":{"rendered":"