change Oracle user account status from EXPIRE GRACE to OPEN

Question

After getting the message Your password will be expired with in 7 days  I changed the password expire days of the default profile to UNLIMITED  But the account status of some users are still remaining in EXPIRE GRACE     Any way to change the Oracle user account status from  EXPIRE GRACE  to OPEN without resetting the password

User · Answer

Step-1 Need to find user details by using below query

SQL> select username, account_status from dba_users where username='BOB';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BOB                            EXPIRED

Step-2 Get users password by using below query.

SQL>SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='BOB';

ALTER USER BOB IDENTIFIED BY VALUES 'S:9BDD17811E21EFEDFB1403AAB1DD86AB481E;T:602E36430C0D8DF7E1E453;2F9933095143F432';

Step -3 Run Above alter query

SQL> ALTER USER BOB IDENTIFIED BY VALUES 'S:9BDD17811E21EFEDFB1403AAB1DD86AB481E;T:602E36430C0D8DF7E1E453;2F9933095143F432';
User altered.

Step-4 :Check users account status

SQL> select username, account_status from dba_users where username='BOB';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BOB                            OPEN

User · Answer

Compilation from jonearles  answer  http   kishantha blogspot com 2010 03 oracle-enterprise-manager-console html and http   blog flimatech com 2011 07 17 changing-oracle-password-in-11g-using-alter-user-identified-by-values   Oracle 11g    To stop this happening in the future do the following    Login to sqlplus as sysdba -  sqlplus   as sysdba  Execute - ALTER PROFILE DEFAULT LIMIT FAILED LOGIN ATTEMPTS UNLIMITED PASSWORD LIFE TIME UNLIMITED    To reset users  status  run the query   select  alter user      su name      identified by values                 spare4              su password          from sys user  su  join dba users du on ACCOUNT STATUS like  EXPIRED   and su name   du username    and execute some or all of the result set

User · Answer

No  you cannot directly change an account status from EXPIRE GRACE  to OPEN without resetting the password   The documentation says      If you cause a database user s password to expire with PASSWORD   EXPIRE  then the user  or the DBA  must change the password before   attempting to log into the database following the expiration      However  you can indirectly change the status to OPEN by resetting the user s password hash to the existing value   Unfortunately  setting the password hash to itself has the following complications  and almost every other solution misses at least one of these issues    Different versions of Oracle use different types of hashes  The user s profile may prevent re-using passwords  Profile limits can be changed  but we have to change the values back at the end  Profile values are not trivial because if the value is DEFAULT  that is a pointer to the DEFAULT profile s value   We may need to recursively check the profile    The following  ridiculously large PL SQL block  should handle all of those cases   It should reset any account to OPEN  with the same password hash  regardless of Oracle version or profile settings   And the profile will be changed back to the original limits   --Purpose  Change a user from EXPIRED to OPEN by setting a user s password to the same value  --This PL SQL block requires elevated privileges and should be run as SYS  --This task is difficult because we need to temporarily change profiles to avoid --  errors like  ORA-28007  the password cannot be reused   -- --How to use  Run as SYS in SQL Plus and enter the username when prompted  --  If using another IDE  manually replace the variable two lines below  declare     v username varchar2 128     trim upper   amp USERNAME         --Do not change anything below this line      v profile                 varchar2 128       v old password reuse time varchar2 128       v uses default for time   varchar2 3       v old password reuse max  varchar2 128       v uses default for max    varchar2 3       v alter user sql          varchar2 4000   begin     --Get user s profile information      -- This is tricky because there could be an indirection to the DEFAULT profile      select         profile          case when user password reuse time    DEFAULT  then default password reuse time else user password reuse time end password reuse time          case when user password reuse time    DEFAULT  then  Yes  else  No  end uses default for time          case when user password reuse max     DEFAULT  then default password reuse max  else user password reuse max  end password reuse max          case when user password reuse max     DEFAULT  then  Yes  else  No  end uses default for max     into v profile  v old password reuse time  v uses default for time  v old password reuse max  v uses default for max     from               --User s profile information          select             dba profiles profile              max case when resource name    PASSWORD REUSE TIME  then limit else null end  user password reuse time              max case when resource name    PASSWORD REUSE MAX  then limit else null end  user password reuse max         from dba profiles         join dba users             on dba profiles profile   dba users profile         where username   v username         group by dba profiles profile       users profile     cross join               --Default profile information          select             max case when resource name    PASSWORD REUSE TIME  then limit else null end  default password reuse time              max case when resource name    PASSWORD REUSE MAX  then limit else null end  default password reuse max         from dba profiles         where profile    DEFAULT        default profile       --Get user s password information      select          alter user    name    identified by values               spare4    case when password is not null then     else null end    password                     into v alter user sql     from sys user      where name   v username       --Change profile limits  if necessary      if v old password reuse time  lt  gt   UNLIMITED  then         execute immediate  alter profile    v profile    limit password reuse time unlimited       end if       if v old password reuse max  lt  gt   UNLIMITED  then         execute immediate  alter profile    v profile    limit password reuse max unlimited       end if       --Change the user s password      execute immediate v alter user sql       --Change the profile limits back  if necessary      if v old password reuse time  lt  gt   UNLIMITED  then         if v uses default for time    Yes  then             execute immediate  alter profile    v profile    limit password reuse time default           else             execute immediate  alter profile    v profile    limit password reuse time    v old password reuse time          end if      end if       if v old password reuse max  lt  gt   UNLIMITED  then         if v uses default for max    Yes  then             execute immediate  alter profile    v profile    limit password reuse max default           else             execute immediate  alter profile    v profile    limit password reuse max    v old password reuse max          end if      end if  end

User · Answer

In case you know the password of that user  or you would like to guess it  do the following    connect user password   If this command connects successufully  you will see the message  connected   otherwise you d see an error message  If you are then successufull logging  that means that you know the password  In that case  just do    alter user NAME OF THE USER identified by OLD PASSWORD    and this will reset the password to the same password as before and also reset the account status for that user

User · Answer

set long 9999999 set lin 400 select DBMS METADATA GET DDL  USER   YOUR USER NAME   from dual    This will output something like this   SQL gt  select DBMS METADATA GET DDL  USER   WILIAM   from dual   DBMS METADATA GET DDL  USER   WILIAM   --------------------------------------------------------------------------------     CREATE USER  WILIAM  IDENTIFIED BY VALUES  S 6680C1468F5F3B36B726CE7620F FD9657F0E0E49AE56AAACE847BA368CEB 120F24A4C2554B4F        DEFAULT TABLESPACE  USER        TEMPORARY TABLESPACE  TEMP        PASSWORD EXPIRE   Just use the first piece of that with alter user instead   ALTER USER  WILIAM  IDENTIFIED BY VALUES  S 6680C1468F5F3B36B726CE7620F FD9657F0E0E49AE56AAACE847BA368CEB 120F24A4C2554B4F     This will put the account back in to OPEN status without changing the password  as long as you cut and paste correctly the hash value from the output of DBMS METADATA GET DDL  and you don t even need to know what the password is

[oracle] change Oracle user account status from EXPIRE(GRACE) to OPEN

Examples related to oracle