| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: DDL auditing - *Extremely* detailed
Sorry I need to do some adjustment to get previous email get work, but that
was the idea.
CREATE OR REPLACE TRIGGER sys.tgr_create
AFTER CREATE ON DATABASE DECLARE csql_text ora_name_list_t;
n NUMBER;
cSQL CLOB;
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(09);
var_program varchar2(48);
var_sqltext varchar2(1000);
BEGIN select s.username, s.osuser, s.machine, s.program
into var_user, var_osuser, var_machine, var_program
from sys.v_$session s
,sys.v_$sqlarea t
where s.audsid = userenv('sessionid')
and s.prev_sql_addr = t.address(+)
and s.prev_hash_value = t.hash_value(+)
and username is not null;
n:= ora_sql_txt(csql_text);
FOR I IN 1..N LOOP cSQL:=cSQL||csql_text(i);
END LOOP; INSERT INTO SYS.ddls
(dd2_user, dd2_time, dd2_objeto, dd2_tipo,DD2_SQL,
DD2_OSUSER , DD2_MACHINE , DD2_OPROGRAM )
VALUES( 'C-'||DBMS_STANDARD.LOGIN_USER,SYSDATE, DBMS_STANDARD.DICTIONARY_OBJ_NAME, DBMS_STANDARD.dictionary_obj_type,cSQL,
var_osuser, var_machine, var_program
);
END; /
Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition
 |
 |