-- Following scripts create audit tables and then also create audit triggers for full database or for only one table as per need.
If user provides only schema name parameter then following process will create audit triggers for all tables in the schema.
And if user provides schema and table_name parameters then only for that table audit triggers is created.
Three procedures are provided.
1. First procedure can create tables to store audit data.
2. Second procedure generates scripts to add DML audit triggers for the above tables.
3. Third procedure calls above two procedures to create audit tables and generate triggers scripts.
4. Follow the next steps to execute script generated with above procedure to create triggers.
Third procedure can be called in two ways
1. With schema name and table name parameter (For only one table)
2. Only schema name (For all tables in schema)
Issues that can appear
1. If your table contains table with large column size i.e size not supported by Mysql below script will not be able to create audit table but it still it will provide script that you can modify to create audit table manually.
2. If table contains column with spaces in column name then again you need to modify script manually generated by following procedures.
Create a user to be audited or use existing user.
create new user by following command
GRANT USAGE ON *.* TO `myuser`@`%` IDENTIFIED BY 'mypassword' REQUIRE NONE;
1. Following Procedure create audit tables to store audit data.
use mydb;
DELIMITER $$
DROP PROCEDURE IF EXISTS create_aud_tabs$$
CREATE PROCEDURE create_aud_tabs (IN p_db_name varchar(64),IN tbl_name varchar(64))
BEGIN
DECLARE v_column_list varchar(10000) default '';
DECLARE v_query varchar(10000) DEFAULT '';
declare has_error int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
SET SESSION group_concat_max_len=15000;
select GROUP_CONCAT(distinct 'old_', column_name,' ',column_type,',new_',COLUMN_NAME,' ',column_type)
into v_column_list
from information_schema.COLUMNS
where TABLE_SCHEMA=table_schema
and TABLE_NAME=tbl_name
and table_name not like 'aud\_%'
order by ordinal_position ;
set v_query=concat ('create table ',p_db_name,'.aud_',tbl_name,'( audit_id int NOT NULL AUTO_INCREMENT,',v_column_list,',audit_type varchar(30),audit_user varchar(100),audit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,primary key (audit_id))');
select v_query;
SET @sql = v_query;
PREPARE stmt FROM @sql;
EXECUTE stmt ;
if has_error=1 then
select v_query;
set has_error=0;
end if;
END$$
DELIMITER ;
-- 2. Following Procedure Create Scripts to add audit for DML operations.
DELIMITER $$
DROP PROCEDURE IF EXISTS add_aud_triggers $$
CREATE PROCEDURE add_aud_triggers (IN P_db_name varchar(64),IN tbl_name varchar(64),in p_user_to_audit varchar(64) ,in p_trigger_script_path varchar(1000))
BEGIN
DECLARE v_column_list varchar(20000) DEFAULT '';
DECLARE v_del_val_list varchar(20000);
DECLARE v_ins_val_list varchar(20000);
DECLARE v_upd_val_list varchar(20000) DEFAULT '';
DECLARE u_trigger varchar(20000)DEFAULT '';
Declare d_trigger varchar (20000)DEFAULT '';
Declare i_trigger varchar (20000)DEFAULT '';
Declare has_error int;
Declare CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
SET SESSION group_concat_max_len=20000;
set v_column_list='';
set v_upd_val_list='';
set v_del_val_list= '';
set v_ins_val_list= '';
SELECT GROUP_CONCAT( distinct 'old_', column_name,',new_',COLUMN_NAME)
into v_column_list
FROM information_schema.COLUMns
where TABLE_SCHEMA=table_schema
and TABLE_NAME=tbl_name
order by ordinal_position ;
-- select v_column_list;
SELECT GROUP_CONCAT(distinct 'old.', column_name,',new.',COLUMN_NAME)
into v_upd_val_list
FROM information_schema.COLUMns
where TABLE_SCHEMA=table_schema
and TABLE_NAME=tbl_name
order by ordinal_position ;
SELECT GROUP_CONCAT(distinct 'old.', column_name,',null')
into v_del_val_list
FROM information_schema.COLUMns
where TABLE_SCHEMA=table_schema
and TABLE_NAME=tbl_name
order by ordinal_position ;
-- -------------------
SELECT GROUP_CONCAT(distinct 'null,','new.', column_name)
into v_ins_val_list
FROM information_schema.COLUMns
where TABLE_SCHEMA=table_schema
and TABLE_NAME=tbl_name
order by ordinal_position ;
-- ---------------------------------------
SET u_trigger = '';
SET u_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_upd_aud$$\n');
SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',tbl_name,'_upd_aud AFTER update ON ',tbl_name,' FOR EACH ROW \n');
SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
SET u_trigger = CONCAT(u_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
set u_trigger = concat (u_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_upd_val_list,',''update'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
SET u_trigger = CONCAT(u_trigger,'END IF;\n');
SET u_trigger = CONCAT(u_trigger,'END$$ \nDELIMITER ; \n');
select u_trigger from dual into @triggertxt ;
SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/upd_trigger_',tbl_name,'.sql', '"');
PREPARE stmt5 FROM @savestr;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
-- -------------------------------------
SET d_trigger = '';
SET d_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_del_aud$$\n');
SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',tbl_name,'_del_aud AFTER delete ON ',tbl_name,' FOR EACH ROW \n');
SET d_trigger = CONCAT(d_trigger,'BEGIN \n');
SET d_trigger = CONCAT(d_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
set d_trigger=concat (d_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_del_val_list,',''delete'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
SET d_trigger = CONCAT(d_trigger,'END IF;\n');
SET d_trigger = CONCAT(d_trigger,'END$$ \nDELIMITER ; \n');
select d_trigger from dual into @triggertxt ;
SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/del_trigger_',tbl_name,'.sql', '"');
-- select @savestr;
PREPARE stmt5 FROM @savestr;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
-- -------------------------------------
SET i_trigger = '';
SET i_trigger = CONCAT('DELIMITER $$ \n DROP TRIGGER IF EXISTS tra_',tbl_name,'_ins_aud$$\n');
SET i_trigger = CONCAT(i_trigger,'CREATE TRIGGER tra_',tbl_name,'_ins_aud AFTER insert ON ',tbl_name,' FOR EACH ROW \n');
SET i_trigger = CONCAT(i_trigger,'BEGIN \n');
SET i_trigger = CONCAT(i_trigger,'if SUBSTRING_INDEX(SESSION_USER(),''@'',1)= ''',p_user_to_audit,''' then \n');
set i_trigger=concat (i_trigger,concat ('insert into ',P_db_name,'.aud_',tbl_name,'(',v_column_list,',audit_type ,audit_user ,audit_date) \nvalues (',v_ins_val_list,',''insert'',SESSION_USER(),CURRENT_TIMESTAMP); \n' ));
SET i_trigger = CONCAT(i_trigger,'END IF;\n');
SET i_trigger = CONCAT(i_trigger,'END$$ \nDELIMITER ; \n');
select i_trigger from dual into @triggertxt ;
SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', p_trigger_script_path ,'/ins_trigger_',tbl_name,'.sql', '"');
-- select @savestr;
PREPARE stmt5 FROM @savestr;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
-- --------------------------------------------
set v_column_list ='';
END$$
DELIMITER ;
3. Following procedure call above created procedures to create audit tables and then generate scrip to create audit triggers for update and delete operations.
DELIMITER $$
DROP PROCEDURE IF EXISTS create_audit$$
CREATE PROCEDURE create_audit (in p_db_name varchar(64),in p_table_name varchar(64),in p_user_to_audit varchar(64),in p_trigger_script_path varchar(1000))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(64);
DEClARE tab_cursor CURSOR FOR
select table_name
from information_schema.tables
where TABLE_SCHEMA=p_db_name
and (TABLE_NAME=p_table_name or p_table_name is null)
and table_name not like 'aud\_%'
order by table_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
OPEN tab_cursor;
set v_table_name='';
get_table: LOOP
-- select v_finished;
FETCH tab_cursor INTO v_table_name;
-- select v_finished;
IF v_finished = 1 THEN
LEAVE get_table;
END IF;
select v_table_name;
call create_aud_tabs(p_db_name,v_table_name);
call add_aud_triggers(p_db_name,v_table_name,p_user_to_audit,p_trigger_script_path);
END LOOP get_table;
CLOSE tab_cursor;
END$$
DELIMITER ;
To create audit tables and trigger script for complete schema.
call create_audit('mydb,null,'myuser','/usr/local/mysql/data/triggers');
To create audit tables and trigger script for only on table in schema.
call create_audit('mydb,'table_name','myuser','/usr/local/mysql/data/triggers');
To create Audit table for one table use following Procedure.
call create_aud_tabs('mydb','mytable');
To get script for audit trigger of one table use following script.
call add_aud_triggers('mydb','mytable','myuser','/usr/local/mysql/data/triggers');
Note: path "/usr/local/mysql/data/triggers"
must be created manually before executing the procedures
-----------------------------------------------------------------------------------------------------
4. Follow the next steps to execute script generated with above procedure to create triggers.
now the audit script is created and we can proceed with following commands.
-- Execute following query and and paste result in file called triggers.sql and place this file with other scripts generated at "/usr/local/mysql/data/mydb/triggers"
as given in the above procedures.
select concat ('source ' ,filename )
from (
select concat('del_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%'
union all
select concat('upd_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%'
union all
select concat('ins_trigger_',table_name,'.sql') filename,table_name
from information_schema.tables
where TABLE_SCHEMA='mydb'
and table_name not like 'aud\_%') files
order by table_name ;
Now go to the location "/usr/local/mysql/data/mydb/triggers"
and execute following command to create triggers.
-- Command to execute create trigger scripts
/usr/local/mysql/bin/mysql -u root -h hostname -p -f mydb < triggers.sql
Helping Script with above procedures
-- Query to generate drop script for audit tables
select concat ('drop table ',table_schema,'.',table_name,';')
from information_schema.tables
where table_schema='mydb'
and table_name like 'aud\_%';
-- Query to count tables
select count(0)
from information_schema.tables t
where t.table_schema='mydb'
and t.table_type='BASE TABLE'
and t.table_name not like 'aud\_%';
-- Query to count Audit tables
select count(0)
from information_schema.tables t
where t.table_schema='mydb'
and t.table_type='BASE TABLE'
and t.table_name like 'aud\_%';
-- Query to find tables for which script is Unable to create audit table
select CONCAT ('SELECT * FROM ',t.table_name,';')
from information_schema.tables t left outer join information_schema.tables a
on(t.TABLE_NAME=substr(a.table_name,5))
and a.table_schema='mydb'
and a.table_name like 'aud\_%'
where a.table_name is null
and t.table_schema='mydb'
and t.table_name not like 'aud\_%';
-- Query to find tables for which script is Unable to create audit triggers
select CONCAT ('SELECT * FROM ',a.table_name,';')
from information_schema.tables a left outer join information_schema.TRIGGERS t
on (t.EVENT_OBJECT_TABLE=a.TABLE_NAME)
where t.EVENT_OBJECT_TABLE is null
and a.table_schema='mydb'
and a.table_name not like 'aud\_%';
-----------------------
-- Query to find table which have 3 or less triggers (ins,upd,del)
select EVENT_OBJECT_TABLE,tb.TABLE_NAME
from (
select t.EVENT_OBJECT_TABLE
from information_schema.TRIGGERS t
group by t.EVENT_OBJECT_TABLE
having count(0)>=3 ) tr right outer join information_schema.tables tb
on (tr.EVENT_OBJECT_TABLE=tb.TABLE_NAME)
where tb.TABLE_SCHEMA='mydb'
-- and tb.table_name='mytable'
and tr.EVENT_OBJECT_TABLE is null
and tb.TABLE_name not like 'aud\_%';
Following query will give you the script to Grant priviliges to new user on the database.
select concat ('grant select ,insert ,update ,delete on mydb.',table_name ,' to ','`myuser`@`%`;')
from information_schema.tables
where TABLE_SCHEMA='mydb'
-- and (TABLE_NAME=p_table_name or p_table_name is null)
and table_name not like 'aud\_%';
