CREATE TABLE AUDIT_DDL ( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), TERMINAL varchar2(255), owner varchar2(30), type varchar2(30), name varchar2(30), sysevent varchar2(30));
connect uid/pwd
CREATE OR REPLACE TRIGGER audit_ddl_trg after ddl on schema BEGIN IF(ora_sysevent='TRUNCATE') THEN null; -- If we do not care about truncate ELSE INSERT INTO audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent) values( sysdate, sys_context('USERENV','OS_USER') , sys_context('USERENV','CURRENT_USER') , sys_context('USERENV','HOST') , sys_context('USERENV','TERMINAL') , ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, ora_sysevent ); END IF; END; /
Source: Don Burleson http://www.dba-oracle.com/t_ddl_triggers.htm
No comments:
Post a Comment