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

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 pass开发者_开发百科word?

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:

1. Different versions of Oracle use different types of hashes.
2. The user's profile may prevent re-using passwords.
3. Profile limits can be changed, but we have to change the values back at the end.
4. 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('&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 <> 'UNLIMITED' then
        execute immediate 'alter profile '||v_profile||' limit password_reuse_time unlimited';
    end if;

    if v_old_password_reuse_max <> '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 <> '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 <> '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;
/

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.

set long 9999999
set lin 400
select DBMS_METADATA.GET_DDL('USER','YOUR_USER_NAME') from dual;

This will output something like this:

SQL> 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.

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.

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

PART I (FIND IF USER EXISTS)

--could check with a system type account e.g.SYS

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

2. SQL> select username, account_status from dba_users where username like 'BOB%';

3. SQL> select username, account_status from dba_users where like '%BOB%';

PART II Changing the account properties

SQL> ALTER user [username] account UNLOCK; --unlocks the account it it was locked

SQL> Alter user [username] IDENTIFIED BY "password"; --would change the password for user

SQL> ALTER user [username] account UNLOCK; --unlocks the account it it was locked

SQL> Alter user [username] IDENTIFIED BY "password"; --would change the password for user

This one is working for me.