Re: DDL auditing - *Extremely* detailed

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Tue, 4 May 2004 15:37:58 -0400
Message-ID: <012201c4320f$50a056c0$2501a8c0@dazasoftware.com>

Hi, what I found about audit is the same I knew, I supposed there could be a trick to g et more information.
I think (sorry if seems exaggerated), but the triggers I wrote are infinetely more effective, because when you need to detect problem (specially in development you need exact information)

I am putting the ddls if this could be useful to some one

CREATE TABLE sys.ddls

    (dd2_user                       VARCHAR2(100),
    dd2_time                       DATE,
    dd2_objeto                     VARCHAR2(100),
    dd2_tipo                       VARCHAR2(100),
    dd2_sql                        CLOB,
    dd2_osuser                     VARCHAR2(200),
    dd2_machine                    VARCHAR2(200),
    dd2_oprogram                   VARCHAR2(200))
  PCTFREE     10
  PCTUSED     40

  INITRANS 1
  MAXTRANS 255
  TABLESPACE tbl_blob
  STORAGE (
    INITIAL 16384
    MINEXTENTS 1
    MAXEXTENTS 2147483645
  )
  LOB (DD2_SQL) STORE AS
  (
   NOCACHE LOGGING
   CHUNK 8192
   PCTVERSION 10
  )
/

CREATE OR REPLACE TRIGGER sys.tgr_alter
 AFTER
  ALTER
 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(
  'A-'||DBMS_STANDARD.LOGIN_USER,SYSDATE,   DBMS_STANDARD.DICTIONARY_OBJ_NAME,
  DBMS_STANDARD.dictionary_obj_type,cSQL,   var_osuser, var_machine, var_program   );
END;
/

• End of DDL Script for Trigger ADM.TGR_ALTER
• Start of DDL Script for Trigger ADM.TGR_CREATE
• Generated 04/05/2004 15:36:01 from ADM_at_erpe.world

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;
/

• End of DDL Script for Trigger ADM.TGR_CREATE
• Start of DDL Script for Trigger ADM.TGR_DROP
• Generated 04/05/2004 15:36:04 from ADM_at_erpe.world

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 = 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(
 'D-'||DBMS_STANDARD.LOGIN_USER,SYSDATE,

 DBMS_STANDARD.DICTIONARY_OBJ_NAME,
 DBMS_STANDARD.dictionary_obj_type,cSQL,
 var_osuser, var_machine, var_program

 );
END;
/

• End of DDL Script for Trigger ADM.TGR_DROP
• Start of DDL Script for Table ADM.DDLS
• Generated 04/05/2004 15:36:28 from ADM_at_erpe.world
• End of DDL Script for Table ADM.DDLS

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

Received on Tue May 04 2004 - 14:39:32 CDT