Home » RDBMS Server » Security » Auditing at table level in 11g (Oracle 11.2.0.3 on Sparc 64bit)
Auditing at table level in 11g [message #552739] Fri, 27 April 2012 12:53 Go to next message
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.
Re: Auditing at table level in 11g [message #552740 is a reply to message #552739] Fri, 27 April 2012 13:10 Go to previous messageGo to next message
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;
Re: Auditing at table level in 11g [message #552744 is a reply to message #552739] Fri, 27 April 2012 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
11g introduced many default audits, did you disable them?

Regards
Michel
Re: Auditing at table level in 11g [message #552748 is a reply to message #552744] Fri, 27 April 2012 13:40 Go to previous messageGo to next message
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.
Re: Auditing at table level in 11g [message #552750 is a reply to message #552748] Fri, 27 April 2012 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Auditing at table level in 11g [message #552753 is a reply to message #552750] Fri, 27 April 2012 15:02 Go to previous messageGo to next message
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
Re: Auditing at table level in 11g [message #552764 is a reply to message #552753] Sat, 28 April 2012 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Auditing at table level in 11g [message #552768 is a reply to message #552739] Sat, 28 April 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Auditing at table level in 11g [message #552775 is a reply to message #552768] Sat, 28 April 2012 04:36 Go to previous message
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.
Previous Topic: Spanish character encryption problem in UTF8
Next Topic: Applying Critical Patch Update
Goto Forum:
  


Current Time: Thu Mar 28 08:58:06 CDT 2024