Re: Oracle Audit aud$ vs Database Logon Trigger
- From: Michael Dinh <mdinh235@xxxxxxxxx>
- To: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- Date: Thu, 18 Oct 2012 19:39:24 -0700
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@unknown080027c82a1c ~]$ cat vaudit.sql
select username, action_name from dba_audit_session;
SYS @ orcl > audit session by scott whenever successful;
Audit succeeded.
SYS @ orcl > @vaudit
no rows selected
SYS @ orcl > conn scott/tiger
Connected.
SCOTT @ orcl > @vaudit
USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT LOGON
SCOTT @ orcl > conn oe/oe
Connected.
OE @ orcl > @vaudit
USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT LOGON
SCOTT LOGOFF
OE @ orcl > conn scott/badpas
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
@ > conn / as sysdba
Connected.
SYS @ orcl > @vaudit
USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT LOGON
SCOTT LOGOFF
SYS @ orcl > noaudit session by scott;
Noaudit succeeded.
SYS @ orcl > conn scott/tiger
Connected.
SCOTT @ orcl > @vaudit
USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT LOGON
SCOTT LOGOFF
SCOTT @ orcl >
On Thu, Oct 18, 2012 at 2:47 PM, Walker, Jed S <Jed_Walker@xxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Sanjay Mishra
> Sent: Thursday, October 18, 2012 3:37 PM
> To: Christopher.Taylor2@xxxxxxxxxxxx; kevin.lange@xxxxxxxxxx;
> mdinh235@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxx" <
> Christopher.Taylor2@xxxxxxxxxxxx>
> To: kevin.lange@xxxxxxxxxx; smishra_97@xxxxxxxxx; mdinh235@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Lange, Kevin G
> Sent: Thursday, October 18, 2012 9:58 AM
> To: smishra_97@xxxxxxxxx; 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> 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@xxxxxxxxx>
> To: smishra_97@xxxxxxxxx
> Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxx>
> 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
> >
> >
> >--
> >//www.freelists.org/webpage/oracle-l
> >
> >
> >
> --
> //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.
>
> --
> //www.freelists.org/webpage/oracle-l
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
