ChatGPT解决这个技术问题 Extra ChatGPT

How do I turn off Oracle password expiration?

I'm using Oracle for development. The password for a bootstrap account that I always use to rebuild my database has expired.

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.

I think you might be better off asking this on serverfault.com. I'm not going to force it because you did say you're using it for development, and I think there's still a chance someone here will know and/or others here might benefit from this information.
I think I'll do just that. I was debating which site it was more appropriate for, since it's a basic database question and not so much a DBA thing.
Not sure what the dupe policy is for cross-site questions, but here's the link: serverfault.com/questions/37622/…

J
Jason Aller

To alter the password expiry policy for a certain user profile in Oracle first check which profile the user is using:

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

This altered the profile. However, I have users whose passwords are set to expire because the default profile had it as such when they were created. How do I alter these user accounts so the password doesn't expire?
select username,expiry_date,account_status from dba_users; to view the account_status. for those expiring account, you may need to reset the password once for the last time.
alter user aaa account unlock;
For completeness, if you need to change a user to another profile: ALTER USER Bob PROFILE MyNonExpiringProfile;.
no rows selected on the query "select profile from DBA_USERS where username = '';"
S
Shimon

For development you 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;

K
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 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:

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.

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.

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

Thanks for the help. It's so hard to find a relevant answer. Every other answer refers to PASSWORD_LIFE_TIME only.
The SYS.USER$.PASSWORD column will only contain the hash of the NON-case-sensitive (uppercase?) version of the password. In Oracle 11, unless you set the system parameter SEC_CASE_SENSITIVE_LOGON=FALSE there will be be a much longer hash of the case sensitive password in the SYS.USER$.SPARE4 column.
Thank you for nailing the case with the grace period state
a
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 orafaq for more details. You can see here for an example of one person's perspective and usage.


I think in a fresh 11g install (as opposed to an upgrade) with the recommended improved security, the passwords will default to expire after 30 days.
C
Community

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

SELECT username, account_status FROM 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


Whilst I agree it is rash to disable password expiration in Production environments we might want to turn it off in Development or Testing.
P
Peter Petrekanics

For those who are using Oracle 12.1.0 for development purposes: I found that the above methods would have no effect on the db user: "system", because the account_status would remain in the expired-grace period. The easiest solution was for me to use SQL Developer: within SQL Developer, I had to go to: View / DBA / Security and then Users / System and then on the right side: Actions / Expire pw and then: Actions / Edit and I could untick the option for expired. This cleared the account_status, it shows OPEN again, and the SQL Developer is no longer showing the ORA-28002 message.