RE: Audit object
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 May 2004 13:20:16 -0500
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@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Smith, Ron L.
Sent: Tuesday, May 18, 2004 11:21 AM
To: oracle-l@xxxxxxxxxxxxx
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@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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
