.::: Deploy MariaDB Audit filter by only Create, Insert, Update, Delete :::.

 

 correlation https://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

A. Prepare Filter
1. Create
grep -iE "create " server_audit.log | grep -ivE "select|SHOW|INSERT"

2. Insert
grep -iE "insert into|insert ignore" server_audit.log | grep -ivE "select|CREATE|SHOW"

3. Update
grep -iE "UPDATE .*SET" server_audit.log | grep -ivE "select|CREATE"

4. Delete
grep -i "delete" server_audit.log | grep -ivE "select|CREATE|SHOW|INSERT" 

B. Create output specify

[root@teguhth mysql]# cat script_output_filter.sh
grep -iE "create " server_audit.log | grep -ivE "select|SHOW|INSERT" > server_audit_create.log
grep -iE "insert into|insert ignore" server_audit.log | grep -ivE "select|CREATE|SHOW" > server_audit_insert.log
grep -iE "UPDATE .*SET" server_audit.log | grep -ivE "select|CREATE" > server_audit_update.log
grep -i "delete" server_audit.log | grep -ivE "select|CREATE|SHOW|INSERT" > server_audit_delete.log
[root@teguhth mysql]#
 

C. Create table MariaDB Audit custom

use teguhthtools;

CREATE TABLE `tbl_mariadb_audit_create`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` longtext DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tbl_mariadb_audit_insert`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` longtext DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tbl_mariadb_audit_update`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` longtext DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tbl_mariadb_audit_delete`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` longtext DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `tbl_mariadb_audit`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` longtext DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

D. Create script to run

1. Create script
mysql -u root -pxxx -e "delete from teguhthtools.tbl_mariadb_audit_create; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit_create.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit_create FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'; select * from teguhthtools.tbl_mariadb_audit_create; ";

mysql -u root -pxxx -e "delete from teguhthtools.tbl_mariadb_audit_insert; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit_insert.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit_insert FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'; select * from teguhthtools.tbl_mariadb_audit_insert; ";

mysql -u root -pxxx -e "delete from teguhthtools.tbl_mariadb_audit_update; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit_update.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit_update FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'; select * from teguhthtools.tbl_mariadb_audit_update; ";

mysql -u root -pxxx -e "delete from teguhthtools.tbl_mariadb_audit_delete; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit_delete.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit_delete FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'; select * from teguhthtools.tbl_mariadb_audit_delete; ";

mysql -u root -pxxx -e "delete from teguhthtools.tbl_mariadb_audit; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'; Select * from teguhthtools.tbl_mariadb_audit; ";

2. check result
select * from teguhthtools.tbl_mariadb_audit_create;
select * from teguhthtools.tbl_mariadb_audit_insert;
select * from teguhthtools.tbl_mariadb_audit_update;
select * from teguhthtools.tbl_mariadb_audit_delete;
select * from teguhthtools.tbl_mariadb_audit;
 

or delete 

delete from teguhthtools.tbl_mariadb_audit_create;
delete from teguhthtools.tbl_mariadb_audit_insert;
delete from teguhthtools.tbl_mariadb_audit_update;
delete from teguhthtools.tbl_mariadb_audit_delete;
delete from teguhthtools.tbl_mariadb_audit;