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.

SYMPTOMS

A user is getting ORA-28000: The account is locked error frequently. Unlocking the account with ALTER USER ... ACCOUNT UNLOCK statement fixes issue temporarily. Then after some time, the same account lock happens again.

CAUSE

It happens when something (user, script, or application) keeps connecting to a database with a INCORRECT PASSWORD multiple times.

The account locks automatically whenever a certain number of failed login attempts occurs, with the total failed login attempts being more than the value of the FAILED_LOGIN_ATTEMPTS user profile parameter.

DEMO

Create a user for the demo

SQL> CREATE USER DEMO IDENTIFIED BY DEMO;

User created.

SQL> GRANT CONNECT TO DEMO;

Grant succeeded.

What is the user's default profile?

-- The user's default profile

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='DEMO';

USERNAME   ACCOUNT_STATUS  CREATED    LOCK_DATE  EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
DEMO       OPEN            08-JAN-22             07-JUL-22  DEFAULT


-- User profile password policies

COL RESOURCE_NAME FOR A25 
COL RESOURCE_TYPE FOR A10
COL LIMIT FOR A10

SELECT * FROM DBA_PROFILES 
WHERE 
       PROFILE='DEFAULT' AND 
       RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');

PROFILE     RESOURCE_NAME             RESOURCE_T LIMIT      COM INH IMP
----------- ------------------------- ---------- ---------- --- --- ---
DEFAULT     FAILED_LOGIN_ATTEMPTS     PASSWORD   10         NO  NO  NO
DEFAULT     PASSWORD_LOCK_TIME        PASSWORD   1          NO  NO  NO

These profile's RESOURCES are

FAILED_LOGIN_ATTEMPTS

Specify the number of consecutive failed attempts to log in to the user account before the account is locked. If you omit this clause, then the default is 10 times.

PASSWORD_LOCK_TIME

Specify the number of days an account will be locked after the specified number of consecutive failed login attempts. If you omit this clause, then the default is 1 day.

To proceed further with the demo, enable DATABASE AUDITING

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

Activate the audit for failed logins attempts

SQL> AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Audit succeeded.

Run the following bashcommand that attempts to log in to the database with the wrong password 11 times

time for i in {1..11}; do echo wrong_password | sqlplus DEMO 2>&1 1>/dev/null; done
NOTE

Before running the bash command, you must configure Oracle Environment Variables.

When the bash command completes, query the audit table to get details about the locked user

-- Connect as privileged user

SQL> SHOW USER
USER is "SYS"

-- Get status of the DEMO user

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='DEMO';

USERNAME   ACCOUNT_STATUS  CREATED    LOCK_DATE  EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
DEMO       LOCKED(TIMED)   08-JAN-22  08-JAN-22  07-JUL-22  DEFAULT

-- Attempt to connect as the DEMO user

SQL> connect DEMO/DEMO
ERROR:
ORA-28000: The account is locked.

Warning: You are no longer connected to ORACLE.

As expected, the bash command locked the DEMO user.

Query the audit table to get details about the account lock.

-- Connect as privileged user

SQL> SHOW USER
USER is "SYS"


-- Query audit table 

SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A10 
COL USERNAME FOR A10
COL USERHOST FOR A30
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 
      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.

The DEMO account will be locked for a time specified in the PASSWORD_LOCK_TIME profile value.

To unlock the account immediately use the ALTER USER ... ACCOUNT UNLOCK statement

-- Connect as privileged user

SQL> SHOW USER
USER is "SYS"

-- Unlock the DEMO user 

SQL>  ALTER USER DEMO ACCOUNT UNLOCK;

User altered.

-- Connect as the DEMO user 

SQL> connect DEMO/DEMO
Connected.

SQL> SHO USER
USER is "DEMO"

That's it. We successfully simulated the user account lock and found who did it.

As the final step of the demo, deactivate the audit for failed logins attempts and drop the DEMO user.

-- Connect as privileged user

SQL> SHOW USER
USER is "SYS"

-- Disable failed login auditing

SQL> NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

Noaudit succeeded.

-- Drop the DEMO user 

SQL> DROP USER DEMO;

User dropped.

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