WHO IS LOCKING MY ORACLE ACCOUNT?
Finding out who locked an account in the database and why it was locked.
DEMO: WHO IS LOCKING MY ORACLE ACCOUNT?
1. Enable DATABASE AUDITING if it's not enabled yet. Database bounce is required.
SQL> SHO PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
2. Activate the audit for failed logins attempts
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
Audit succeeded.
3. Wait some time and check
SET LINES 300
SET PAGES 999
COL INUM FOR 9999
COL OS_USERNAME FOR A11
COL USERNAME FOR A10
COL CONNECTION_FROM FOR A30
COL ACTION_NAME FOR A10
COL OS_PROCESS FOR A10
COL TIMESTAMP FOR A20
SELECT INSTANCE_NUMBER INUM,
OS_USERNAME,
USERNAME,
USERHOST CONNECTION_FROM,
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.
4. Now, deactivate the audit for failed logins attempts
NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;
Noaudit succeeded.
Finding the source of failed login attempts. (Doc ID 352389.1)
How to find "Failed Login USERNAME" in OEM (Doc ID 2564866.1)
