Re: Turn Audit to Monitor Logins
Date: Sat, 6 Dec 2008 12:32:55 -0800 (PST)
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 http://yong321.freeshell.org/oranotes/AuditLogMoveAndQuery.txt 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).
> 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