| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Audit object
Ron
What is your objective? To be immediately notified whenever anyone performs DDL so you can go swat them? To reconstruct changes if something goes wrong?
Another idea is to set security so that only trusted people can perform DDL changes. If your organization has a strong tradition of allowing anyone to make changes, this may take some time to implement, but may have a better long-run payoff.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Smith, Ron L.
Sent: Tuesday, May 18, 2004 11:21 AM
To: oracle-l_at_freelists.org
Subject: 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_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);
,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
-- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 13:20:08 CDT
 |
 |