将 Oracle 用户帐户状态从 EXPIRE(GRACE) 更改为 OPEN [英] change Oracle user account status from EXPIRE(GRACE) to OPEN

问题描述

收到消息Your password will be expired with in 7 days后，我将default配置文件的密码过期天数更改为UNLIMITED.但部分用户的账户状态仍停留在EXPIRE(GRACE).

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

有什么方法可以在不重置密码的情况下将 Oracle 用户帐户状态从 EXPIRE(GRACE) 更改为 OPEN?

Any way to change the Oracle user account status from EXPIRE(GRACE) to OPEN without resetting the password?

推荐答案

不，您不能直接将帐户状态从 EXPIRE(GRACE) 更改为 OPEN，而无需重置密码.

No, you cannot directly change an account status from EXPIRE(GRACE) to OPEN without resetting the password.

文档 说:

如果您使用 PASSWORD 导致数据库用户的密码过期EXPIRE，然后用户(或 DBA)必须在更改密码之前尝试在到期后登录数据库.

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.

<小时>

但是，您可以通过将用户的密码哈希重置为现有值来间接将状态更改为 OPEN.不幸的是，将密码哈希设置为自身具有以下复杂性，并且几乎所有其他解决方案都至少遗漏了其中一个问题:

---

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. 不同版本的 Oracle 使用不同类型的哈希.
2. 用户的个人资料可能会阻止重复使用密码.
3. 配置文件限制可以更改，但我们必须在最后将值更改回来.
4. 配置文件值并非微不足道，因为如果值为 DEFAULT，则它是指向 DEFAULT 配置文件值的指针.我们可能需要递归检查配置文件.

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.

以下非常大的 PL/SQL 块应该可以处理所有这些情况.无论 Oracle 版本或配置文件设置如何，它都应该使用相同的密码哈希将任何帐户重置为 OPEN.并且配置文件将更改回原始限制.

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;
/

这篇关于将 Oracle 用户帐户状态从 EXPIRE(GRACE) 更改为 OPEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！