Re: DDL auditing - *Extremely* detailed

  • From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 4 May 2004 15:37:58 -0400

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

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

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



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