Home » RDBMS Server » Security » How to extract info from oracle audit files created on Operating System (Oracle database Enterprise Edition, 11gR2, Solaris 5.10)
How to extract info from oracle audit files created on Operating System [message #656469] Thu, 06 October 2016 19:07 Go to next message
SSharma7985
Messages: 8
Registered: January 2011
Location: India
Junior Member
Hi Experts ,

Is there some tool available to extract info from the oracle audit files . We enabled auditing on all our databases with audit_trail=OS and below mentioned to be audited .

AUDIT SESSION;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;

IS there some oracle inbuilt package which can be used to generate auditing reports from audit files .

thanks,
saurabh
Re: How to extract info from oracle audit files created on Operating System [message #656470 is a reply to message #656469] Thu, 06 October 2016 20:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SSharma7985 wrote on Thu, 06 October 2016 17:07
Hi Experts ,

Is there some tool available to extract info from the oracle audit files . We enabled auditing on all our databases with audit_trail=OS and below mentioned to be audited .

AUDIT SESSION;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;

IS there some oracle inbuilt package which can be used to generate auditing reports from audit files .

There is NO builtin auditing reports since little to no auditing is enabled by default.
You get to write your own AUDIT report that meet your local requirements.
Re: How to extract info from oracle audit files created on Operating System [message #656472 is a reply to message #656469] Fri, 07 October 2016 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think the closest to package that would give you programmatic access to the audit trail would to set the destination to XML[,EXTENDED] and then query the v$xml_audit_trail view. You'll need to write the code yourself. If we all do it together, we can present it at OOW and be famous Smile
Re: How to extract info from oracle audit files created on Operating System [message #656473 is a reply to message #656472] Fri, 07 October 2016 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It's not difficult to do it, the main problem is to know what "report" covers.
In short, what is the specification?

Re: How to extract info from oracle audit files created on Operating System [message #656484 is a reply to message #656473] Fri, 07 October 2016 11:31 Go to previous messageGo to next message
SSharma7985
Messages: 8
Registered: January 2011
Location: India
Junior Member
thanks john for the suggestion , i was thinking of doing the same . I will test that on few databases .

Hi michel , requirement is to develop a weekly report which contains
1) who logged in(login time/logoff time/machine)
2) how many select queries were fired
3) how many DML's were fired

In case if some data leak is reported then we are supposed to provide a detailed report which contains which user fired what query at a given time .
Re: How to extract info from oracle audit files created on Operating System [message #656486 is a reply to message #656484] Fri, 07 October 2016 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SSharma7985 wrote on Fri, 07 October 2016 09:31


In case if some data leak is reported then we are supposed to provide a detailed report which contains which user fired what query at a given time .
post SQL & results that shows an example of a data leak.

is application 3-tier?
Re: How to extract info from oracle audit files created on Operating System [message #656503 is a reply to message #656484] Sat, 08 October 2016 12:24 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
SSharma7985 wrote on Fri, 07 October 2016 11:31
thanks john for the suggestion , i was thinking of doing the same . I will test that on few databases .

Hi michel , requirement is to develop a weekly report which contains
1) who logged in(login time/logoff time/machine)
2) how many select queries were fired
3) how many DML's were fired

In case if some data leak is reported then we are supposed to provide a detailed report which contains which user fired what query at a given time .
Ok, sounds like you may be called upon to be able to answer a question like "How the H*** did WikiLeaks get hold of that?" To do that, just be aware that you are going to have to be auditing to a fairly fine level, and that will require a lot of disk space, depending on your retention requirements. You also need to look at the resulting audit records and make sure the have the necessary information. If they don't have it, then how you actually read and report those audit records is a moot point.

And if WikiLeaks (or something similar - even something totally internal to the organization) is really a concern, then you need to consider how to lock the barn door, in addition to simply getting a picture of who is coming and going.
Previous Topic: Applying ROLE-based security to columns in a View in Oracle
Next Topic: Access to Public Synonym
Goto Forum:
  


Current Time: Fri Mar 29 09:36:15 CDT 2024