Oracle11g password expiry issue

Question

I have a user which has a default profile in oracle db. I want to reproduce the following warning ERROR: ORA-28002: the password will expire within X days

In dba_profile I set following values.

FAILED_LOGIN_ATTEMPTS            PASSWORD 10
PASSWORD_LIFE_TIME               PASSWORD 1
PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
PASSWORD_REUSE_MAX               PASSWORD 1
PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
PASSWORD_LOCK_TIME               PASSWORD 1
PASSWORD_GRACE_TIME              PASSWORD 1

but however by using above value i am able to generate ORA-28002 error for system user. but for CUSUSER users it is not giving this error.

When i look into dba_users table it shows following values.

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
CUSUSER                        OPEN
SYSTEM                         EXPIRED(GRACE)

I am not able to update account_status for CUSUSER to EXPIRED(GRACE)

Please any one let me know how would i reproduce this error for CUSUSER.

Answer 1

According to the Oracle documentation here (see heading Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value) the account status is changed from OPEN to EXPIRED(GRACE) if the user is currently logged in when you change PASSWORD_LIFE_TIME to a low value. I guess you were logged in as system when you changed the PASSWORD_LIFE_TIME. Try logging in as CUSUSER and then changing it.

The following method will set a user's account status to EXPIRED(GRACE):

create profile EXPTESTPROF limit
failed_login_attempts             1
password_life_time                1/24/60/60
password_reuse_time               unlimited
password_reuse_max                1
password_verify_function          null
password_lock_time                1
password_grace_time               1;

create user EXPTEST identified by EXPTEST;

alter user EXPTEST profile EXPTESTPROF;

grant "CONNECT" to EXPTEST;

Then login as EXPTEST. You'll get the message that the password will expire within 1 day. If you check the ACCOUNT_STATUS now it should say EXPIRED(GRACE):

select ACCOUNT_STATUS
  from DBA_USERS
 where USERNAME = 'EXPTEST'