Gradual Database Password Rollover Time in Oracle Database 19c and 21c | PASSWORD_ROLLOVER_TIME
What is Gradual Database Password Rollover Time?
Introduced in Oracle Database 21c and backported to 19c in version 19.12, the PASSWORD_ROLLOVER_TIME feature provides a smooth, non-disruptive way to change passwords. By setting a rollover period, administrators can enable users to connect with both old and new passwords, facilitating seamless transitions and reducing downtime.
Setting Up Password Rollover in Oracle
To implement gradual password rollover, you need to configure a PROFILE with the PASSWORD_ROLLOVER_TIME attribute. Here’s a step-by-step guide:
-- Connect as a privileged user
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
-- Create a test user
CREATE USER testuser IDENTIFIED BY OldPassword1;
GRANT CONNECT, RESOURCE TO testuser;
-- Define a profile with PASSWORD_ROLLOVER_TIME
CREATE PROFILE pw_rollover_prof LIMIT PASSWORD_ROLLOVER_TIME 1;
-- Assign the profile to the test user
ALTER USER testuser PROFILE pw_rollover_prof;
The PASSWORD_ROLLOVER_TIME value can range from 1 hour to 60 days. Adjust this value based on the time required to update application connections.
Example: Implementing Password Rollover
Once the profile is set, you can change the user’s password, allowing access with both the old and new passwords during the rollover period:
-- Log in as the test user and change the password
conn testuser/OldPassword1@//localhost:1521/pdb1
ALTER USER testuser IDENTIFIED BY NewPassword1;
-- Connect using both passwords during the rollover period
conn testuser/OldPassword1@//localhost:1521/pdb1
-- Connected
conn testuser/NewPassword1@//localhost:1521/pdb1
-- Connected
During the specified rollover time, both passwords are valid, allowing applications to transition without immediate disruptions.
Disabling Password Rollover
To disable gradual password rollover, set PASSWORD_ROLLOVER_TIME to 0. This action ensures that only the new password is usable after the change:
ALTER PROFILE pw_rollover_prof LIMIT PASSWORD_ROLLOVER_TIME 0;
-- Testing the change
conn testuser/OldPassword1@//localhost:1521/pdb1
-- Error: ORA-01017: invalid username/password
conn testuser/NewPassword1@//localhost:1521/pdb1
-- Connected
Key Considerations and Warnings
- Security Risks: If a password is compromised, it is critical to disable rollover immediately to prevent misuse of the old password.
- Transition Downtime: Avoid setting rollover time to zero without caution, as active sessions may face interruptions.
- Periodic Validation: Confirm
ACCOUNT_STATUS using the DBA_USERS view to monitor any active rollovers:
SELECT username, account_status, password_change_date
FROM dba_users
WHERE username = 'TESTUSER';
Conclusion
The PASSWORD_ROLLOVER_TIME feature in Oracle Database 19c and 21c simplifies password transitions, reducing downtime and improving security management. Configure and monitor this setting according to your security needs to ensure smooth password changes.