Re: Auditing ALL activities on a table

fitzjarrell_at_cox.net wrote:
>
> nirav wrote:
> > Hi
> >
> > I need to have a detailed audit on ALL activities of a table..ie I need
> > to know all the SELECT, INSERT,UPDATE, DELETE against the table(DDLs
> > against the table need not be audited..)
> >
> > how can this be done? it should be such that the exact sql statement
> > fired against the table should be captured..can this be done via fga or
> > some other means?
> >
> > appreciate ur help..
>
> Certainly you appreciate the help since you're possibly too lazy to
> look this up yourself.
>
> Object auditing won't provide the SQL statements issued, only that
> SELECT, INSERT, UPDATE or DELETE actions were executed against the
> object in question. It will also provide who did such operations, and
> when, and the success or failure of the action.
>
> Fine-grained auditing may not return the exact SQL text for SELECT
> statements as it is truncated to 2000 characters; it won't return text
> for INSERT, UPDATE or DELETE statements as fine-grained auditing is
> designed and implemented to monitor SELECTs. And, you'll need to
> configure all cases you wish to monitor against a specific table using
> the DBMS_FGA package. Multiply this by the number of tables you want
> audited and I imagine it becomes a daunting task.
>
> You _could_ write your own trigger to capture user-level SQL
> statements, using V$SESSION, V$PROCESS and V$SQLTEXT for insert, update
> and delete statements. although this table you'd be populating could
> grow to be quite large in a short amount of time, and it still wouldn't
> capture SELECT statements. Possibly you could also use fine-grained
> auditing as a starting point (again, configuring it for ALL select
> statements for ALL tables [which would be a feat unto itself. as you'd
> need to monitor every column in every table]) by searching V$SQLTEXT
> for the value in the SQL_TEXT column in DBA_FGA_AUDIT_TRAIL to return
> the unabridged statement text, however this would still not include any
> unqualified SELECT statements (such as SELECT * from EMP, for example)
> as fine-grained auditing relies upon a WHERE clause. Your audit trail,
> therefore, would be incomplete.
>
> What problem are you *really* trying to solve with this question? If
> we knew that other, possibly more usable, solutions might be presented.
> As it stands now your request of capturing all SQL statements against
> every table in your schema/database can't be fulfilled. Present the
> real problem and we'll try to help you further.
>
> David Fitzjarrell

10g extends fga to ins/upd/del

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------