patternMinor
Oracle11g password expiry issue
Viewed 0 times
issueexpirypasswordoracle11g
Problem
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.
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.
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.
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 1but 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.
Solution
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
The following method will set a user's account status to EXPIRED(GRACE):
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):
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'Code Snippets
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;select ACCOUNT_STATUS
from DBA_USERS
where USERNAME = 'EXPTEST'Context
StackExchange Database Administrators Q#10792, answer score: 5
Revisions (0)
No revisions yet.