Re: Oracle Audit aud$ vs Database Logon Trigger
Date: Thu, 18 Oct 2012 19:39:24 -0700
Message-ID: <CAENwkM7jfoB56CEsnjSzp5gazVVzNaRsKbL_AFuUfSHW0xo7bw_at_mail.gmail.com>
Please allow me to give you a simple demonstration. See the simplicity to enable and disable auditing? Of course, you will need to test the performance against trigger. Also, make sure you move sys.aud$ to its own tablespace. I have not benchmark this against the trigger solution. [oracle_at_unknown080027c82a1c ~]$ cat vaudit.sql select username, action_name from dba_audit_session;
SYS _at_ orcl > audit session by scott whenever successful;
Audit succeeded.
SYS _at_ orcl > @vaudit
no rows selected
SYS _at_ orcl > conn scott/tiger
Connected.
SCOTT _at_ orcl > @vaudit
USERNAME ACTION_NAME ------------------------------ ---------------------------- SCOTT LOGON
SCOTT _at_ orcl > conn oe/oe
Connected.
OE _at_ orcl > @vaudit
USERNAME ACTION_NAME ------------------------------ ---------------------------- SCOTT LOGON SCOTT LOGOFF
OE _at_ orcl > conn scott/badpas
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
_at_ > conn / as sysdba
Connected.
SYS _at_ orcl > @vaudit
USERNAME ACTION_NAME ------------------------------ ---------------------------- SCOTT LOGON SCOTT LOGOFF
SYS _at_ orcl > noaudit session by scott;
Noaudit succeeded.
SYS _at_ orcl > conn scott/tiger
Connected.
SCOTT _at_ orcl > @vaudit
USERNAME ACTION_NAME ------------------------------ ---------------------------- SCOTT LOGON SCOTT LOGOFF
SCOTT _at_ orcl >
On Thu, Oct 18, 2012 at 2:47 PM, Walker, Jed S <Jed_Walker_at_cable.comcast.com
> wrote:
> If you use built-in auditing be careful about using he trail in the
> database. On RAC this can cause a lot of cluster waits.
>
> For what you need I'd probably create a logon trigger, and maybe just
> update a row for each user to show the latest logon time. That would keep
> the amount of data small and allow you to do those reports.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Sanjay Mishra
> Sent: Thursday, October 18, 2012 3:37 PM
> To: Christopher.Taylor2_at_parallon.net; kevin.lange_at_ppoone.com;
> mdinh235_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: Oracle Audit aud$ vs Database Logon Trigger
>
> Chris
> That will be lots of trigger plus if new users are added then we cannot
> automate. So it should be database based.
>
> Sanjay
>
>
>
> ________________________________
> From: "Christopher.Taylor2_at_parallon.net" <
> Christopher.Taylor2_at_parallon.net>
> To: kevin.lange_at_ppoone.com; smishra_97_at_yahoo.com; mdinh235_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Sent: Thursday, October 18, 2012 11:24 AM
> Subject: RE: Oracle Audit aud$ vs Database Logon Trigger
>
> If you use a logon trigger, consider putting it in the SCHEMA of the users
> - "CREATE ... AFTER LOGON ON SCOTT.SCHEMA..."
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Lange, Kevin G
> Sent: Thursday, October 18, 2012 9:58 AM
> To: smishra_97_at_yahoo.com; Michael Dinh
> Cc: oracle-l
> Subject: RE: Oracle Audit aud$ vs Database Logon Trigger
>
> I use both for different databases and reasons. I think I like the logon
> trigger more simply because it is more customizable on what data is
> recorded. Plus, it's a lot easier to exclude certain IDs in the logon
> trigger than it is in the Oracle Auditing. Of course, auditing is written
> by Oracle and integrated into the system (as opposed to one of us writing a
> logon trigger) so I tend to think that the code is beter and less of a drag
> on the system.
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Sanjay Mishra
> Sent: Thursday, October 18, 2012 9:40 AM
> To: Michael Dinh
> Cc: oracle-l
> Subject: Re: Oracle Audit aud$ vs Database Logon Trigger
>
> Thanks Mike. Do we have any pros and cons for both approach. This database
> is heavily accessed in terms of logon like 500+ user anytime and has 3000+
> user in the database. So want to explore as use this approach for this many
> user or so based on maintenance and reliability.
>
> Tx
> Sanjay
>
>
>
>
> ________________________________
> From: Michael Dinh <mdinh235_at_gmail.com>
> To: smishra_97_at_yahoo.com
> Cc: oracle-l <oracle-l_at_freelists.org>
> Sent: Wednesday, October 17, 2012 11:28 PM
> Subject: Re: Oracle Audit aud$ vs Database Logon Trigger
>
>
> I would use audit functionality for simplicity.
>
> AUDIT SESSION WHENEVER SUCCESSFUL;
>
> Query DBA_AUDIT_SESSION
>
> Use DBMS_AUDIT_MGMT to manage.
>
>
> HTH
>
> -Michael.
>
>
> On Wed, Oct 17, 2012 at 7:32 PM, Sanjay Mishra <smishra_97_at_yahoo.com>
> wrote:
>
> Hi
> >
> >Can someone help as what is best approach in term system performance
> >and best practices for auditing. Requirements is only to check what
> >user has not logon to the database in x number of days and create a
> >report on daily basis. Requirement is to report user who has not logon
> >to the database in last 180 days. So what is best practices like to use
> >Audit command or Database Logon Trigger. Any help in providing any fact
> >is highly appreciable
> >
> >TIA
> >Sanjay
> >
> >
> >--
> >http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity to
> which it is addressed. If the reader of this e-mail is not the intended
> recipient or his or her authorized agent, the reader is hereby notified
> that any dissemination, distribution or copying of this e-mail is
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and delete this e-mail immediately.
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 19 2012 - 04:39:24 CEST