Print Friendly, PDF & Email
How do I find out who is locking an Oracle account?

QUESTIONS
Who is locking my account?
Who locked a user account?
How to track account lock?
Who specifies a wrong password during login?
Identifying a process which is locking an Oracle account by using an incorrect password.

SYMPTOMPS
A user complains about his account is being locked every 5-10 minutes. He asked to find out why it happens and who locks it.
By reviewing the DBA_USERS view, you see that the user account is in the LOCKED(TIMED) status.

CAUSE
This situation can happen in the case when something (a user, a script or an application) is trying to connect to a database with a wrong password multiple times and thereby locking an account.

Account locking behavior is based on the FAILED_LOGIN_ATTEMPTS profile parameter. By default, in the DEFAULT profile, the FAILED_LOGIN_ATTEMPTS allows 10 failed login attempts before an account will be locked.

TESTCASE

Create a user for the testcase

--Create user

CREATE USER TESTCASE IDENTIFIED BY TESTCASE;
GRANT CONNECT TO TESTCASE;

-- what is 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='TESTCASE';

USERNAME   ACCOUNT_STATUS  CREATED    LOCK_DATE  EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
TESTCASE   OPEN            18-DEC-17             16-JUN-18  DEFAULT

-- Get details about user profile 

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      COMMON
----------- ------------------------- ---------- ---------- ---------
DEFAULT     FAILED_LOGIN_ATTEMPTS     PASSWORD   10         NO
DEFAULT     PASSWORD_LOCK_TIME        PASSWORD   1          NO

According to the Oracle documentation, these 2 profile parameters 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 testcase the AUDIT TRAIL must be 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 TRAIL is disabled enable it by issuing the ALTER SYSTEM SET AUDIT_TRAIL command. Then bounce the database.

-- Enable option

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

-- Restart database 

-- SHUTDOWN IMMEDIATE
-- STARTUP 

Provided that AUDIT TRAIL is turned on issue the following commands

-- To Audit all failed login attempts

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- A table with audit data

SELECT * FROM DBA_AUDIT_SESSION;

no rows selected

Now everything is ready to catch all login failed attempts. So let’s simulate a situation when a user connects to the database providing a wrong password multiple times. Here is the simple bash script to perform the simulation.

NOTE

Before you can continue with the bash script you must set up Oracle environment variables. For instance

## Set Oracle environment variables 

export ORACLE_SID=dbpilot
export ORACLE_HOME=/oracle/product/12c
export PATH=${ORACLE_HOME}/bin:${ORACLE_HOME}/OPatch:$PATH

Here is the bash script

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

When bash script is finished query the audit table to find out details about locked user

-- Connected as privileged user

SQL> SHOW USER
USER is "SYS"

-- Get status of the TESTCASE 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='TESTCASE';

USERNAME   ACCOUNT_STATUS  CREATED    LOCK_DATE  EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
TESTCASE   LOCKED(TIMED)   18-DEC-17  18-DEC-17  16-JUN-18  DEFAULT

-- Try to connect as the TESTCASE user

SQL> connect TESTCASE/TESTCASE
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

As expected the bash script has locked the TESTCASE user. Now query audit table to get details about user account lock.

-- Connected as privileged user

SQL> SHOW USER
USER is "SYS"

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     TESTCASE   dbpilot.net     18-DEC-2017 13:25:23 LOGON      11533            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:24 LOGON      11540            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:25 LOGON      11547            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:26 LOGON      11554            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:27 LOGON      11561            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:28 LOGON      11568            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:29 LOGON      11575            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:31 LOGON      11582            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:32 LOGON      11589            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:33 LOGON      11596            1017
    0 oracle     TESTCASE   dbpilot.net     18-DEC-2017 13:25:33 LOGON      11603           28000

11 rows selected.

COL SYSTIMESTAMP FOR A40
SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
----------------------------------------
18-DEC-17 01.26.42.485294 PM +03:00

The DBA_AUDIT_SESSION view contains records for all failed login attempts made by the script. The RETURNCODE column of the view corresponds to the ORA-* errors raised during the script’s failed login attempts. The view provides enough information to find out who or what caused the locking of the TESTCASE user account.

In this example, the user TESTCASE unsuccessfully attempted to connect to the database from the server dbpilot.net with the wrong password 10 times (database raised ORA-01017 error), and thus the user TESTCASE locked its account itself (database raised ORA-28000 error).

The descriptions of the errors located in the RETURNCODE column are following:

oracle@dbpilot.net: oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

oracle@dbpilot.net: oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:   The user has entered wrong password consequently for maximum
//           number of times specified by the user's profile parameter
//           FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA

To unlock the user account you can either wait 1 day according to the PASSWORD_LOCK_TIME profile value or just unlock the user account by the ALTER USER … ACCOUNT UNLOCK command

-- Connected as privileged user

SQL> SHOW USER
USER is "SYS"

-- Unlock the TESTCASE user 

SQL> ALTER USER TESTCASE ACCOUNT UNLOCK;

-- Connect as the TESTCASE user 

SQL> connect TESTCASE/TESTCASE
Connected.

SQL> SHO USER
USER is "TESTCASE"

-- Check the TESTCASE user 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='TESTCASE';

USERNAME   ACCOUNT_STATUS  CREATED    LOCK_DATE  EXPIRY_DAT PROFILE
---------- --------------- ---------- ---------- ---------- -----------
TESTCASE   OPEN            18-DEC-17             16-JUN-18  DEFAULT

That’s it. We successfully simulated the situation of locking the user account and found out who did that.

As the final step of the testcase stop the failed login attempt auditing and drop the TESTCASE user.

-- Connected as privileged user

SQL> SHOW USER
USER is "SYS"

-- Stop failed login attempts auditing

SQL> NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

Noaudit succeeded.

-- Drop the user 

SQL> DROP USER TESTCASE;

User dropped.

 
 

Version  : 16:16 18.12.2017
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0