Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

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.