2 Replies Latest reply: Dec 3, 2012 12:48 PM by marlborocat RSS

    why the user account is locked, not locked(timed) in 11g

    marlborocat
      when i do the following in 11g database, the account is only LOCKED, not LOCKED(TIMED) as 10g. i have some other 11g database also showing LOCKED(TIMED).

      I want the status to show LOCKED(TIMED) when user typed wrong password for a few times. and i want the status to show "LOCKED" when DBA manually lock the account. is there somewhere we can control this? or this is a 11g new thing?

      create user test123 identified by test123;
      grant create session to test123;
      create profile test_profile limit FAILED_LOGIN_ATTEMPTS 1;
      alter user test123 profile test_profile;
      connect test123/23l2l2
      connect test123/23l2l2
      connect test123/23l2l2
      connect test123/23l2l2
      conn / as sysdba
      select username, account_status from dba_users where username = 'TEST123';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      TEST123 LOCKED
        • 1. Re: why the user account is locked, not locked(timed) in 11g
          vlethakula
          check below (password_lock_time you need to set)


          create profile TESTAPP limit failed_login_attempts 3 PASSWORD_LOCK_TIME 3; ------------>created profile

          alter user testprofile default profile testapp;

          sqlplus testprofile/test

          SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 11:16:29 2012

          Copyright (c) 1982, 2011, Oracle. All rights reserved.

          ERROR:
          ORA-01017: invalid username/password; logon denied


          Enter user-name: testprofile
          Enter password:
          ERROR:
          ORA-28000: the account is locked ---------------------->3 invalid attempts


          select username,account_status from dba_users where username='TESTPROFILE';

          USERNAME ACCOUNT_STATUS
          ------------------------------ --------------------------------
          TESTPROFILE LOCKED(TIMED) -------------------------------->LOCKED(TIMED)


          alter user TESTPROFILE account unlock;

          alter user testprofile account lock; ---------------------------->Manual lock


          select username,account_status from dba_users where username='TESTPROFILE';

          USERNAME ACCOUNT_STATUS
          ------------------------------ --------------------------------
          TESTPROFILE LOCKED
          • 2. Re: why the user account is locked, not locked(timed) in 11g
            marlborocat
            I figured this out. PASSWORD_LOCK_TIME has to be set to show locked(timed).