如何关闭 Oracle 密码过期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1095871/
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
How do I turn off Oracle password expiration?
提问by Josh Kodroff
I'm using Oracle for development. The password for a bootstrap account that I always use to rebuild my database has expired.
我正在使用 Oracle 进行开发。我总是用来重建我的数据库的引导帐户的密码已过期。
How do I turn off password expiration for this user (and all other users) permanently?
如何永久关闭此用户（和所有其他用户）的密码过期功能？
I'm using Oracle 11g, which has passwords expire by default.
我正在使用 Oracle 11g，它的密码默认过期。
回答by Pedro Carri?o
To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using:
要在 Oracle 中更改某个用户配置文件的密码过期策略，请首先检查用户正在使用哪个配置文件：
select profile from DBA_USERS where username = '<username>';
Then you can change the limit to never expire using:
然后，您可以使用以下方法将限制更改为永不过期：
alter profile <profile_name> limit password_life_time UNLIMITED;
If you want to previously check the limit you may use:
如果您想事先检查限制，您可以使用：
select resource_name,limit from dba_profiles where profile='<profile_name>';
回答by Shimon
For developmentyou can disable password policy if no other profile was set (i.e. disable password expiration in default one):
对于开发，如果没有设置其他配置文件，您可以禁用密码策略（即在默认配置中禁用密码过期）：
ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Then, reset password and unlock user account. It should never expire again:
然后，重置密码并解锁用户帐户。它不应该再次过期：
alter user user_name identified by new_password account unlock;
回答by Kieron Hardy
As the other answers state, changing the user's profile (e.g. the 'DEFAULT' profile) appropriately will lead to passwords, that once set, will never expire.
正如其他答案所述，适当地更改用户的配置文件（例如“默认”配置文件）将导致密码一旦设置就永远不会过期。
However, as one commenter points out, passwords set under the profile's old values may already be expired, and (if after the profile's specified grace period) the account locked.
但是，正如一位评论者指出的那样，在配置文件的旧值下设置的密码可能已经过期，并且（如果在配置文件指定的宽限期之后）帐户被锁定。
The solution for expired passwords with locked accounts (as provided in an answering comment) is to use one version of the ALTER USER command:
具有锁定帐户的过期密码的解决方案（在回答评论中提供）是使用一个版本的 ALTER USER 命令：
ALTER USER xyz_user ACCOUNT UNLOCK;
However the unlock command only works for accounts where the account is actually locked, but not for those accounts that are in the grace period, i.e. where the password is expired but the account is not yet locked. For these accounts the password must be reset with another version of the ALTER USER command:
然而，unlock 命令只对账户实际被锁定的账户有效，而对那些处于宽限期的账户无效，即密码过期但账户尚未锁定的账户。对于这些帐户，必须使用另一个版本的 ALTER USER 命令重置密码：
ALTER USER xyz_user IDENTIFIED BY new_password;
Below is a little SQL*Plus script that a privileged user (e.g. user 'SYS') can use to reset a user's password to the current existing hashed value stored in the database.
下面是一个小的 SQL*Plus 脚本，特权用户（例如用户“SYS”）可以使用它来将用户的密码重置为存储在数据库中的当前现有散列值。
EDIT: Older versions of Oracle store the password or password-hash in the pword column, newer versions of Oracle store the password-hash in the spare4 column. Script below changed to collect the pword and spare4 columns, but to use the spare4 column to reset the user's account; modify as needed.
编辑：旧版本的 Oracle 将密码或密码哈希存储在 pword 列中，新版本的 Oracle 将密码哈希存储在备用 4 列中。下面的脚本改为收集pword和spare4列，但使用spare4列重置用户账户；根据需要修改。
REM Tell SQL*Plus to show before and after versions of variable substitutions.
SET VERIFY ON
SHOW VERIFY
REM Tell SQL*Plus to use the ampersand '&' to indicate variables in substitution/expansion.
SET DEFINE '&'
SHOW DEFINE
REM Specify in a SQL*Plus variable the account to 'reset'.
REM Note that user names are case sensitive in recent versions of Oracle.
REM DEFINE USER_NAME = 'xyz_user'
REM Show the status of the account before reset.
SELECT
ACCOUNT_STATUS,
TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE,
TO_CHAR(EXPIRY_DATE, 'YYYY-MM-DD HH24:MI:SS') AS EXPIRY_DATE
FROM
DBA_USERS
WHERE
USERNAME = '&USER_NAME';
REM Create SQL*Plus variable to hold the existing values of the password and spare4 columns.
DEFINE OLD_SPARE4 = ""
DEFINE OLD_PASSWORD = ""
REM Tell SQL*Plus where to store the values to be selected with SQL.
REM Note that the password hash value is stored in spare4 column in recent versions of Oracle,
REM and in the password column in older versions of Oracle.
COLUMN SPARE4HASH NEW_VALUE OLD_SPARE4
COLUMN PWORDHASH NEW_VALUE OLD_PASSWORD
REM Select the old spare4 and password columns as delimited strings
SELECT
'''' || SPARE4 || '''' AS SPARE4HASH,
'''' || PASSWORD || '''' AS PWORDHASH
FROM
SYS.USER$
WHERE
NAME = '&USER_NAME';
REM Show the contents of the SQL*Plus variables
DEFINE OLD_SPARE4
DEFINE OLD_PASSWORD
REM Reset the password - Older versions of Oracle (e.g. Oracle 10g and older)
REM ALTER USER &USER_NAME IDENTIFIED BY VALUES &OLD_PASSWORD;
REM Reset the password - Newer versions of Oracle (e.g. Oracle 11g and newer)
ALTER USER &USER_NAME IDENTIFIED BY VALUES &OLD_SPARE4;
REM Show the status of the account after reset
SELECT
ACCOUNT_STATUS,
TO_CHAR(LOCK_DATE, 'YYYY-MM-DD HH24:MI:SS') AS LOCK_DATE,
TO_CHAR(EXPIRY_DATE, 'YYYY-MM-DD HH24:MI:SS') AS EXPIRY_DATE
FROM
DBA_USERS
WHERE
USERNAME = '&USER_NAME';
回答by akf
I believe that the password expiration behavior, by default, is to never expire. However, you could set up a profile for your dev user set and set the PASSWORD_LIFE_TIME. See the orafaqfor more details. You can see herefor an example of one person's perspective and usage.
我相信密码过期行为，默认情况下，是永不过期。但是，您可以为您的开发用户集设置配置文件并设置PASSWORD_LIFE_TIME. 有关更多详细信息，请参阅orafaq。您可以在此处查看一个人的观点和用法的示例。
回答by Pawan Kumar
I will suggest its not a good idea to turn off the password expiration as it can lead to possible threats to confidentiality, integrity and availability of data.
我建议关闭密码过期不是一个好主意，因为它可能会对数据的机密性、完整性和可用性造成威胁。
However if you want so.
但是，如果您愿意的话。
If you have proper access use following SQL
如果您有适当的访问权限，请使用以下 SQL
SELECT username, account_status FROM dba_users;
从 dba_users 中选择用户名、帐户状态；
This should give you result like this.
这应该给你这样的结果。
USERNAME ACCOUNT_STATUS
------------------------------ -----------------
SYSTEM OPEN
SYS OPEN
SDMADM OPEN
MARKETPLACE OPEN
SCHEMAOWNER OPEN
ANONYMOUS OPEN
SCHEMAOWNER2 OPEN
SDMADM2 OPEN
SCHEMAOWNER1 OPEN
SDMADM1 OPEN
HR EXPIRED(GRACE)
USERNAME ACCOUNT_STATUS
------------------------------ -----------------
APEX_PUBLIC_USER LOCKED
APEX_040000 LOCKED
FLOWS_FILES LOCKED
XS$NULL EXPIRED & LOCKED
OUTLN EXPIRED & LOCKED
XDB EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
Now you can use Pedro Carri?o answer https://stackoverflow.com/a/6777079/2432468
现在您可以使用 Pedro Carri?o 回答https://stackoverflow.com/a/6777079/2432468
