I have been using both Sign-On Audit [set to FORM level] and
Session Audit [ie, through the database
AUDIT SESSION command].
Although Sign-On Audit has been in place since day-one, the Session Audit
was initiated after "Auditors"
were unhappy about us not capturing attempts to connect as the APPS
user. With 15000 to 20000 concurrent
requests and 1000 to 1500 user logins per day, Session Audit for
APPS was still manageable and
had been in place for 6 months. Currently, due to other
considerations [performance during the
September month-end was poor and people wanted to "fix the
blame"], Session Audit is set to WHENEVER NOT SUCCESSFUL
for APPS but ALWAYS for other database accounts. [Business Analysts
have their own, Read-Only accounts with SELECT
privileges and do not have the APPS passwords].
As I purge Concurrent Request and Sign-On Audit data older than 10 days,
another Audit requirement has
been to capture these records for 6 months. I have a BEFORE-DELETE
trigger copying the records
from FND_CONCURRENT_REQUESTS, FND_LOGINS etc etc to ARCHIVE
tables.
This is an R11 11.0.3 environment with the complete Financials and
Distribution/SupplyChain modules.
Hemant
At 08:34 AM 31-10-03 -0800, you wrote:
Thanks for the input,
John. I am primarily concerned about I/O overhead, but I guess that
depends on the level of auditing selected, number of concurrent users,
which apps they are logged into, time of the month, etc.etc...
Vicki Pierce
Database Administration
x2401
John Kanagaraj <john.kanagaraj@hds.com>
Sent by: ml-errors@fatcity.com
10/30/2003 07:24 PM
Please respond to
ORACLE-L@fatcity.com
To
Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc
Subject
RE: Overhead Associated with Signon Audit in Financials 11.0
For all the non-APPS DBAs out there...
Oracle Applications 10.4 onwards (lowest version I have seen) provides for a
feature called 'Signon Auditing'. This is NOT Oracle's Auditing (which goes
into SYS.AUD$). It is a parameter driven auditing that records all Users
that logged in when set to USER, Application Responsibilities that they
chose (upon login as well as subsequently switched to) when set to
RESPONSIBILITY, in addition to recording the USER level, and the Forms that
they chose to run when set to FORMS, in addition to that recorded at
RESPONSIBILITY and USER levels. Thus, when set to FORMS, a user login would
at best produce a minimum of three rows, etc. These rows are updated when
the user logged out, so all sorts of reports about who is/was logged on,
forms currently being used, etc. can be determined. In fact, for an Apps DBA
to tie back a session to an actual user, at least USER level signon auditing
should be turned on. The problem with Apps is that all users would login in
the APPS schema using the encrypted password which is obtained using a
dummy connection... Forms and further Access is then determined by
'Responsbilities' that are in turn tied to 'Organizations' and 'Datasets'.
By default, almost all Applications tables record the last updated user and
timestamp, so there is some inbuilt auditing, albeit not a trail. Oracle
provides an additional Audit function that performs an audit trail for such
datasets, and this can produce significant overhead for data storage.
Thus.... all discussions about SYS.AUD$ are not really relevant in this
particular thread, although some good ideas have been aired. Switching on
Auditing without understanding what is ultimately required would be very
counterproductive, whether this is on an APPS database or not, in any case.
[As an aside, most of this is enabled via the AOL - Applications Object
Layer (aka FND - Foundation Layer) and is a solid example of providing
'Application' infrastructure. And don't get me started on the Concurrent
Processing - that's an excellent one too]
I am going to stop now and let Apps gurus such as Andy R, Tanel and Tim G
comment.
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
>-----Original Message-----
>From: Mladen Gogala [mailto:mladen@wangtrading.com]
>Sent: Thursday, October 30, 2003 1:39 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Overhead Associated with Signon Audit in Financials 11.0
>
>
>It is true, auditing adds significant overhead, but not
>session auditing.
>Significant overhead is added by DML auditing because you ad
>significant
>amount of modified blocks to every transaction you audit, you
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: john.kanagaraj@hds.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :
http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Oct 31 2003 - 11:24:25 CST
