How do I turn off Oracle password expiration

Question

I m using Oracle for development   The password for a bootstrap account that I always use to rebuild my database has expired   How do I turn off password expiration for this user  and all other users  permanently   I m using Oracle 11g  which has passwords expire by default

User · Answer

For development you can disable password policy if no other profile was set  i e  disable password expiration in default one    ALTER PROFILE  DEFAULT  LIMIT PASSWORD VERIFY FUNCTION NULL    Then  reset password and unlock user account  It should never expire again   alter user user name identified by new password account unlock

User · Answer

I believe that the password expiration behavior  by default  is to never expire   However  you could set up a profile for your dev user set and set the PASSWORD LIFE TIME  See the orafaq for more details   You can see here for an example of one person s perspective and usage

User · Answer

To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using   select profile from DBA USERS where username     lt username gt      Then you can change the limit to never expire using   alter profile  lt profile name gt  limit password life time UNLIMITED    If you want to previously check the limit you may use   select resource name limit from dba profiles where profile   lt profile name gt

User · Answer

For those who are using Oracle 12 1 0 for development purposes  I found that the above methods would have no effect on the db user   quot system quot   because the account status would remain in the expired-grace period  The easiest solution was for me to use SQL Developer  within SQL Developer  I had to go to   View   DBA   Security and then Users   System  and then on the right side   Actions   Expire pw and then  Actions   Edit and I could untick the option for expired  This cleared the account status  it shows OPEN again  and the SQL Developer is no longer showing the ORA-28002 message

User · Answer

I will suggest its not a good idea to turn off the password expiration as it can lead to possible threats to  confidentiality  integrity and availability of data   However if you want so   If you have proper access use following SQL      SELECT username  account status FROM dba users    This should give you result like this      USERNAME                       ACCOUNT STATUS ------------------------------ -----------------  SYSTEM                         OPEN SYS                            OPEN SDMADM                         OPEN MARKETPLACE                    OPEN SCHEMAOWNER                    OPEN ANONYMOUS                      OPEN SCHEMAOWNER2                   OPEN SDMADM2                        OPEN SCHEMAOWNER1                   OPEN SDMADM1                        OPEN HR                             EXPIRED GRACE   USERNAME                       ACCOUNT STATUS ------------------------------ -----------------  APEX PUBLIC USER               LOCKED APEX 040000                    LOCKED FLOWS FILES                    LOCKED XS NULL                        EXPIRED  amp  LOCKED OUTLN                          EXPIRED  amp  LOCKED XDB                            EXPIRED  amp  LOCKED CTXSYS                         EXPIRED  amp  LOCKED MDSYS                          EXPIRED  amp  LOCKED   Now you can use Pedro Carri  o answer https   stackoverflow com a 6777079 2432468

User · Answer

As the other answers state  changing the user s profile  e g  the  DEFAULT  profile  appropriately will lead to passwords  that once set  will never expire   However  as one commenter points out  passwords set under the profile s old values may already be expired  and  if after the profile s specified grace period  the account locked    The solution for expired passwords with locked accounts  as provided in an answering comment  is to use one version of the ALTER USER command   ALTER USER xyz user ACCOUNT UNLOCK    However the unlock command only works for accounts where the account is actually locked  but not for those accounts that are in the grace period  i e  where the password is expired but the account is not yet locked  For these accounts the password must be reset with another version of the ALTER USER command   ALTER USER xyz user IDENTIFIED BY new password    Below is a little SQL Plus script that a privileged user  e g  user  SYS   can use to reset a user s password to the current existing hashed value stored in the database   EDIT  Older versions of Oracle store the password or password-hash in the pword column  newer versions of Oracle store the password-hash in the spare4 column  Script below changed to collect the pword and spare4 columns  but to use the spare4 column to reset the user s account  modify as needed   REM Tell SQL Plus to show before and after versions of variable substitutions  SET VERIFY ON SHOW VERIFY  REM Tell SQL Plus to use the ampersand   amp   to indicate variables in substitution expansion  SET DEFINE   amp   SHOW DEFINE  REM Specify in a SQL Plus variable the account to  reset   REM Note that user names are case sensitive in recent versions of Oracle  REM DEFINE USER NAME    xyz user   REM Show the status of the account before reset  SELECT   ACCOUNT STATUS    TO CHAR LOCK DATE   YYYY-MM-DD HH24 MI SS   AS LOCK DATE    TO CHAR EXPIRY DATE   YYYY-MM-DD HH24 MI SS   AS EXPIRY DATE FROM   DBA USERS WHERE   USERNAME     amp USER NAME    REM Create SQL Plus variable to hold the existing values of the password and spare4 columns  DEFINE OLD SPARE4      DEFINE OLD PASSWORD       REM Tell SQL Plus where to store the values to be selected with SQL  REM Note that the password hash value is stored in spare4 column in recent versions of Oracle  REM   and in the password column in older versions of Oracle  COLUMN SPARE4HASH NEW VALUE OLD SPARE4 COLUMN PWORDHASH NEW VALUE OLD PASSWORD  REM Select the old spare4 and password columns as delimited strings  SELECT            SPARE4         AS SPARE4HASH            PASSWORD         AS PWORDHASH FROM    SYS USER   WHERE    NAME     amp USER NAME    REM Show the contents of the SQL Plus variables DEFINE OLD SPARE4 DEFINE OLD PASSWORD  REM Reset the password - Older versions of Oracle  e g  Oracle 10g and older   REM ALTER USER  amp USER NAME IDENTIFIED BY VALUES  amp OLD PASSWORD   REM Reset the password - Newer versions of Oracle  e g  Oracle 11g and newer   ALTER USER  amp USER NAME IDENTIFIED BY VALUES  amp OLD SPARE4   REM Show the status of the account after reset SELECT   ACCOUNT STATUS    TO CHAR LOCK DATE   YYYY-MM-DD HH24 MI SS   AS LOCK DATE    TO CHAR EXPIRY DATE   YYYY-MM-DD HH24 MI SS   AS EXPIRY DATE FROM   DBA USERS WHERE   USERNAME     amp USER NAME

[oracle] How do I turn off Oracle password expiration?

Examples related to oracle

Examples related to security