Print Friendly, PDF & Email

 
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.

 
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