1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Profile and wrong password issue

Discussion in 'Server Administration and Options' started by Arju, Oct 17, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Whenever we create a user that user is automatically assigned to default profile. Starting from oracle 10.2g the limit for FAILED_LOGIN_ATTEMPTS for the DEFAULT profile is 10. Prior to release 10.2g, the default limit for this resource was UNLIMITED.
    Let's see for my oracle version,
    Code (Text):
    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    Code (Text):
    SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';

    PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
    ------------------------------ -------------------------------- -------- ----------------------------------------
    DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
    MONITORING_PROFILE             FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
     
    We see this limit is 10 which indicates if an user consecutively attempts to logon to database more than 10 times with wrong password then his account will be locked.

    Let's start by creating user test_profile with password t.
    Code (Text):
    SQL> grant create session to test_profile identified by t;

    Grant succeeded.
    Connect to database with right password.
    Code (Text):
    SQL> conn test_profile/t
    Connected.
    Now let's try to connect to database with wrong password for 10 times.
    Code (Text):
    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    Warning: You are no longer connected to ORACLE.
    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied


    SQL> conn test_profile/te
    ERROR:
    ORA-01017: invalid username/password; logon denied

    SQL> conn test_profile/te
    ERROR:
    ORA-28000: the account is locked
    So see at time 11th number wrong entry automatically lock the account and hence the user will not be able to logon to database. This might increase the account secure in order to avoid brute force attack to database. However you may wish to change this default value to high number or unlimited. In that case do following,
    To unlock,
    Code (Text):
    SQL> alter user test_profile account unlock;

    User altered.
    To change lock limit of retry to 1000 times,
    Code (Text):
    SQL> alter profile default limit failed_login_attempts 1000;

    Profile altered.
    To make it unlimited,
    Code (Text):
    SQL> alter profile default limit failed_login_attempts unlimited;

    Profile altered.