This discussion is archived
2 Replies Latest reply: Dec 3, 2012 10:48 AM by marlborocat RSS

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

marlborocat Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I figured this out. PASSWORD_LOCK_TIME has to be set to show locked(timed).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points