Re: Auditing ALL activities on a table

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 Received on Mon May 29 2006 - 10:19:40 CDT