Print Friendly, PDF & Email
Why an Oracle user is locked? Who is locking an Oracle user?
GOAL

Finding out who locked an account in the database and why it was locked.

DEMO

DEMO: WHO IS LOCKING MY ORACLE ACCOUNT?

SOLUTION
How to trace failed login attempts using audit

1. Enable DATABASE AUDITING if it's not enabled yet. Database bounce is required.

SQL> SHO PARAMETER AUDIT_TRAIL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

2. Activate the audit for failed logins attempts

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Audit succeeded.

3. Wait some time and check

SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A11 
COL USERNAME FOR A10
COL CONNECTION_FROM FOR A30
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20

SELECT INSTANCE_NUMBER INUM,
       OS_USERNAME,
       USERNAME,
       USERHOST CONNECTION_FROM,
       TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,
       ACTION_NAME,
       OS_PROCESS,
       RETURNCODE 
FROM 
       DBA_AUDIT_SESSION 
WHERE 
      RETURNCODE IN (1017, 28000) 
ORDER BY EXTENDED_TIMESTAMP;
INUM OS_USERNAM USERNAME   USERHOST               TIMESTAMP            ACTION_NAM OS_PROCESS RETURNCODE
----- ---------- ---------- ---------------------- -------------------- ---------- ---------- ----------
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:07:58 LOGON      14793            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:07:59 LOGON      14797            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:00 LOGON      14801            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:02 LOGON      14805            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:03 LOGON      14809            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:04 LOGON      14814            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:05 LOGON      14818            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:06 LOGON      14822            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:07 LOGON      14826            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:08 LOGON      14830            1017
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:08 LOGON      14834           28000
    0 oracle     DEMO       dbpilot.vmware.local   08-JAN-2022 17:08:43 LOGON      14866           28000

12 rows selected.

The query output provides all the necessary information to identify the cause of the problem.

In this demo, something from the host dbpilot.vmware.local, using OS user oracle, attempted to log in to the database by the DEMO user.

First 10 attempts were made with the wrong password - ORA-1017. After 10 failed login attempts, the account was locked - ORA-28000.

SQL> !oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

SQL> !oerr ora 28000
28000, 00000, "The account is locked."
// *Cause:   The wrong password was entered multiple consecutive times as
//           specified by the profile parameter FAILED_LOGIN_ATTEMPTS, or the
//           DBA locked the account, or the user is a common user locked in
//           the root container.
// *Action:  Wait for the PASSWORD_LOCK_TIME 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
08 JAN 202218:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0