8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

Gradual Database Password Rollover Time (PASSWORD_ROLLOVER_TIME) in Oracle Database 19c and 21c

Changing database user passwords can be very disruptive for applications connecting to the database. Typically we have to schedule downtime so we can change the database user password, then work through all the application servers, changing their connection details.

From Oracle 21c onward we can define a gradual password rollover time, which allows us to connect using both the old and the new passwords during the gradual rollover time period. This means we can alter the database user password, and work through any application connections without having a hard cut-off for new connections. This feature was backported to Oracle 19c in the 19.12 release update.

Related articles.

Create a Test User

We connect to a privileged user and create a test user.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;

create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

Create a Gradual Database Password Rollover Profile

We create a new profile, setting the PASSWORD_ROLLOVER_TIME limit to 1 day, and associate the test user with this profile.

-- drop profile pw_rollover_time_prof;

create profile pw_rollover_time_prof limit
  password_rollover_time 1;

alter user testuser1 profile pw_rollover_time_prof;

We can also alter the PASSWORD_ROLLOVER_TIME limit of an existing profile using the ALTER PROFILE command. In the following example we set it to 1.5 days.

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

The minimum PASSWORD_ROLLOVER_TIME value is one hour (1/24), and the maximum time is 60 days.

Test It

We connect to the test user and reset the password.

conn testuser1/testuser1@//localhost:1521/pdb1

alter user testuser1 identified by newpasswd1;

For the next 1.5 days we can connect using both the old and new passwords.

conn testuser1/testuser1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

Views

The DBA_USERS view includes a PASSWORD_CHANGE_DATE column, and the ACCOUNT_STATUS column tells us if the user is currently in rollover. The query below shows us the status of the TESTUSER1 account.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

select account_status,
       to_char(password_change_date, 'dd-mon-yyyy hh24:mi:ss') as password_change_date
from   dba_users
where  username = 'TESTUSER1';

ACCOUNT_STATUS                   PASSWORD_CHANGE_DATE
-------------------------------- -----------------------------
OPEN & IN ROLLOVER               15-oct-2021 09:22:43

SQL>

The value of the PASSWORD_ROLLOVER_TIME limit is available from the DBA_PROFILES view, like any other profile limit.

column resource_name format a25
column limit format a10

select resource_name,
       limit
from   dba_profiles
where  profile = 'PW_ROLLOVER_TIME_PROF'
and    resource_name = 'PASSWORD_ROLLOVER_TIME';

RESOURCE_NAME             LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME    1.5

SQL>

Disabling Gradual Database Password Rollover

Setting the PASSWORD_ROLLOVER_TIME limit to 0 disables gradual password rollover. In the following example we set the limit to zero, and fail to connect to the test user with the old password.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 0;

conn testuser1/testuser1@//localhost:1521/pdb1
  USER          = testuser1
  URL           = jdbc:oracle:oci8:@//localhost:1521/pdb1
  Error Message = no ocijdbc21 in java.library.path
  USER          = testuser1
  URL           = jdbc:oracle:thin:@//localhost:1521/pdb1
  Error Message = ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

A Word of Warning

Imagine the old password were compromised and we needed to disable the current gradual password rollover, so the old password could no longer be used, but we want to enable gradual password rollover for future password changes. We must make sure there is at least one connection attempt between disabling and reenabling gradual password rollover, or we may not get the password reset we desire.

We reset the rollover time and change the password again.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

conn testuser1/newpasswd1@//localhost:1521/pdb1

alter user testuser1 identified by newpasswd2;

We can connect with either of the new passwords again.

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd2@//localhost:1521/pdb1
Connected.
SQL>

This time set the PASSWORD_ROLLOVER_TIME limit to 0, then immediately back to 1.5 days, with no connection attempt between them.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 0;

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

Provided there have been no new connection attempts between the resets, using either the old or new passwords, we can still connect with both passwords, so we have not really disabled and reenabled gradual password rollover.

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd2@//localhost:1521/pdb1
Connected.
SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.