Wednesday 24 May 2017

Failed login attempts



Number of failed login attempts exceeds threshold value message comes from OEM.
How to check what user is causing this? DBA_AUDIT_SESSION can help to identifiy the username,userhost, etc:
SQL> desc DBA_AUDIT_SESSION
 Name              
 -------------------
 OS_USERNAME       
 USERNAME          
 USERHOST          
 TERMINAL          
 TIMESTAMP         
 ACTION_NAME       
 LOGOFF_TIME       
 LOGOFF_LREAD      
 LOGOFF_PREAD      
 LOGOFF_LWRITE     
 LOGOFF_DLOCK      
 SESSIONID         
 RETURNCODE        
 CLIENT_ID         
 SESSION_CPU       
 EXTENDED_TIMESTAMP
 PROXY_SESSIONID   
 GLOBAL_UID        
 INSTANCE_NUMBER   
 OS_PROCESS
 


Lets check the errors within last day:


SELECT
  TO_CHAR(TIMESTAMP,'YYYY/MM/DD HH24:MI') TIMESTAMP,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  RETURNCODE
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
ORDER BY TIMESTAMP;
TIMESTAMP        OS_USERNAM USERNAME              TERMINAL          ACTION_NAME   RETURNCODE
---------------- ---------- --------------------- ----------------- ------------- ----------
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:56 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:57 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017
2017/05/24 12:57 SYSTEM     DBP_007_GENEOS_USER   LONGCASU0014      LOGON               1017

SELECT  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
SUBSTR(USERNAME,1,20) USERNAME,
SUBSTR(TERMINAL,1,20) TERMINAL,
ACTION_NAME,
RETURNCODE,count(*)
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
group by SUBSTR(OS_USERNAME,1,20),
SUBSTR(USERNAME,1,20),
SUBSTR(TERMINAL,1,20),
ACTION_NAME,RETURNCODE
order by count(*);
OS_USERNAM USERNAME                 TERMINAL         ACTION_NAME  RETURNCODE   COUNT(*)
---------- ------------------------ ---------------- ------------ ---------- ----------
umesnag    DBP_107_ETL_USER         unknown          LOGON              1017          1
singtaja   DBP_TOOLS_USER           unknown          LOGON              1017          1
singtaja   DBP_007_ACCESS_OWNER     unknown          LOGON             28000          1
rohivai    DBP_TOOLS_USER           unknown          LOGON              1017          1
dubeamib   DUBEAMIB                 unknown          LOGON             28001          1
kumshar    PRASAVV                  unknown          LOGON             28001          1
nunepab    DBP_011_STAGING_USER     unknown          LOGON              1017          1
singhash   DBP_TOOLS_USER           unknown          LOGON              1017          1
umalaks    URJA_DBP_107_RAP_OWN     unknown          LOGON              1017          1
umalaks    DBP_107_RAP_OWNER        unknown          LOGON              1017          1
kumakun    DBP_TOOLS_USER           unknown          LOGON              1017          5
panitan    DBP_014_APP_USER         unknown          LOGON              1017         10
dubeamib   DBP_107_ETL_USER         unknown          LOGON              1017         12
infarun    DBP_007_ETL_USER                          LOGON             28000         36
SYSTEM     DBP_007_GENEOS_USER      LONGCASU0014     LOGON              1017        121
SYSTEM     DBP_007_GENEOS_USER      LONGCASU0014     LOGON             28000       6920



RETURNCODE column is simply Oracle Error Code


SQL> !oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:   The user has entered wrong password consequently for maximum
//           number of times specified by the user's profile parameter
//           FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA
SQL> !oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action: