How do you check the User failed login attempts without auditing the user?
A user created with default profile and the profile is modified to have the resource name FAILED_LOGIN_ATTEMPTS set to 10. Now the user account will lock when the attempts to connect to the user is more than 10.
How do we verify that there are so many number of failed login attempts earlier.
Is auditing the user the only solution ?
No
You can find the FAILED_LOGIN_ATTEMPTS value from the lcount column of user$ table.
SQL> select * from dba_profiles where resource_name = 'FAILED_LOGIN_ATTEMPTS' and limit=10;
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10
First i queried the failed login attemps and it shows the value "0".
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 0
Then i tried with one failed login attempt from another session and the value increased to 1.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 1
Again tried with failed login attempt from another session and the value increased to 2.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 2
Now i tried to connect to the user successfully without fail and the failed login attempt parameter is set to "0" agian.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 0
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
10
First i queried the failed login attemps and it shows the value "0".
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 0
Then i tried with one failed login attempt from another session and the value increased to 1.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 1
Again tried with failed login attempt from another session and the value increased to 2.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 2
Now i tried to connect to the user successfully without fail and the failed login attempt parameter is set to "0" agian.
SQL> select USER#,NAME,LCOUNT from user$ where NAME='RAC_ACCNT';
USER# NAME LCOUNT
---------- ------------------------------ ----------
303 RAC_ACCNT 0
Conclusions :
============
In order to check if the FAILED_LOGIN_ATTEMPTS is working or not , we need to try giving wrong passwords 10 times continuously without any successful attempts. Then 11th attempt will lock the user account. But if there is atleast one single successful attempt to connect to the user , the value resets to "0" again.
Posts
