Integrating MariaDB Audit plugin in MySQL 5.7

Aug 30, 2020

Mins to Read

All

‍

Basically, Auditing is the process of monitoring and recording the activity happening inside the Server. MariaDB Audit Plugin works with MariaDB, MySQL and Percona Servers. It is used to log the database Operations like, SQL Statements, User informations, Connections. The information is stored in a user defined log file or in Syslog.

Available audit plugins for MySQL.

MariaDB Audit plugin is easier to implement and it has a better filtering option. Most of the options are online and do not need a server restart. ( old versions have serious bugs). This plugin is compatible with Oracle MySQL, Percona Server and MariaDB. Even Amazon RDS uses MariaDB Audit plugin. We have implemented it with MySQL 5.7 latest. ( MariaDB Audit plugin do not support 8.0 ).

Installation:

In LINUX MariaDB audit plugin installation contains below simple steps.

Step I)

Download the tar archive file for Audit plugin from MariaDB.

Step II)

Extract the downloaded tar archive file.

Step III)

Copy the plugin to the plugin directory in MySQL based on your linux server (64 bit/32 bit).

 
cp server_audit-1.4.0/linux-x86-64/server_audit.so /usr/lib64/mysql/plugin/

chown -R mysql.mysql  /usr/lib64/mysql/plugin/server_audit.so

Step IV)

Install the MariaDB Audit Plugin into the MySQL Server.

Syntax:

 
INSTALL PLUGIN ‘plugin name’ SONAME ‘filename.so’;

For example,

Note: While installation the (.so) file must be placed inside the plugin Directory, Otherwise the Installation will be failed.

Logging formats:

We have two formats for logging like, file and syslog. By using the Option “server_audit_output_type” we can change the formats. For example,

Syslog format,

Output-Structure:

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 22:31:49,mydbops17,root,localhost,3,80,QUERY,information_schema,'SHOW GLOBAL VARIABLES LIKE "server_audit%"',0
20200503 22:31:58,mydbops17,root,localhost,3,81,QUERY,information_schema,'SHOW GLOBAL VARIABLES LIKE "server_audit%"',0
20200503 22:45:53,mydbops17,root,localhost,3,82,QUERY,information_schema,'show databases',0
20200503 22:46:40,mydbops17,root,localhost,3,83,QUERY,information_schema,'SELECT DATABASE()',0
20200503 22:46:40,mydbops17,root,localhost,3,85,QUERY,test,'show databases',0
20200503 22:46:40,mydbops17,root,localhost,3,86,QUERY,test,'show tables',0
20200503 22:46:43,mydbops17,root,localhost,3,89,QUERY,test,'show tables',0
20200503 22:47:17,mydbops17,root,localhost,3,90,QUERY,test,'select * from t1',0

MariaDB Audit-Plugin logging the Output like this above screenshot. It has Details like timestamp, server host, username, host, connectionid, queryid, operation, database, object, retcode. They are recorded in CSV format .Either JSON or XML is not supported.

MariaDB Audit-Plugin variables:

Below are the variables list for MariaDB Audit Plugin.

Variable_name :

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_loc_info

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

We can also get the variables using “show global variables” Query. From this Blog we have see the detail explanation about variables “server_audit_events”, “server_audit_incl_users” and “server_audit_excl_users”

Server_audit_logging:

This is the variable used to allow the logging. By default the logging is in OFF condition we need to enable this. After enabling the variable “server_audit_logging” the plugin starts the logging.

Variable_name:server_audit_logging

Value: ON

Server_audit_events:

The variable “Server_audit_events” is used to define logging from the Server. We have 6 type of logs in MariaDB-Audit-Plugin.

Connect
Query
Query_DML
Query_DDL
Query_DCL

(i) Connect:

If we enable “Connect” in ‘server_audit_events‘ it logs all connections, disconnects and the failed connections with error code. We can enable it on both config file and Global.

 
mysql> set global Server_audit_events='connect';
Query OK, 0 rows affected (0.00 sec)

