Skip to Main Content
  • Questions
  • User expired status even profile life password is set to unlimited

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Daniel.

Asked: April 07, 2017 - 6:49 pm UTC

Last updated: April 10, 2017 - 9:48 am UTC

Version: 11.1.0.7.0 64bit

Viewed 1000+ times

You Asked

Hi Masters!

I created some profiles a week ago and add some users into that profile, for some reason one of the users got today its account expired but the profile password life time is set to unlimited, do you know what happened?

Profile:

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
PASSWORD_LIFE_TIME PASSWORD UNLIMITED

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD 1
PASSWORD_GRACE_TIME PASSWORD 7


Best regards,
Daniel

and Chris said...

By any chance had these users entered the grace period before you changed their profile?

Once an account is in the grace period, to get out of it you have to reset the password. Just changing the profile isn't enough.

Your new profile has a 7 day grace period. So a week later these passwords expire!

create profile short_pwd limit 
  PASSWORD_LIFE_TIME 1/1440 PASSWORD_GRACE_TIME 1/1440;
create profile unlimited_pwd limit 
  PASSWORD_LIFE_TIME unlimited PASSWORD_GRACE_TIME 1/1440;

create user usr identified by usr profile short_pwd;
grant create session to usr;

select account_status, expiry_date, sysdate from dba_users
where  username = 'USR';

ACCOUNT_STATUS  EXPIRY_DATE           SYSDATE               
OPEN            10-APR-2017 02:44:09  10-APR-2017 02:43:10

exec dbms_lock.sleep(61);

conn usr/usr

conn chris/chris

alter user usr profile unlimited_pwd;

select account_status, to_char(expiry_date, 'HH24:MI:SS') exp, to_char(sysdate, 'HH24:MI:SS') dt
from dba_users
where  username = 'USR';

ACCOUNT_STATUS  EXP       DT        
EXPIRED(GRACE)  02:45:13  02:44:22  

exec dbms_lock.sleep(61);

conn usr/usr

ORA-28001: the password has expired

conn chris/chris

select account_status, to_char(expiry_date, 'HH24:MI:SS') exp, to_char(sysdate, 'HH24:MI:SS') dt
from   dba_users
where  username = 'USR';

ACCOUNT_STATUS  EXP       DT        
EXPIRED         02:45:13  02:45:57 




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_LOCK

More on PL/SQL routine DBMS_LOCK here