RE: Audit object

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Tue, 18 May 2004 11:20:44 -0500
Message-ID: <270A0BDDFDE54E41B78F0F06D82A66B85089B7@okcexg3.kmg.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Cachito Reyes Pacheco
Sent: Tuesday, May 18, 2004 11:08 AM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 18 2004 - 11:17:57 CDT