Re: Auditing DML of selected users

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Tue, 4 May 2004 13:50:42 +0100
Message-ID: <ZOoK56AiG5lARxpr@peterfinnigan.demon.co.uk>

Hi George,

Thanks for your reply. Audit is split into three groups, auditing SQL statements, auditing privileges and auditing objects. You specify the user for statements and privileges but the object for objects. You are in effect auditing the object not the user when auditing access to a table. You cannot set audit for a particular user on a table. See:

SQL> audit insert on scott.emp by dbsnmp; audit insert on scott.emp by dbsnmp

                             *

ERROR at line 1:
ORA-01708: ACCESS or SESSION expected

You have to audit access to the object and then find out who accessed it from the audit trail.

SQL> audit insert on scott.emp;

Audit succeeded.

SQL> You also cannot get the DML statement from the normal audit trail.

You would need to use triggers to get the DML. Also i wouldn't audit all tables as you could generate huge quantities of audit trail!

Your issue sounds like you need Fine Grained Audit. Whihc for 9i is only any use for select but in 10g is extended to dml.

hth

kind regards

Pete

In article <109bv31skuegc64_at_corp.supernews.com>, George Mansoor <ghm_at_4link.net> writes
>Pete - Thanks for the information. I just don't get how to audit
>insert/update/delete on all tables for just a few users and get the DML
>statement for that insert/update/delete. Is this possible w/ auditing, or do
>I need to install triggers on every table?
>
>Thanks
>
>George
>
>"Pete Finnigan" <plsql_at_petefinnigan.com> wrote in message
>news:60aiXyAznTjARxfw_at_peterfinnigan.demon.co.uk...
>> Hi
>>
>> You can use audit, have a look at a paper I wrote last year called "An
>> introduction to simple Oracle auditing" it is available from my site at
>> http://www.petefinnigan.com/orasec.htm
>>
>> kind regards
>>
>> Pete
>> --
>> Pete Finnigan
>> email:pete_at_petefinnigan.com
>> Web site: http://www.petefinnigan.com - Oracle security audit specialists
>> Book:Oracle security step-by-step Guide - see http://store.sans.org for
>details.
>
>

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Tue May 04 2004 - 07:50:42 CDT