关于用户密码过期后修改密码仍无法登录的问题解决

关于用户密码过期后修改密码仍无法登录的问题解决

登录oracle数据库，登录后提示密码过期，修改新密码后仍然无法登录，提示ORA-28041: Authentication protocol internal error ORACLE。

解决办法

1、登录sys用户，检查DBA_USERS中的USERNAME和account_status的值。
能够正常登录的用户，account_status的值是open。我要登录的用户当时的状态是EXPIRED。
2、将用户的状态修改为open。
参考链接: change Oracle user account status from EXPIRE(GRACE) to OPEN。
使用下面的代码即可直接修改account_status。

//使用的时候根据实际情况，修改&USERNAME的值即可
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
    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;
/

执行后可以再次查看account_status，现在变成open了。再次登录既可登陆成功。