Re: Turn Audit to Monitor Logins

From: Yong Huang <>
Date: Sat, 6 Dec 2008 12:32:55 -0800 (PST)
Message-ID: <>


You just need to set audit_trail and type "audit session". The way to find who tried to login multiple times unsuccessfully causing an account to be locked is to see returncode of dba_audit_session or dba_audit_trail changing from multiple 1017's to 28000. See beginning from "Example: Find who attempted 5 times to login to APPUSER and caused the account to be locked:"

Dba_audit_session is an extracted version of dba_audit_trail. I like the latter because the additional columns sometimes satisfy my curiosity. For instance, from the text in comment_text column, you can get the client side port which helps you pinpoint the exact line in listener.log.

As you can imagine, implementing FAILED_LOGIN_ATTEMPTS in profile is best done to accounts an app server logins, not humans that don't remember the password well (unless the impact is limited to that person alone).

Yong Huang

> I suggest you see Ch 8 Database Auditing: Security Considerations in the
> Oracle(R) Database Security Guide 10*g* Release 2 (10.2)* *Part Number
> B14266-04 which covers basic reasons to audit. You can find the syntax in
> the SQL manual.
> See view dba_audit_sessions in the Oracle version# Reference manual.
> -- Mark D Powell --
> Phone (313) 592-5148

> Hi Gurus,
> I have an requirement from applicaition team to "Turn audit on for a an
> user in an Database, in order to monitor who logins (both failed and
> successful logins) to this account
> and when the login is made and from where, etc, as far as Oracle can
> capture these information.
> This audit is intended to find out who logins to the user repeatedly and
> unsuccessfully that caused the account to be locked in more than one
> occasions." Please help me on this
> Regards,
> Nagaraj.

Received on Sat Dec 06 2008 - 14:32:55 CST

Original text of this message