How to trace failed login attempts.
GOAL

How to trace failed login attempts using audit.

SYMPTOMS

The following incident is being reported

Host=dbpilot.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
How to trace failed login attempts using audit

1. Enable the audit. Database bounce is required.

-- Enable audit 

ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE;

-- Bounce the database 

-- SHUTDOWN IMMEDIATE
-- STARTUP

-- Verify the audit is enabled

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

2. Activate the audit for failed logins attempts

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Audit succeeded.

3. Wait some time and check 

-- Getting 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.

The query output provides all the necessary information to identify a problematic database account.

In this demo, the SAP_DEV database user attempted to log on to the database from the host DBPILOT\S001AS-APPS but failed with ORA-28001.

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
//

4. Now, deactivate the audit for failed logins attempts

NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

Noaudit succeeded.
REFERENCES

Finding the source of failed login attempts. (Doc ID 352389.1)
How to find "Failed Login USERNAME" in OEM (Doc ID 2564866.1)

Written At
13 JUL 202019:00
Red Hat Release
7.x x64
Enterprise Manager
13.3.0.0.0
Database Release
11.2.0.4