Find the user failed login attempts in Oracle database 11gR2
Posted by Mir Sayeed Hassan on June 18th, 2018
Find the user failed login attempts in Oracle database 11gR2
The user is created with the default profile set in database & the FAILED_LOGIN_ATTEMPTS set to 10, In this case if you exceed the more than 10 failed login attempts, then your account will be locked
Now verify the user with number of failed login attempts in database, We can find out this from the lcount column in user$ table as shown below:
sys@TESTDB> select instance_name,status,version from v$instance; INSTANCE_NAME STATUS VERSION ---------------- ------------ ----------------- testdb OPEN 11.2.0.4.0
Create the sample user; MIR
sys@TESTDB> create user mir identified by mir12345; User created.
sys@TESTDB> grant connect,resource to mir; Grant succeeded.
&
sys@TESTDB> connect mir/mir12345; Connected.
mir@TESTDB> show user USER is "MIR"
Verify the Profile assign to the user:
sys@TESTDB> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS where username='MIR'; USERNAME PROFILE ACCOUNT_STATUS ------------------------------ ------------------------------ -------------------------------- MIR DEFAULT OPEN
sys@TESTDB> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
sys@TESTDB> select USER#,NAME,LCOUNT from user$ where NAME='MIR'; USER# NAME LCOUNT ---------- ------------------------------ ---------- 530 MIR 0
Status of the user as shown:
sys@TESTDB> select username,account_status from dba_users where username='MIR'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- MIR OPEN
As you can see lcount is “0”, It mean no failed login attempts so far
Now try to attempts some failed login attempts
sys@TESTDB> sho user USER is "SYS"
sys@TESTDB> conn mir/miradsfgd; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. sys@TESTDB> connect sys/sys as sysdba Connected. sys@TESTDB> connect mir/jkdsgfgasdf; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
sys@TESTDB> select USER#,NAME,LCOUNT from user$ where NAME='MIR'; USER# NAME LCOUNT ---------- ------------------------------ ----------------- 530 MIR 2 - 2 times login failed attempts in database
Continue with 10 times & see does the account will be lock
sys@TESTDB> select username,account_status from dba_users where username='MIR'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- MIR LOCKED(TIMED)
sys@TESTDB> select USER#,NAME,LCOUNT from user$ where NAME='MIR'; USER# NAME LCOUNT ---------- ------------------------------------ ---------- 530 MIR 11
To resolve this you need to unlock the account & intimate the consult person to use the correct password
sys@TESTDB> alter user mir account unlock; User altered.
sys@TESTDB> select username,account_status from dba_users where username='MIR'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- MIR OPEN
sys@TESTDB> select USER#,NAME,LCOUNT from user$ where NAME='MIR'; USER# NAME LCOUNT ---------- ------------------------------ ---------- 530 MIR 0
Conclusions:
As per the above scenario, If you FAILED_LOGIN_ATTEMPTS is working or not, try giving the wrong password for more than 10 times without any success attempts, therefore the account will be lock status, In case if there is any 1 successful attempts to the user the the value will ve reset to ‘0’ – Hence tested & verified in our test env.