Audit Schema

[stoolpigeon]

I would like to know if it is possible to turn on auditing on all the objects in a schema at once.

I have tried

audit alter table on SCOTT by access whenever succesful;

and I get an error, 'SQL command not properly ended'

All the examples I find for this show the auditing being turned on for a single table at a time. Is there another format for audit that will do what I want, or do I need to set up triggers instead.

I have set up triggers on the ddl I want but the auditing function has a lot more capability than my triggers.

Thank You for any assistance.

 

[SantaMufasa]

StoolPigeon,

I, personally, prefer audit triggers that can tell me WHO, made WHAT changes (old and new values) to WHICH tables/columns, and WHEN those changes occurred. I can also turn those audit triggers on and off virtually instantly and I can be very selective/granular about any of the components that I audit. My experience has shown me that I cannot do all of that with Oracle's AUDIT commands/features.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[stoolpigeon]

It looks that is the route I will be going. I set up a log table, and a trigger. The trigger inserts the following into the table.

insert into
   sys.ddl_log
 (select
   ora_login_user,
   sysdate,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_owner,
   ora_dict_obj_name from dual
 );

My primary concern is with ALTER and DROP statements. Are there other pieces of information that you would collect?

Thanks

 

[SantaMufasa]

Actually, my main concern is Who would be ALTERing/DROPping tables besides the owner of the the table? If more than one person is logging into the schema that owns the table, then that's the first thing I would fix.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[stoolpigeon]

Well, there are a lot of developers, and we want to know who does what. (I'm a junior dba - new to Oracle, in a decent sized shop using Oracle and Peoplesoft.)

And if you don't mind while I've got you here. In my local install of 10g on my laptop, I created the following trigger while I was logged in as sys:

CREATE OR REPLACE
TRIGGER ALTER_TABLE_LOG BEFORE ALTER ON "SCOTT".SCHEMA 
BEGIN
 insert into
   sys.ddl_log
 (select
   ora_login_user,
   sysdate,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_owner,
   ora_dict_obj_name from dual
 );

END;

When I alter a table logged in as SCOTT, a row is added to the table. When I alter the same table logged in as SYS or HR, then no row is added to the log table.

It seems I've added a trigger that fires when SCOTT alters tables. I want a trigger that fires when anyone alters a table in the SCOTT schema.

 

[maswien]

The same code works for me, I used another user than the schema owner to alter the table and then there is a record logged to the table.

 

[Beilstwh]

Creating a trigger on DATABASE implies that the triggering event is outside the scope of a user and it applies to all users (for example, a trigger created on LOGON event by the DBA). Creating a trigger on SCHEMA implies that the trigger is created in the current user's schema and is fired only for that user. Also you should NEVER create your own objects in the SYS schema. This schema is reserved for database objects only. If you want to protect your audit table, make it in a protected schema and issue the following commands (in the schema)

grant insert on ddl_log to public;


This way ALL users can write into it, but they can't read it or alter it.

Bill
Oracle DBA/Developer
New York State, USA