Re: How to do auditing for fDML

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 20 Dec 2009 07:21:44 +0000 (UTC)
Message-ID: <hgkja8$8g0$1_at_news.eternal-september.org>

On Sat, 19 Dec 2009 21:33:39 -0800, zigzagdna wrote:

> I want to monitor if system accounts such as system, sys or other DBA
> accounts
> did any DML in my application schema. What are the minimal audit
> statements
> needed to accomplish this. I did read Oracle's documentation on AUDIT
> statement, but it
> doest not show how to audit entire schema, it shows how to audit
> indivual objects in a schema which requires lots of staements and when
> new bjects are added, one has to add auditing on them.
>
> I know using Oracle Database Vault one can set realm to restrict this
> but I do
> not have Data Vault, just looking to audit this using Oracle’s database
> auditing.
> My organization also has Oracle Audit Vault, but Database Auditing
> Events have to be set in database
> first for collection agent to pass this information to Oracle Audit
> Vault server.
> Thanks a lot.

You can't audit the entire schema with one statement. Something like

	select 'audit all on '||owner||'.'||table_name||';' 
	from dba_tables
	where owner='OWNER';

would create a SQL script that would do the trick. I's called "SQL generating SQL" and is the oldest trick in the book.

-- 
http://mgogala.byethost5.com

Received on Sun Dec 20 2009 - 01:21:44 CST