OraFAQ Forum: Security » How to extract info from oracle audit files created on Operating System

Home » RDBMS Server » Security » How to extract info from oracle audit files created on Operating System (Oracle database Enterprise Edition, 11gR2, Solaris 5.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to extract info from oracle audit files created on Operating System [message #656469]

Thu, 06 October 2016 19:07

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

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8976
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

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

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

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 .

Report message to a moderator

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

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?

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Applying ROLE-based security to columns in a View in Oracle
Next Topic:	Access to Public Synonym

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 11:42:39 CDT 2025