RE: Audit object

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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: