Re: monitor failed login attempts and locked schema accounts

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 26 May 2008 07:40:09 GMT
Message-ID: <483a6959$0$30637$834e42db@reader.greatnowhere.com>


On Sat, 24 May 2008 21:39:20 -0700, steve wrote:

> Hi,
> Other than using Audit Vault, would it be possible to monitor failed
> login attempts? Listener.log only shows successful log in attempts.
>
> Also, would it be possible to be alerted when a schema account status
> changed to "Locked" due to several failed login attempts?
>
> Thanks.
>
> Steve

First, set your AUDIT_TRAIL parameter to "DB". That'll require a restart. After that, do the following:

AUDIT SESSION WHENEVER NOT SUCCESSFUL; The result will look like this:
SQL> select * from dba_audit_trail;

OS_USERNAME



USERNAME

USERHOST

TERMINAL

TIMESTAM OWNER
-------- ------------------------------

OBJ_NAME
    ACTION ACTION_NAME                  NEW_OWNER
---------- ---------------------------- ------------------------------
NEW_NAME
OBJ_PRIVILEGE    SYS_PRIVILEGE                            ADM
---------------- ---------------------------------------- ---
GRANTEE                        AUDIT_OPTION
------------------------------ ----------------------------------------
SES_ACTIONS         LOGOFF_T LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE
------------------- -------- ------------ ------------ -------------
LOGOFF_DLOCK

COMMENT_TEXT

 SESSIONID ENTRYID STATEMENTID RETURNCODE ---------- ---------- ----------- ---------- PRIV_USED

CLIENT_ID

ECONTEXT_ID SESSION_CPU


EXTENDED_TIMESTAMP
PROXY_SESSIONID GLOBAL_UID                       INSTANCE_NUMBER
--------------- -------------------------------- ---------------
OS_PROCESS       TRANSACTIONID           SCN
---------------- ---------------- ----------
SQL_BIND

SQL_TEXT

mgogala
SCOTT
nwxp-mgogala
pts/1
26.05.08

       100 LOGON Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp) (HOST=10.1.14
.139)(PORT=47672))
    491557 1 1 1017

26.05.08 09:37:11.826247 +02:00

                                                               0
10414

Elapsed: 00:00:00.21
SQL> Return code can be found, surprisingly enough, in the "RETURNCODE" column. In this case, it is 1017, which stands for: "ORA-01017: invalid username/password; logon denied"

No need to use audit or any other types of vaults. Normal auditing options which are part of the database are all you need. Of course, you also need the Guide and a towel.

-- 
Mladen Gogala
http://mgogala.freehostia.com
Received on Mon May 26 2008 - 02:40:09 CDT

Original text of this message