Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 13 October 2014

ORA-28002: the password will expire within 5 days

Today one user written to me when he is trying to connect the database through toad getting the following error: ORA-28002: the password will expire within 5 days
Initially I have given him solution to set the PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently or simply change the password to avoid it temporarily.
SQL> SELECT profile FROM dba_users WHERE username = 'HRMS';
SQL> SELECT resource_name, LIMIT from dba_profiles where
     profile='DEFAULT';
SQL> SELECT * from  dba_profiles where profile='DEFAULT';
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE
     USERNAME='HRMS';
Then connect with particular accounts and change the password.
SQL> PASSWORD
Changing password for HRMS
Old password:
New password:
Retype new password:
Password changed
Again he has written to me that he has changed every thing to UNLIMITED still getting ORA-28002 and asking can you please tell me why it is automatic getting locked after few minutes.
I am unlocking the account 
SQL> Alter user MCR account unlock
Then trying to connect through Toad connect mcr/password 
Result: ORA-2800 account locked.
SQL> select resource_name,liMit from dba_profiles where profile='DEFAULT';
RESOURCE_NAME                 LIMIT
--------------                -------------
COMPOSITE_LIMIT               UNLIMITED
SESSIONS_PER_USER             UNLIMITED
CPU_PER_SESSION               UNLIMITED
CPU_PER_CALL                  UNLIMITED
LOGICAL_READS_PER_SESSION     UNLIMITED
LOGICAL_READS_PER_CALL        UNLIMITED
IDLE_TIME                     UNLIMITED
CONNECT_TIME                  UNLIMITED
PRIVATE_SGA                   UNLIMITED
FAILED_LOGIN_ATTEMPTS         UNLIMITED
PASSWORD_LIFE_TIME            UNLIMITED
PASSWORD_REUSE_TIME           UNLIMITED
PASSWORD_REUSE_MAX            UNLIMITED
PASSWORD_VERIFY_FUNCTION      NULL
PASSWORD_LOCK_TIME            UNLIMITED
PASSWORD_GRACE_TIME           UNLIMITED
SQL> select username,account_Status,expiry_date from dba_users;
USERNAME    ACCOUNT_STATUS    EXPIRY_D
--------    --------------    --------
MGMT_VIEW   OPEN
SYS         OPEN
SYSTEM      OPEN
SCOTT       OPEN
HRMS        OPEN
MCR         OPEN
BACKUPUSER OPEN
BT_WM_USR   OPEN
DBSNMP      EXPIRED(GRACE)    08/06/13
SYSMAN      EXPIRED(GRACE)    08/06/13
FLOWS_FILES EXPIRED & LOCKED 30/03/10
MDSYS       EXPIRED & LOCKED 30/03/10
ORDSYS      EXPIRED & LOCKED 30/03/10
WMSYS       EXPIRED & LOCKED 30/03/10
APPQOSSYS   EXPIRED & LOCKED 30/03/10
WKSYS       EXPIRED & LOCKED 03/12/12
WK_TEST     EXPIRED & LOCKED 03/12/12
ORDDATA     EXPIRED & LOCKED 30/03/10
CTXSYS      EXPIRED & LOCKED 03/12/12
WKPROXY     EXPIRED & LOCKED 03/12/12
ORDPLUGINS EXPIRED & LOCKED 30/03/10
OWBSYS      EXPIRED & LOCKED 30/03/10
ORACLE_OCM EXPIRED & LOCKED 30/03/10
MDDATA      EXPIRED & LOCKED 30/03/10
DIP         EXPIRED & LOCKED 30/03/10
SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 30/03/10
SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 30/03/10
Solution:
In fact I thought the “PASSWORD_LIFE_TIME” value changed to UNLIMITED would resolve this problem. After searching oracle support, I found this note [ID 162818.1].
The PASSWORD_LIFE_TIME limit of a profile is measured from the last time an account's password was changed or the account creation time if the password has never been changed. These are the dates USER$.PTIME and USER$.CTIME respectively.
SQL> select username, profile,account_status,expiry_date, created
from dba_users
where default_tablespace  not in ('SYSAUX','SYSTEM')
 --and expiry_date is not null
 and account_status not like '%LOCKED' order by expiry_date;

In fact it is NOT measured since from the current time. Therefore, any accounts affected by the changed profile whose last password change time was more than PASSWORD_LIFE_TIME days ago immediately expire and enter their grace period on their next connection, issuing the ORA-28002 warning.
SQL> ALTER PROFILE test LIMIT PASSWORD_LIFE_TIME UNLIMITED;
This change only affects accounts those have not entered their grace period. The accounts that have entered their grace period will have to change their passwords.

9 comments:

  1. Goog, but it correct for ORACLE10g and 11g... for Oracle9i is not funcionaly. How to do it for this versión?.

    Tks.

    ReplyDelete
  2. It's a nice blog with lot of information,thanks for sharing...
    Hi this is babu, I want to share some information for you,If you want to join any professional course visit this website
    oracle training in chennai It's a best place to learn oracle in chennai
    thanks, babu

    ReplyDelete
  3. It's a nice blog with lot of information,thanks for sharing...
    Hi this is babu, I want to share some information for you,If you want to join any professional course visit this website
    oracle training in Chennai It's a best place
    to learn oracle in chennai

    thanks, babu

    ReplyDelete
  4. The information you posted here is useful to make my career better keep updates..If anyone want to become an oracle certified professional reach FITA, which offers real timeOracle Training in Chennai with years of experienced professionals.

    ReplyDelete
  5. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are interested to learn Hadoop Training Chennai visit FITA IT training and placement academy which offer Big Data Training Chennai.

    ReplyDelete
  6. valuable information..
    SAS Institute introduced the SAS Certified Professional Program,training proper understanding of how the SAS software works. Among the five certification programs that SAS Institute has come up with, SAS training can be considered as the entry point into the big data and the data analytics industry.
    SAS online training in hyderabad

    ReplyDelete
  7. your blog is so impressive ,its a great pleasure to read the post in your blog SAP HANA Online Training

    ReplyDelete
  8. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your mobile phone daily.

    Start following our trades NOW and profit up to 270% a day.

    ReplyDelete