EM Event: Critical:oradb – Number of failed login attempts exceeds threshold value.
ISSUE
Just got the “Number of failed login attempts exceeds threshold value” error from the Enterprise Manager Cloud Control 13c? And you aren’t sure what should you do?
Bellow the solution using audit option.
Bellow the solution using audit option.
SYMPTOMS
You received the following alert message from the Enterprise Manager Cloud Control 13c
Host=bpilot.net Target type=Database Instance Target name=oradb Categories=Security Message=Number of failed login attempts exceeds threshold value. Severity=Critical Event reported time=Jul 13, 2020 10:26:55 AM MSK Operating System=Linux Platform=x86_64 Associated Incident Id=127157 Associated Incident Status=New Associated Incident Owner= Associated Incident Acknowledged By Owner=No Associated Incident Priority=None Associated Incident Escalation Level=0 Event Type=Metric Alert Event name=audit_failed_logins2:failed_login_count Total Occurrences=307539 Occurrences in this event=720 First Occurred in this event=Jul 13, 2020 9:57:01 AM MSK Last Occurred in this event=Jul 13, 2020 10:26:51 AM MSK Metric Group=Failed Logins Metric=Failed Login Count Metric value=720 Key Value= Rule Name=ROOT_NOTIFICATION_RULE,ALL TARGET EVENTS Rule Owner=SYSMAN Update Details: Number of failed login attempts exceeds threshold value.
SOLUTION
Provided that the audit trail is turned on.
COL NAME FOR A15
COL VALUE FOR A10
SELECT NAME,DISPLAY_VALUE VALUE FROM V$PARAMETER WHERE NAME='audit_trail';
NAME VALUE
--------------- ----------
audit_trail DB
If the audit is disabled, enable it by the next command and restart a database
ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
Activate the audit for failed logins attempts
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
Audit succeeded.
When you recieve another “Number of failed login attempts exceeds threshold value” error query the DBA_AUDIT_SESSION table to get details about an error
-- Get details for the last day
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A10
COL USERNAME FOR A10
COL USERHOST FOR A25
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
OS_USERNAME,
USERNAME,
USERHOST,
TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,
ACTION_NAME,
OS_PROCESS,
RETURNCODE
FROM
DBA_AUDIT_SESSION
WHERE
EXTENDED_TIMESTAMP > (SYSDATE - 1) AND RETURNCODE > 0
ORDER BY EXTENDED_TIMESTAMP;
INUM OS_USERNAM USERNAME USERHOST TIMESTAMP ACTION_NAM OS_PROCESS RETURNCODE
----- ---------- ---------- ----------------------- -------------------- ---------- ---------- ----------
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:32 LOGON 5265 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:32 LOGON 5267 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:42 LOGON 5278 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:42 LOGON 5282 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:42 LOGON 5284 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:42 LOGON 5286 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:52 LOGON 5301 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:52 LOGON 5307 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:52 LOGON 5313 28001
0 система SAP_DEV DBPILOT\S001AS-APPS 13-JUL-2020 15:52:52 LOGON 5319 28001
32718 rows selected.
-- Current timestamp
COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------
13-JUL-20 03.53.15.590757 PM +03:00
So, the query output tell us there are lots of failed logins attempts for at least for the last day to the database user SAP_DEV. Logins fail because of the error ORA-28001.
Ok, but what the errors 28001 means?
SQL> !oerr ora 28001
28001, 00000, "the password has expired"
// *Cause: The user's account has expired and the password needs to be
// changed
// *Action: change the password or contact the DBA
//
The error description tells us that application from the DBPILOT\S001AS-APPS host is trying to connect to the expired user SAP_DEV and it fails all the time because the account is expired.
Let’s check the user account status
-- Account status
SET LINES 200
SET PAGES 999
COL USERNAME FOR A10
COL ACCOUNT_STATUS FOR A15
COL CREATED FOR A10
COL LOCK_DATE FOR A10
COL EXPIRY_DATE FOR A10
COL PROFILE FOR A11
SELECT USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE,PROFILE FROM DBA_USERS WHERE USERNAME='SAP_DEV';
USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
SAP_DEV EXPIRED 11-DEC-16 04-JUL-20 DEFAULT
-- Current timestamp
COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------
13-JUL-20 03.54.15.590757 PM +03:00
So, In the EXPIRY_DATE column we can see that the user SAP_DEV is in the expired status for the last 10 days (Oops!).
Lets confirm that by querying the DBA_AUDIT_SESSION table again
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL USERNAME FOR A10
COL USERHOST FOR A30
COL ACTION_NAME FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
USERNAME,
USERHOST,
ACTION_NAME,
TO_DATE(TIMESTAMP,'DD-MON-YYYY') FAILED_DATE,
COUNT(ACTION_NAME) FAILED_ATTEMPTS
FROM
DBA_AUDIT_SESSION
WHERE
RETURNCODE = 28001
GROUP BY
INSTANCE_NUMBER,
USERNAME,
USERHOST,
ACTION_NAME,
TO_DATE(TIMESTAMP,'DD-MON-YYYY')
ORDER BY 5;
INUM USERNAME USERHOST ACTION_NAM FAILED_DATE FAILED_ATTEMPTS
----- ---------- ------------------------- ---------- -------------- ---------------
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 04-JUL-20 12023
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 05-JUL-20 34556
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 06-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 07-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 08-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 09-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 10-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 11-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 12-JUL-20 34560
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 13-JUL-20 22872
10 rows selected
It has been confirmed that the application has been trying to connect to the SAP_DEV user since 04-JUL-20 and fails permanently.
To fix the issue just reset the SAP_DEV password. Here I’m going to reset the password to the same value as it had before
SQL> SELECT PASSWORD FROM USER$ WHERE NAME='SAP_DEV';
PASSWORD
------------------------------------------------------------------------------------------
675EBC774A9FDC86
SQL> ALTER USER SAP_DEV IDENTIFIED BY VALUES '675EBC774A9FDC86';
User altered.
-- Check status again
SET LINES 200
SET PAGES 999
COL USERNAME FOR A10
COL ACCOUNT_STATUS FOR A15
COL CREATED FOR A10
COL LOCK_DATE FOR A10
COL EXPIRY_DATE FOR A30
COL PROFILE FOR A11
SELECT USERNAME,
ACCOUNT_STATUS,
CREATED,LOCK_DATE,
TO_CHAR(EXPIRY_DATE,'DD-MON-YYYY HH24:MI:SS') EXPIRY_DATE,
PROFILE
FROM
DBA_USERS WHERE USERNAME='SAP_DEV';
USERNAME ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DATE PROFILE
---------- --------------- ---------- ---------- ------------------------------ -----------
SAP_DEV OPEN 11-DEC-16 09-JAN-2021 15:55:00 DEFAULT
When account password has been reset the issue has gone.
-- The latest timestamp with the error ORA-28001
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL USERNAME FOR A10
COL USERHOST FOR A25
COL ACTION_NAME FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
USERNAME,
USERHOST,
ACTION_NAME,
TO_CHAR(MAX(EXTENDED_TIMESTAMP),'DD-MON-YYYY HH24:MI:SS') TIMESTAMP
FROM
DBA_AUDIT_SESSION
WHERE
RETURNCODE = 28001
-- AND USERNAME='SAP_DEV'
GROUP BY INSTANCE_NUMBER,USERNAME, USERHOST, ACTION_NAME;
INUM USERNAME USERHOST ACTION_NAM TIMESTAMP
----- ---------- ------------------------- ---------- --------------------
0 SAP_DEV DBPILOT\S001AS-APPS LOGON 13-JUL-2020 15:52:52
-- Current timestamp
COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------
13-JUL-20 03.56.11.294757 PM +03:00
No new failed logins attempts(28001) are recorded. So the issue has been fixed.
You can also confirm it by querying the Enterprise Manager repository database. No new “Number of failed login attempts exceeds threshold value.” errors must be generated after the account becomes OPEN.
-- All generated alert by Enterpirse Manager for the database oradb by days
SET LINES 300
SET PAGES 999
COL TARGET_NAME FOR A10
COL "ERROR DATE" FOR A11
COL "OEM ALERT" FOR A60
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
SELECT TARGET_NAME,
TO_DATE(COLLECTION_TIMESTAMP,'DD-MM-YYYY') "ERROR DATE",
MESSAGE "OEM ALERT",
COUNT(MESSAGE) "ERRORS NUMBER"
FROM
MGMT_VIEW.MGMT$ALERT_NOTIF_LOG
WHERE
MESSAGE LIKE '%Number of failed login attempts exceeds threshold value%' AND TARGET_NAME='oradb'
GROUP BY
TARGET_NAME,TO_DATE(COLLECTION_TIMESTAMP,'DD-MM-YYYY'),MESSAGE
ORDER BY 2;
TARGET_NAM ERROR DATE OEM ALERT ERRORS NUMBER
---------- ----------- ------------------------------------------------------------ -------------
oradb 27-JUN-2020 Number of failed login attempts exceeds threshold value. 1
oradb 04-JUL-2020 Number of failed login attempts exceeds threshold value. 17
oradb 05-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 06-JUL-2020 Number of failed login attempts exceeds threshold value. 47
oradb 07-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 08-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 09-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 10-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 11-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 12-JUL-2020 Number of failed login attempts exceeds threshold value. 48
oradb 13-JUL-2020 Number of failed login attempts exceeds threshold value. 32
11 rows selected.
-- The latest generated alert (Queried on the next day)
COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
----------------------------------------
14-JUL-20 09.51.42.902197 AM +03:00
SET LINES 300
SET PAGES 999
COL TARGET_NAME FOR A10
COL "ERROR DATE" FOR A11
COL "OEM ALERT" FOR A60
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT TARGET_NAME,
MESSAGE "OEM ALERT",
MAX(TO_DATE(COLLECTION_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS')) "ERROR TIMESTAMP"
FROM
MGMT_VIEW.MGMT$ALERT_NOTIF_LOG
WHERE
MESSAGE LIKE '%Number of failed login attempts exceeds threshold value%' AND TARGET_NAME='oradb'
GROUP
BY TARGET_NAME, MESSAGE;
TARGET_NAM OEM ALERT ERROR TIMESTAMP
---------- ------------------------------------------------------------ -----------------------------
oradb Number of failed login attempts exceeds threshold value. 13-JUL-2020 15:56:55
After the account became OPEN no new errors were generated by the Enterprise Manager anymore.
RELATED
WHO IS LOCKING MY ACCOUNT?
Version : 18:39 13.07.2020
Platform : Oracle Linux Server 7.2
Database : Oracle database 11.2.0.4
Platform : Oracle Linux Server 7.2
Database : Oracle database 11.2.0.4
Leave a Reply
Social Link(s)
