DEMO: WHO IS LOCKING MY ORACLE ACCOUNT?
Finding out who locked an account in the database and why it was locked.
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.
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.
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
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
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.
Finding the source of failed login attempts. (Doc ID 352389.1)
How to find "Failed Login USERNAME" in OEM (Doc ID 2564866.1)
