OraFAQ Forum: Security » Auditing at table level in 11g

Home » RDBMS Server » Security » Auditing at table level in 11g (Oracle 11.2.0.3 on Sparc 64bit)

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

Auditing at table level in 11g [message #552739]

Fri, 27 April 2012 12:53

naresh515
Messages: 11
Registered: September 2006

Junior Member

Hello All,

We have recently upgraded our DR environment from 9i to 11g. We have auditing turned on for 3 tables.

On 9i, AUD$ table size is 11G for 12 months and the upgraded 11g environment has 9G in 2 days....

Below is the sql statement we used to turn on auditing on these tables.

audit select,update,delete on audit_Test2 by session;

Opened SR with Oracle, but no proper response from them...

Can you please advice what changes we need to do in order to reduce the amount of audit data on these tables in 11g?

Thanks,
Raghu Yella.

Report message to a moderator

Re: Auditing at table level in 11g [message #552740 is a reply to message #552739]

Fri, 27 April 2012 13:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you please advice what changes we need to do in order to reduce the amount of audit data on these tables in 11g?
noaudit select,update,delete on audit_Test2 by session;

Report message to a moderator

Re: Auditing at table level in 11g [message #552744 is a reply to message #552739]

Fri, 27 April 2012 13:26

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

Senior Member
Account Moderator

11g introduced many default audits, did you disable them?

Regards
Michel

Report message to a moderator

Re: Auditing at table level in 11g [message #552748 is a reply to message #552744]

Fri, 27 April 2012 13:40

naresh515
Messages: 11
Registered: September 2006

Junior Member

Blackswan - noaudit -> will totally disable auditing on these tables. We want auditing on these tables similar to what we had in 9i but it seems like 11g is placing more information in the AUD$ table.

Michel - Oracle support also specified that there are many default audits. Can you please direct us with any notes or steps to look into the defaults?

Thanks.

Report message to a moderator

Re: Auditing at table level in 11g [message #552750 is a reply to message #552748]

Fri, 27 April 2012 14:19

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

Senior Member
Account Moderator

The following views gives you which audits are enabled:
dba_obj_audit_opts
dba_priv_audit_opts
dba_stmt_audit_opts

Regards
Michel

Report message to a moderator

Re: Auditing at table level in 11g [message #552753 is a reply to message #552750]

Fri, 27 April 2012 15:02

naresh515
Messages: 11
Registered: September 2006

Junior Member

Thanks Michael.

These are the settings we currently have in 9i and 11g. But in 11g the amount of data is more.

Here is what we came across.
"On 9i - when a session is opened and performing inserts/updates/select/deletes in a loop of for eg., 100.. it will generate one entry in AUD$ table. But in 11g, the same loop of 100 is generating 100 entries in AUD$ table" HOW TO RESTRICT THIS?

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI
LOYALTY_OWNER ACCOUNT_REDEMPTIONS TABLE -/- -/- -/- S/S -/- -/- -/- -/- -/- S/S S/S -/- -/- -/- -/- -/-
e

Report message to a moderator

Re: Auditing at table level in 11g [message #552764 is a reply to message #552753]

Sat, 28 April 2012 00:46

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

Senior Member
Account Moderator

Quote:

On 9i - when a session is opened and performing inserts/updates/select/deletes in a loop of for eg., 100.. it will generate one entry in AUD$ table. But in 11g, the same loop of 100 is generating 100 entries in AUD$ table" HOW TO RESTRICT THIS?

In 9i, you surely audit BY SESSION when you do it BY ACCESS in 11g.

Regards
Michel

Report message to a moderator

Re: Auditing at table level in 11g [message #552768 is a reply to message #552739]

Sat, 28 April 2012 01:24

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

Senior Member
Account Moderator

Same question at http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=51279

Answer provided by Tarun Biswas

This was an intentional change with the new auditing architecture introduced in 11g. From 11.1.0.7 the auditing system uses "write once" audit records, this means that once a record has been written it can't be updated. This change improves performance of the auditing system (because there is less contention and it removes the need for indexes on aud$). This also means that the db audit trail works in the same way as the XML and OS audit trails. This change is architectural, there is no way to go back to the old BY SESSION auditing behavior.

Also read MOS note AUD$ Growing After Upgrade to 11g or ATC patch installed [ID 852815.1]

Regards
Michel

Report message to a moderator

Re: Auditing at table level in 11g [message #552775 is a reply to message #552768]

Sat, 28 April 2012 04:36

naresh515
Messages: 11
Registered: September 2006

Junior Member

Thanks for the update on this Michel. Appreciate your time on this.

We will plan on implementing FGA and see how it works..

Thanks,
Raghu Yella.

Report message to a moderator

Previous Topic:	Spanish character encryption problem in UTF8
Next Topic:	Applying Critical Patch Update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 00:41:20 CST 2024