Re: DDL auditing - *Extremely* detailed
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 May 2004 11:44:38 -0400
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
