Home » RDBMS Server » Security » Auditing of sql text without using oracle audit
Auditing of sql text without using oracle audit [message #138190] Tue, 20 September 2005 12:10 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I am tasked with finding options to audit activity on a 9i database. In 10g, you can use db_extended as an audit_trail value to not only capture object name and timestamp etc of audited actions but also capture the sqltext and bind variables used in the insert, select, etc statements. But I can't use that in this case.

They want to be able to audit all activity performed by a few specific accounts which are used for special access to the database. They want to know all DML done by these accounts, including the actual sql text used.

Because they want this database wide, they can't use dbms_fga fine grained auditing because that is at the individual object level. Likewise for using standard object auditing.

They say sql_trace is not an option because they tried it and it captures so much information that it slows the user down with all of the disk io. I don't know if I buy this reason, but regardless, it is not an option for me in this case.

We might could use oracle auditing and whenever audit records are written have a trigger to go and try and scrape the sql text out of v$sql or some such view. I can't think this would be ideal though, and it would be a program to develop. Also they want info on each time a statement is executed (by access) rather than only once per identical statement, and the whole point of the shared pool is to only keep one copy of each statement.

Someone suggest something called sql navigator by TOAD, which I'll investigate after lunch.

But my question is, does anyone have any other options to consider? Third party tools to intercept sql text and log them? Another option from within oracle and 9iR2?
Re: Auditing of sql text without using oracle audit [message #138203 is a reply to message #138190] Tue, 20 September 2005 13:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Martin,
I beleive, to audit DML activities anyhow we need a trigger.
So using a trigger may be one of the options.
How about using logminer to audit?
I am myself looking for /in need of one such extensive approach.
I am still looking into logminer in this aspect.

Thanks & Regards.

Re: Auditing of sql text without using oracle audit [message #138227 is a reply to message #138190] Tue, 20 September 2005 15:49 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
But what about capturing selects if using LogMiner?

Early indications to me seem that the best approach is to just upgrade to 10g and use the db_extended option of built in auditing. But I'm still looking for alternatives.
Previous Topic: Data Level Security
Next Topic: audit procedure inside a package
Goto Forum:
  


Current Time: Fri Sep 24 03:39:48 CDT 2021