The script below (a trigger) audits and records information about DDL occurring in the current schema, recording the OS login name, the terminal, the session user, the command issued, the object type involved, and the object name.

create table audit_events (
event_date date,
event_user varchar2(30),
event_osuser varchar2(30),
event_machine varchar2(30),
event_terminal varchar2(30),
event_operation varchar2(30),
event_object_type varchar2(60),
event_object_name varchar2(60))
/

CREATE OR REPLACE TRIGGER object_change AFTER
CREATE OR DROP OR ALTER ON <>.SCHEMA
BEGIN
Declare
Cursor C1 is
select
sys_context(‘USERENV’,’OS_USER’) osuser,
sys_context(‘USERENV’,’TERMINAL’) terminal,
sys_context(‘USERENV’,’HOST’) machine,
sys_context(‘USERENV’,’SESSION_USER’) sess_user
from dual;
BEGIN
FOR C2 IN C1 LOOP
null;
Insert into grpaaa.audit_events
(
Event_date,
Event_user,
Event_osuser,
Event_machine,
Event_terminal,
Event_operation,
Event_object_type,
Event_object_name
)
values
(
sysdate,C2.sess_user,C2.osuser,c2.machine,c2.terminal,
ora_sysevent,ora_dict_obj_type,
ora_dict_obj_name);
END LOOP;
END;
END;
/

How to create trigger audits with Oracle DDL

Leave a Reply

Your email address will not be published. Required fields are marked *

eighty four − seventy five =