SYS User Account Password Expiry Date Not Updated After Password Change

Due to the bug 28538439 the sys user password expiry date does not get updated on the dba_users view after a password change. This could be reproduced as shown below (DB version 19.13).

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username = 'SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

Change the sys user password

alter user sys identified by hello_1234;

User altered.

The expiry date for sys user is not updated.

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

The bug first started on 12.2 (refere 2518310.1 and 2482400.1) and patch for bug 28538439 does exists. For 19c this is already included in DBRU (checked on 19.10, 19.12 and 19.13)

$ORACLE_HOME/OPatch/opatch lsinv | grep 28538439
     28534475, 28534493, 28535127, 28537481, 28538439, 28541606, 28542455

Even with the patch inplace the hidden parameter "_enable_ptime_update_for_sys" must be set to true. By default this parameter has the value false. Use query here to check hidden parameter values.
After the parameter is set to true restart the database

alter system set "_enable_ptime_update_for_sys"=true scope=spfile;

After the restart the expiry column would still show the old expiry date even though password was reset.

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

Resetting the password again will show the updated value.

 alter user sys identified by hello_1234

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 01-MAY-22 11-MAY-21 DEFAULT

It seems that value on expiry date column has no influence on the usage if password is set before its life time ends (this was later confirmed by MOS). Below is an output from a DBCM VM DB created with LVM as the storage option.

select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';
NAME                           VALUE      DEFLT     TYPE                 DESCRIPTION
------------------------------ ---------- --------- -------------------- --------------------------------------------------
_enable_ptime_update_for_sys   FALSE      TRUE      boolean              Enable/Disable update of user$ for sys


USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 16-JUN-19 17-APR-19 DEFAULT

Hidden parameter is not set and sys password is already expired. Even though database was created recently.

SQL> select created from v$database;

CREATED
---------
02-NOV-21

There's no issue in connecting to the DB as sys.

sqlplus  sys@TESTPW_LHR19D as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 2 13:07:45 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

Expiring the password manually seem to update the expiry date value.

SQL> alter user sys password expire;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        EXPIRED              02-NOV-21 17-APR-19 DEFAULT

But setting the password again seem to set the expiry date to (created date + PASSWORD_LIFE_TIME) which could be again in the past depending on the created date.

SQL> SELECT RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ------------------------------
PASSWORD_LIFE_TIME               60


SQL>  alter user sys identified by hello_1234;

User altered.

SQL>  select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 16-JUN-19 17-APR-19 DEFAULT

SQL> select EXPIRY_DATE-CREATED from dba_users where username='SYS';

EXPIRY_DATE-CREATED
-------------------
                 60

Useful MOS Docs
After Changing SYS Password, DBA_USERS.EXPIRY_DATE Not Updated For SYS User [ID 2518310.1]
Sys Password Reset Is Not Reflecting In Sys.user$ PASSWORD_CHANGE_DATE [ID 2482400.1]

Gradual Database Password Rollover and UCP

Oracle first introduced gradual password rollover as a new feature in 21c. But with RU 19.12 this feature is also available on 19c.
The gradual password rollover feature introduced a new parameter for user profiles called "PASSWORD_ROLLOVER_TIME". When this is set, a user could have two passwords for authentication for the duration specified by the value set for PASSWORD_ROLLOVER_TIME. Once the rollover period ends only the new password is valid.
This could be demo as below using 19.12. A new profile is craeted with password rollver time set to 1.

SQL> create profile test limit PASSWORD_ROLLOVER_TIME 1;

Profile created.

A user is assigned the new profile

SQL> alter user asangaro profile test;

User altered.

The current status of the account is open.

SQL> select username,account_status from dba_users where username='ASANGARO';

USERNAME   ACCOUNT_STATUS
---------- ---------------
ASANGARO   OPEN

Change the password for the user and check the account status

SQL> alter user asangaro identified by hello123##;

User altered.

SQL>  select username,account_status from dba_users where username='ASANGARO';

USERNAME   ACCOUNT_STATUS
---------- -------------------
ASANGARO   OPEN & IN ROLLOVER

Account status is now open but in the rollover period. During this period user can use both the old password and new password set above.
The rollover period could be manually ended by using the following command.

SQL> alter user asangaro expire password rollover period;

User altered.

SQL> select username,account_status from dba_users where username='ASANGARO';

USERNAME   ACCOUNT_STATUS
---------- -----------------
ASANGARO   OPEN

Main thign to remember is the below

Oracle Database does not send any special messages to the database clients that indicate that the user account is in the password rollover period. This design avoids any errors from applications that may not be equipped to handle error and warning messages when a user logs in.

UCP will not automatically update itself with the new password. However, its behaviour may seems it has updated the password and working fine.



Universal Connection Pool (UCP) does the authentication only during the intial creation of the connection. For example if the inital size of the connection pool is set to 10, then those 10 connections will be authenticated using the password provided. There's no authentication happening when these connections are checked out the pool later on. This could be easily tested and verified (no need for PASSWORD_ROLLOVER_TIME configuration) by creating a UCP with initial set of connections and then changing the password. The connection created would still work and would be able to run DB queries. Because of this fact, even after rollover period has ended UCP may continue to function normal unless additoinal connections are created.

If more connections are needed than the intial amount, then authentication take place when those are created in the connection pool. Going by the above example, if password was changed after initializng the pool with 10 connections then during the creation of the 11th connection an ORA-1017 will be thrown. So after the rollover period has ended, if the UCP needed more connection than it had during the rollover period this would result in ORA-1017.

There's no way to update the UCP with the new password without recreating it. The property check interval only concern itself with resizing the pool. Not with password changes. Even if there is a UCP manger, methods such as purge, recycle, refresh would not update the password in the pool.

Only possible solution it seems is to destroy and recreate the pool. During this period the source where password is read must have been updated with the new password.

Alternatively, application could have a rolling restart with the new password.

Connect Through - Connect Without Knowing the Password

At times it may be necessary to grant access to a database schema but not necessarily give the password for the login. Granting connect through comes useful in such cases. Below example demonstrate the usage. Assume user appadmin is the main application user and appguest user whom access to appadmin schema will be granted.

SQL> create user appadmin identified by appadmin;
User created.

SQL> create user appguest identified by appguest;
User created.

SQL> grant connect to appadmin;
Grant succeeded.

SQL> grant connect to appguest;
Grant succeeded.

Grant appguest connect through as appadmin

SQL> alter user appadmin grant connect through appguest;
User altered.

With this command user appguest would be able to connect to appadmin without specifiying appadmin password.

SQL>  conn appguest[appadmin]/appguest
Connected.
SQL> show user
USER is "APPADMIN"

Password specified above is the password of the appguest user and the connected schema is the appadmin schema.




For JDBC proxy connections refer the following metalink notes.
How to use Proxy Users to connect to a Database using Credential Mapping or User Injection. [1529978.1]
How to Ensure USER Session Variable Accurately Reflects "real" User When Using Proxy Authentication with JDBC [431202.1]