Re: Disappearing Triggers!

Norman Dunbar wrote:

> If you are worried about people doing things to your database and you
> not knowing,
> and you are running at 8i then try the following logged in as SYS :
>
> create tablespace logging
> datafile 'path to file' size 201m
> extent management local
> uniform size 64k;
>
> create user logging identified by new_password
> default tablespace logging
> temporary tablespace temp
> quota 0 on system;
>
> create table logging.ddl_log
> ( user_name varchar2(30),
> ddl_date date,
> ddl_type varchar2(30),
> object_type varchar2(18),
> owner varchar2(30),
> object_name varchar2(128)
> ) tablespace logging;
>
> create or replace trigger
> DDLTrigger
> AFTER DDL ON DATABASE /* See below for changes to this line */
> BEGIN
> insert into logging.ddl_log
> ( user_name, ddl_date, ddl_type, object_type, owner, object_name)
> VALUES ( ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type,
> ora_dict_obj_owner, ora_dict_obj_name);
> END;
> /
>
> This will log all ddl on the database to the table in the logging user.
> If yo change the 'DLL' part to 'DROP' or 'LATER' then you only log those
> DDL commands.
>
> You can find out who'se been doing DDL by :
>
> select * from logging.ddl_log;
>
> HTH
>
> Regards,
> Norman.
>
> PS. I can't accept any/all the credit - see
> http://www.dbazine.com/burleson7.html for details.

I have something similar. In addition I track the IP Address, Terminal, Program, and O/S User ID.

I like that "quota 0 on SYSTEM" ... Thanks.

Daniel Morgan Received on Mon Sep 09 2002 - 11:46:52 CDT