WHO IS LOCKING MY ACCOUNT?
QUESTIONS
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
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;
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Related Posts
1 Comment
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Just wanted to say thank you very much for writing this up.