WHO IS LOCKING MY ACCOUNT?

 
QUESTIONS

 
SYMPTOMPS

 
CAUSE

 
TESTCASE

So create a user for this testcase and gather details

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

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

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 official documentation

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.

Provided that 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 audit is disabled, enable by next command and restart the database

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

Enable audiding for failed logon attempts and then query DBA_AUDIT_SESSION view

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Audit succeeded.

SELECT * FROM DBA_AUDIT_SESSION;

no rows selected

So let’s simulate the situation. Here is the simple bash script to lock the account by connecting with a wrong password.

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

Now find out locked users

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

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


Warning: You are no longer connected to ORACLE.

Query table to get details

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 all failed logins attempts made by the script. The column RETURNCODE in the view corresponds to the ORA- error raised on the failed logins. It’s enough information to find out who or what caused locking of the account. Here the user TESTCASE unsuccessfully attempted to connect to the database with wrong password 10 times from the server dbpilot.net

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 account you can wait 1 day accorging to the PASSWORD_LOCK_TIME profile value, or just unlock it by the following command

ALTER USER TESTCASE ACCOUNT UNLOCK;

connect TESTCASE/TESTCASE
Connected.

SHO USER
USER is "TESTCASE"

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

To disable auditing issue the following command

NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;