After enable server_audit_events=connect, the output be like this,

Command executed :

 
[root@mydbops17 vagrant]# mysql -uroot -p'*********'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

[root@mydbops17 vagrant]# mysql -uroot -p'Mydbops@1'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@mydbops17 vagrant]# fg
mysql> ^DBye

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 22:56:27,mydbops17,root,localhost,4,0,CONNECT,,,0
20200503 22:59:26,mydbops17,root,localhost,4,0,DISCONNECT,,,0
20200503 23:00:37,mydbops17,root,localhost,5,0,FAILED_CONNECT,,,1045

From this Output,

DISCONNECT denotes the server exit.

CONNECT denotes the server login.

FAILED denotes the connection error and 1045 is error code.

(ii)Query:

It logs all the details about the Query executing in the server like, executed Query and issued Query with error code. We can enable it in config files and globally.

 
mysql>  set global Server_audit_events='Query';
Query OK, 0 rows affected (0.00 sec)

The output logs like,

Command executed :

 
mysql> use test;
Database changed

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | praveen   |
|    2 | praveen10 |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from t3;
ERROR 1146 (42S02): Table 'test.t3' doesn't exist

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 23:34:38,mydbops17,root,localhost,3,98,QUERY,test,'SELECT DATABASE()',0
20200503 23:34:46,mydbops17,root,localhost,3,100,QUERY,test,'select * from t1',0
20200503 23:34:53,mydbops17,root,localhost,3,102,QUERY,test,'select * from t3',1146

From this output we can see both fine query and issued query.

DB name : test

Table name : t1

 
Select * from test.t1   — Query is fine

Select * from test.t3   — Issued query with error code = 1146

(iii) Query_DML:

The variable “Query_DML” logs only the DML operations like SELECT, INSERT, UPDATE, DELETE, MERGE. We can enable it in both config file and global.

 
mysql>  set global Server_audit_events='Query_DML';
Query OK, 0 rows affected (0.00 sec)

The output logs like,

Command executed :

 
mysql> insert into t2 values (3,'praveen20');
Query OK, 1 row affected (0.00 sec)

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 23:38:44,mydbops17,root,localhost,3,104,QUERY,test,'insert into t2 values (3,\'praveen20\')',0

From this output , we are able to see the SELECT, UPDATE and INSERT Operations.

(iv) Query_DDL:

The variable “Query_DDL” logs only the DDL operations like CREATE, ALTER, DROP, TRUNCATE. It benefits we can enable it in both config files and global.

 
mysql>  set global Server_audit_events='Query_DDL';
Query OK, 0 rows affected (0.00 sec)

The output logs like,

Command executed :

 
mysql> create table t3(id int,name varchar(25));
Query OK, 0 rows affected (0.00 sec)

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 23:46:52,mydbops17,root,localhost,3,106,QUERY,test,'create table t3(id int,name varchar(25))',0

The Output log files contain the DDL Statements.

(v) Query_DCL:

It logs only the DCL operations like GRANTS and REVOKE. We can enable it in both config file and Global. It benefits more in security for the list of users added and grants provided.

The output logs like,

Command executed :

 
mysql> create user 'praveen'@'localhost' identified by '**************';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'praveen'@'localhost' ;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,'praveen20');
Query OK, 1 row affected (0.00 sec)

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 23:52:46,mydbops17,root,localhost,3,108,QUERY,test,'CREATE USER \'praveen\'@\'localhost\' IDENTIFIED WITH \'mysql_native_password\' AS \'*D9E26FC2BF5871718B904BA72DBC9BACC46F2672\'',0
20200503 23:53:19,mydbops17,root,localhost,3,109,QUERY,test,'GRANT ALL PRIVILEGES ON *.* TO \'praveen\'@\'localhost\'',0

