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.
Saturday, June 6, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment