How to audit entire database?

[Saravanan K]

Hi,


Does anyone know how to audit all the tables in a database.

If the user deletes one row in a table, then it should be updated in audit table.

I need procedure for entire database auditing.

Note: it should be verified for all the tables in a database.

here is the auditing for single table

trigger && procedure>show create table tblaudit\G
*************************** 1. row ***************************
Table: tblaudit
Create Table: CREATE TABLE `tblaudit` (
`AuditId` int(8) NOT NULL AUTO_INCREMENT,
`UserName` varchar(50) NOT NULL,
`TableName` varchar(50) NOT NULL,
`FieldName` varchar(50) NOT NULL,
`OldValue` longtext,
`NewValue` longtext,
`DTAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`AuditId`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


trigger && procedure>show create table admin\G
*************************** 1. row ***************************
Table: admin
Create Table: CREATE TABLE `admin` (
`adminid` int(8) NOT NULL AUTO_INCREMENT,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) DEFAULT NULL,
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`adminid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)



create procedure auditpro(in pusername varchar(50),in ptablename varchar(50),in pfieldname varchar(50),in poldvalue text,in pnewvalue text)
begin
insert into tblaudit(`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`) values (pusername, ptablename, pfieldname, poldvalue, pnewvalue);
end?




create trigger auditinsrt after insert on admin
for each row
begin
if (new.first_name <> '') or (new.first_name is not null) then
call auditpro (user(), "admin", "first_name", "", new.first_name);
end if;
if (new.last_name <> '') or (new.last_name is not null) then
call auditpro (user(), "admin", "last_name", "", new.last_name);
end if;
if (new.create_date <> '') or (new.create_date is not null) then
call auditpro (user(), "admin", "create_date", "", new.create_date);
end if;
end?



create trigger auditupd after update on admin
for each row
begin
if (new.first_name <> old.first_name) or (new.first_name is not null and old.first_name is null) or (new.first_name is null and old.first_name is not null) then
call auditpro (user(), "admin", "first_name", old.first_name, new.first_name);
end if;
if (new.last_name <> old.last_name) or (new.last_name is not null and old.last_name is null) or (new.last_name is null and old.last_name is not null) then
call auditpro (user(), "admin", "last_name", old.last_name, new.last_name);
end if;
if (new.create_date <> old.create_date) or (new.create_date is not null and old.create_date is null) or (new.create_date is null and old.create_date is not null) then
call auditpro (user(), "admin", "create_date", old.create_date, new.create_date);
end if;
end?


create trigger auditdel after delete on admin
for each row
begin
call auditpro (user(), "admin", "first_name", old.first_name, "");
call auditpro (user(), "admin", "last_name", old.last_name, "");
call auditpro (user(), "admin", "create_date", old.create_date, "");
end

Hope this helps.

--Saravanan.K