Showing posts with label DBA. Show all posts
Showing posts with label DBA. Show all posts

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: