MySQL Forums :: Stored Procedures :: How to audit entire database?


Advanced Search

How to audit entire database?
Posted by: Saravanan K ()
Date: August 05, 2010 06:22AM

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

Options: ReplyQuote


Subject Views Written By Posted
How to audit entire database? 3183 Saravanan K 08/05/2010 06:22AM
Re: How to audit entire database? 990 Peter Brawley 08/05/2010 02:51PM
Re: How to audit entire database? 1051 Saravanan K 08/05/2010 10:29PM
Re: How to audit entire database? 886 Peter Brawley 08/09/2010 07:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.