Saturday, June 6, 2009

Do Auditing without using Audit command

This below is an easy way of implementing audit for DDL's without using Audit command

This compromises of 2 steps
1)Create a tablespace called audit_tbs
2)Create a table named MONITOR inside AUDIT_TBS
3)Create a trigger.

CREATE TABLE SYS.MONITOR
(OBJ_OWNER VARCHAR2(20),
OBJ_NAME VARCHAR2(30),
OBJ_TYPE VARCHAR2(30),
OBJ_EVENT VARCHAR2(30),
OBJ_DATE TIMESTAMP (6))
TABLESPACE AUDIT_TBS ;

create or replace trigger monitor_trig
before create or drop or alter or truncate on database
declare
cursor c1 is
select dbms_standard.DICTIONARY_OBJ_OWNER owner,
dbms_standard.SYSEVENT event ,
dbms_standard.DICTIONARY_OBJ_NAME name,
dbms_standard.DICTIONARY_OBJ_TYPE type
from dual;
BEGIN
for rec in c1
loop
Insert into monitor values (rec.owner,rec.name,rec.type,rec.event,sysdate);
end loop;
end;
/

You can cutomize the trigger code as per your requirement.

No comments: