The Oracle RDBMS attempts to control unauthorized access to the system by guessing of a password with profile setting (FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME). FAILED_LOGIN_ATTTEMPTS is the parameter that controls the number of times the incorrect password can be presented before the account goes into a LOCKED(TIMED) status, preventing the user from logging on for a specific period of time.
PASSWORD_LOCK_TIME is the time in days an account will stay in a LOCKED(TIMED) status. In this article we will demonstrate how to determine what is locking a user account by presenting the incorrect password. This procedure will work on any OS.
NOTE: For this example to work correctly, the user profile must have the following limits set: FAILED_LOGIN_ATTEMPTS equal to 3. PASSWORD_LOCK_TIME equal to anything greater then zero.
1. Logon to your oracle database server as the oracle software owner.
2. Ensure your init parameter AUDIT_TRAIL is set to DB and then execute the following command to enable login auditing: audit session;
SQL> audit session;
Audit succeeded.
SQL>
3. Attempt to logon to SQLPLUS as a normal user four times with the incorrect password. In this example we are using the user account LJCATT.
SQL> connect ljcatt/asdfasdfa
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect ljcatt/kdlafjasdkljfa
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect ljcatt/kldafjalsdj
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect ljcatt/asdfjafja
ERROR:
ORA-28000: the account is locked
4. Search the dba_audit_session view for records with a returncode equal to 1017 which indicate a failed logon with the following SQL: select userid, userhost, terminal, timestamp, action_name from sys.dba_audit_trail where RETURNCODE=’1017′
column username format a9
column userhost format a9
column terminal format a10
column timestamp format a11
column action_name format a11
select username, userhost, terminal, timestamp, action_name from
sys.dba_audit_trail where RETURNCODE=’1017′;
OUTPUT
SQL> column username format a9
SQL> column userhost format a9
SQL> column terminal format a10
SQL> column timestamp format a11
SQL> column action_name format a11
SQL>
SQL> select username, userhost, terminal, timestamp, action_name from
sys.dba_audit_trail where RETU
RNCODE=’1017′;
USERNAME USERHOST TERMINAL TIMESTAMP ACTION_NAME
——— ——— ———- ———– ———–
LJCATT frankie pts/1 12-MAY-10 LOGON
LJCATT frankie pts/1 12-MAY-10 LOGON
LJCATT frankie pts/1 12-MAY-10 LOGON
SQL>
5 Analysis: From the audit information above we can see that the user LJCATT unsuccessfully attempted to connect to the Oracle database with the wrong password three times from the server FRANKIE. This is normally enough information find out whom or what is causing the locking of an account.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com