Gradual Password Rollover in Oracle Database 19c: Secure Password Changes Without Downtime

Overview

Gradual Password Rollover in Oracle allows both the old and new password to remain valid for a temporary period after a password change, helping applications avoid connection failures during credential updates. It is controlled using the profile parameter PASSWORD_ROLLOVER_TIME, which defines the grace period. This feature was first introduced in Oracle 21c and later backported to Oracle 19c starting from Release Update 19.12. In Oracle 19c, it is available only if the database is running 19.12 or higher. It is mainly used for seamless password rotation in application environments.

Key Concept: PASSWORD_ROLLOVER_TIME

The profile parameter PASSWORD_ROLLOVER_TIME defines the duration (in days, fractional values supported) during which both the old and new passwords remain valid after a password change.

During this window, the user account enters the OPEN & IN ROLLOVER state, which is visible in DBA_USERS.ACCOUNT_STATUS.escription

State	Description
OPEN	Normal state. Only the current password is valid.
OPEN & IN ROLLOVER	Both old and new passwords are accepted. Rollover window is active.
OPEN	Rollover complete. Only the new password is accepted (old invalidated).

NOTE: PASSWORD_ROLLOVER_TIME accepts fractional values (e.g., 1.5 = 36 hours). Setting it to 0 immediately ends the rollover — the old password is rejected at the next login attempt after reconnection.

Step-by-Step Lab Walkthrough  (testuser)

The following steps demonstrate the full lifecycle: profile creation, user setup, password change, rollover observation, and rollover termination.

Create the Rollover Profile

CREATE PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 1;

-- Profile created.

Creates a profile named rollover_profile with a 1-day (24-hour) rollover window.

Create User and Assign Profile

CREATE USER testuser IDENTIFIED BY old_password; 

GRANT CREATE SESSION TO testuser;

ALTER USER testuser PROFILE rollover_profile; 

Creates the testuser, grants login privilege, and assigns the rollover-enabled profile.

Optionally Extend Rollover Window

ALTER PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 1.5;

-- Sets rollover window to 1.5 days (36 hours)

Change the Password (Trigger Rollover)

ALTER USER testuser IDENTIFIED BY new_password; 

-- User altered.

This is the moment the rollover begins. Oracle records the PASSWORD_CHANGE_DATE and puts the account into OPEN & IN ROLLOVER state.

Verify Rollover State

SELECT username, account_status, PASSWORD_CHANGE_DATE

FROM   dba_users

WHERE  username = 'TESTUSER';

Confirm Both Passwords Work

CONN testuser/old_password   -- Connected (old password still valid)

CONN testuser/new_password   -- Connected (new password also valid)

End the Rollover Early (Set Rollover to 0)

CONN / AS SYSDBA

ALTER PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 0;

-- Profile altered.

Setting PASSWORD_ROLLOVER_TIME to 0 terminates the rollover period immediately. The account status remains OPEN & IN ROLLOVER in DBA_USERS until the next login attempt with the old password fails.

Verify Old Password is Now Rejected

CONN testuser/old_password

ERROR:

ORA-01017: invalid username/password; logon denied

CONN locker/new_password   -- Still connects successfully

NOTE: The DBA_USERS row may still show OPEN & IN ROLLOVER briefly after setting PASSWORD_ROLLOVER_TIME=0. The status clears on the next successful login with the new password.

Full Command Summary

Step	SQL Command	Outcome
1	CREATE PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 1;	Profile created with 24h window
2	CREATE USER testuser IDENTIFIED BY old_password;	User created
3	GRANT CREATE SESSION TO testuser;	Login privilege granted
4	ALTER USER locker PROFILE rollover_profile;	Profile assigned to locker
5	ALTER PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 1.5;	Window extended to 36 hours
6	ALTER USER testuser IDENTIFIED BY new_password;	Password changed — rollover begins
7	SELECT username, account_status ... FROM dba_users;	Status: OPEN & IN ROLLOVER
8	CONN testuser/old_password	Connected (old still valid)
9	CONN testuser/new_password	Connected (new also valid)
10	ALTER PROFILE rollover_profile LIMIT PASSWORD_ROLLOVER_TIME 0;	Rollover terminated immediately
11	CONN testuser/old_password	ORA-01017 — old password rejected

Important Notes & Gotchas

Privilege Requirement

Only SYSDBA or a user with the ALTER PROFILE privilege can modify PASSWORD_ROLLOVER_TIME. Attempting it as a normal user results in ORA-01031: insufficient privileges.

 

Status Lag After Setting to 0

DBA_USERS.ACCOUNT_STATUS may still display OPEN & IN ROLLOVER even after setting the rollover to 0. The old password is functionally rejected, but the status column only refreshes on the next successful login.

 

Profile Already Exists

Re-running CREATE PROFILE for an existing profile raises ORA-02379: profile already exists. Use ALTER PROFILE to modify existing profiles.

 

Fractional Days

PASSWORD_ROLLOVER_TIME accepts fractional values. For example, 0.5 = 12 hours, 1.5 = 36 hours. Use these to fine-tune the rollover window for your application deployment windows.

DBA_USERS Reference Columns

SELECT username,

       account_status,

       PASSWORD_CHANGE_DATE,

       profile

FROM   dba_users

WHERE  username = 'TESTUSER';

Production Use Cases

PASSWORD_ROLLOVER_TIME is designed for zero-downtime credential rotation in production environments:

•  Application deployments:  Deploy new app version with new credentials while old connection pools drain gracefully.

•  Secrets manager rotation:  HashiCorp Vault or AWS Secrets Manager can rotate credentials without immediate service impact.

•  Multi-node environments:  Rolling restarts of app servers can complete within the rollover window.

•  GoldenGate / Replication users:  Rotate capture/apply user passwords without halting replication.