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:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment