将 Oracle 用户帐户状态从 EXPIRE(GRACE) 更改为 OPEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5521766/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
change Oracle user account status from EXPIRE(GRACE) to OPEN
提问by user688694
After getting the message Your password will be expired with in 7 days, I changed the password expire days of the defaultprofile to UNLIMITED. But the account status of some users are still remaining in EXPIRE(GRACE).
收到消息后Your password will be expired with in 7 days，我将default配置文件的密码过期天数更改为UNLIMITED. 但部分用户的账户状态仍停留在EXPIRE(GRACE).
Any way to change the Oracle user account status from EXPIRE(GRACE)to OPENwithout resetting the password?
没有办法更改从Oracle用户帐户状态 EXPIRE(GRACE)到OPEN无重设密码？
回答by Jon Heller
No, you cannot directlychange an account status from EXPIRE(GRACE) to OPEN without resetting the password.
不可以，您不能在不重置密码的情况下直接将帐户状态从 EXPIRE(GRACE) 更改为 OPEN。
The documentationsays:
该文件说：
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.
如果您使用 PASSWORD EXPIRE 导致数据库用户的密码过期，则用户（或 DBA）必须在过期后尝试登录数据库之前更改密码。
However, you can indirectlychange 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:
但是，您可以通过将用户的密码哈希重置为现有值来间接将状态更改为 OPEN。不幸的是，将密码哈希设置为自身具有以下复杂性，并且几乎所有其他解决方案都至少遗漏了以下问题之一：
- 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 theDEFAULTprofile's value. We may need to recursively check the profile.
- 不同版本的 Oracle 使用不同类型的散列。
- 用户的配置文件可能会阻止重复使用密码。
- 配置文件限制可以更改，但我们必须在最后更改这些值。
- 配置文件值并非微不足道，因为如果值为
DEFAULT，则它是指向DEFAULT配置文件值的指针。我们可能需要递归检查配置文件。
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.
下面这个大得离谱的 PL/SQL 块应该可以处理所有这些情况。无论 Oracle 版本或配置文件设置如何，它都应该使用相同的密码哈希将任何帐户重置为 OPEN。并且配置文件将更改回原始限制。
--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;
/
回答by vic123
Compilation from jonearles' answer, http://kishantha.blogspot.com/2010/03/oracle-enterprise-manager-console.htmland http://blog.flimatech.com/2011/07/17/changing-oracle-password-in-11g-using-alter-user-identified-by-values/(Oracle 11g):
jonearles 的回答汇编，http ://kishantha.blogspot.com/2010/03/oracle-enterprise-manager-console.html和http://blog.flimatech.com/2011/07/17/changed-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;
- 以 sysdba 身份登录 sqlplus -> sqlplus "/as sysdba"
- 执行 ->
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.
并执行部分或全部结果集。
回答by louigi600
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 一起使用：
ALTER USER "WILIAM" IDENTIFIED BY VALUES 'S:6680C1468F5F3B36B726CE7620F
FD9657F0E0E49AE56AAACE847BA368CEB;120F24A4C2554B4F';
This will put the account back in to OPENstatus 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.
这将在OPEN不更改密码的情况下将帐户恢复到状态（只要您从 的输出中正确剪切和粘贴哈希值DBMS_METADATA.GET_DDL），您甚至不需要知道密码是什么。
回答by Eliandro
In case you know the password of that user, or you would like to guess it, do the following:
如果您知道该用户的密码，或者您想猜测它，请执行以下操作：
connect user/password
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;
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.
这会将密码重置为与以前相同的密码，并且还会重置该用户的 account_status。
回答by Srikant Patra
Step-1 Need to find user details by using below query
步骤 1 需要使用以下查询查找用户详细信息
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.
步骤 2 使用以下查询获取用户密码。
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
步骤-3 在alter 查询上方运行
SQL> ALTER USER BOB IDENTIFIED BY VALUES 'S:9BDD17811E21EFEDFB1403AAB1DD86AB481E;T:602E36430C0D8DF7E1E453;2F9933095143F432';
User altered.
Step-4 :Check users account status
第 4 步：检查用户帐户状态
SQL> select username, account_status from dba_users where username='BOB';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
BOB OPEN
