Showing posts with label dba_audit_session. Show all posts
Showing posts with label dba_audit_session. 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:
Subscribe to:
Posts (Atom)