Home » , » How do you check the User failed login attempts without auditing theuser?

How do you check the User failed login attempts without auditing theuser?

Written By Srikrishna Murthy Annam on Tuesday, December 29, 2009 | 4:36 PM

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


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.
Share this article :

Related Articles By Category



+ comments + 1 comments

Mary Jo Smith
November 21, 2012 at 2:49 PM

not sure what schema houses the users$ table. It appears on 11g appserver....when user logins in correctly after logging in wrong the LCount is not being reset

Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger