RE: Audit object
- From: "Smith, Ron L." <rlsmith@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 May 2004 11:20:44 -0500
Thanks for the sample.
I can't believe Oracle doesn't have a simple command to audit all DDL
changes.
That would be too easy.
Thanks!
Ron
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Cachito Reyes
Pacheco
Sent: Tuesday, May 18, 2004 11:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Audit object
HI, here is an example to save all data from ddls executed
The same to create and alter instead of drop
CREATE OR REPLACE TRIGGER sys.tgr_drop
AFTER
DROP <--
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 =3D userenv('sessionid')
and s.prev_sql_addr =3D t.address(+)
and s.prev_hash_value =3D t.hash_value(+)
and username is not null;
n:=3D ora_sql_txt(csql_text);
FOR I IN 1..N LOOP
cSQL:=3DcSQL||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(
'D-'||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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