Note : I did an insert to show that it won’t record this insert in the audit log , because “server_audit_events” is set to “Query_DCL”. It is applicable to all the other “server_audit_event” also.

The logs contain only the Queries like GRANTS and REVOKE, and the error query had the error code too. It is better to use a log rotate with the output log file.

Note: If we need more options like Query_DDLl, Query_DML, Query_DCL just make the options with a comma.

For example,

 
mysql> set global server_audit_events='CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML';
Query OK, 0 rows affected (0.00 sec)

Server_audit_incl_users:

If we need to audit a specific user operation this option will be helpful for that kind of job. We can enable it in both config and global. It logs all the operations for that particular user based on the “server_audit_events” variables.

 
mysql> set global Server_audit_incl_users='praveen';
Query OK, 0 rows affected (0.00 sec)

We can also add a few users using commas.

Command executed :

 
[root@mydbops17 vagrant]# mysql -uroot -p'********'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | praveen   |
|    2 | praveen10 |
|    1 | praveen20 |
+------+-----------+
3 rows in set (0.00 sec)
mysql>
[3]+  Stopped                 mysql -uroot -p'********'

[root@mydbops17 vagrant]# mysql -upraveen -p'***********'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | praveen   |
|    2 | praveen10 |
|    1 | praveen20 |
+------+-----------+
3 rows in set (0.00 sec)

Logged data:

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200503 23:56:49,mydbops17,root,localhost,7,0,CONNECT,,,0
20200504 00:01:29,mydbops17,praveen,localhost,8,0,CONNECT,,,0
20200504 00:01:29,mydbops17,praveen,localhost,8,127,QUERY,,'select @@version_comment limit 1',0
20200504 00:01:33,mydbops17,praveen,localhost,8,128,QUERY,,'SELECT DATABASE()',0
20200504 00:01:33,mydbops17,praveen,localhost,8,130,QUERY,test,'show databases',0
20200504 00:01:33,mydbops17,praveen,localhost,8,131,QUERY,test,'show tables',0
20200504 00:01:40,mydbops17,praveen,localhost,8,135,QUERY,test,'select * from t1',0

Note : It doesn’t log the query executed by the root user. It just logged the connection entry of the root user and the query entry of “praveen” user.

Server_audit_excl_users:

It is the variable that performs the single or list of users mentioned in the variable, whose activities are not logged. We can enable it in both config file and global.

 
mysql> set global Server_audit_incl_users='';
Query OK, 0 rows affected (0.00 sec)

mysql> set global Server_audit_excl_users='praveen';
Query OK, 0 rows affected (0.00 sec)

I am going to add the praveen user as an excl user. So I am removing that user from incl_user.

Command executed :

 
[root@mydbops17 vagrant]# mysql -upraveen -p'***************'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> select * from t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | praveen   |
|    2 | praveen10 |
|    1 | praveen20 |
+------+-----------+
3 rows in set (0.00 sec)

Logged data :

 
[root@mydbops17 mysql]# tail -f server_audit.log
20200504 00:12:55,mydbops17,praveen,localhost,9,0,CONNECT,,,0

Note : Only login detail is logged for praveen user now.

MariaDB Audit-Plugin is used to log the server operations based on our needs. MariaDB has better filtering than Percona plugin. MariaDB by default stores as a flat file (CSV) . These flat files can be made to ElasticSearch ( ELK with Percona Plugin ).

These Audit logs can be used for HIPAA,PCI, etc compliances.

MySQL DBA

MySQL

MariaDB

No items found.

About the Author

Mydbops

Integrating MariaDB Audit plugin in MySQL 5.7

About the Author

Topic covered

Continue Reading

MongoDB Shutdown Methods: db.shutdownServer() vs systemctl stop

PostgreSQL 17: Convert Streaming Replica to Logical Replication with pg_createsubscriber

Evolution of Histograms in MySQL 8.4: From Manual to Automated Performance Optimization

Mastering Null Handling in MongoDB

Subscribe Now!