Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 17 '12 at 13:50

This question came from our site for professional and enthusiast programmers.

1 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'
share|improve this answer
    
I am trying to do this activity using sys as sysdba –  Prasad S Deshpande Jan 17 '12 at 6:38
    
John I want explicitly reproduce that issue so that I can provide fix in my application. Do you have any idea how would I reproduce it in day. –  Prasad S Deshpande Jan 17 '12 at 11:18
    
I've updated my answer to show how you can do this. –  John Doyle Jan 17 '12 at 12:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.