DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Gradual Database Password Rollover - Oracle 21c

#devops #oracle #database #data

Starting with Oracle Database 21c (and later in Oracle 19.12), a feature called Gradual Database Password Rollover was introduced. This feature allows a user’s previous password to remain valid for a limited period after setting a new password. During this transition period, a user can have two active passwords simultaneously.

Use Case
This feature is particularly useful for managing password changes for database accounts used by applications. With this approach, a new password can be set for a user, and the application can adopt the new password at an appropriate time.

For applications that do not require a restart to update credentials, this feature eliminates downtime during password changes.

Configuration and Implementation
To enable this feature, you need to set the PASSWORD_ROLLOVER_TIME parameter in the profile associated with the user. This parameter specifies how long the old password remains valid after a password change. The minimum value for this parameter is 1 hour (1/24), and the maximum allowable duration is determined by the following formula:

Max(PASSWORD_ROLLOVER_TIME) = Min(PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, 60 days)

Below is an example scenario illustrating the use of this feature:

Scenario

Step 1: Create Profile and User

SQL> CREATE PROFILE prf1 LIMIT PASSWORD_ROLLOVER_TIME 5;

Profile created.

SQL> CREATE USER vahid IDENTIFIED BY p1 PROFILE prf1;

User created.

SQL> GRANT CREATE SESSION TO vahid;

Grant succeeded.
Enter fullscreen mode Exit fullscreen mode

Step 2: Verify Account Status

SQL> SELECT ACCOUNT_STATUS FROM dba_users WHERE username='VAHID';

ACCOUNT_STATUS
-------------------------
OPEN
Enter fullscreen mode Exit fullscreen mode

Step 3: Change Password

SQL> ALTER USER vahid IDENTIFIED BY p2;

User altered.

SQL> SELECT ACCOUNT_STATUS FROM dba_users WHERE username='VAHID';

ACCOUNT_STATUS
-------------------------
OPEN & IN ROLLOVER
Enter fullscreen mode Exit fullscreen mode

At this point, user vahid has two valid passwords: p1 (rollover password) and p2. The rollover password p1 remains valid for 5 days.

Step 4: Verify Connections

SQL> CONNECT vahid/p1@pdb1
Connected.

SQL> CONNECT vahid/p2@pdb1
Connected.
Enter fullscreen mode Exit fullscreen mode

Step 5: Change Password Again During Rollover

If the password is changed again within the 5-day rollover period, the initial rollover password (p1) remains valid, while the new password replaces the previous password (p2):

SQL> ALTER USER vahid IDENTIFIED BY p3;

User altered.

SQL> CONNECT vahid/p1@pdb1
Connected.

SQL> CONNECT vahid/p3@pdb1
Connected.

SQL> CONNECT vahid/p2@pdb1
ORA-01017: invalid username/password; logon denied
Enter fullscreen mode Exit fullscreen mode

Step 6: After the Rollover Period

After 5 days, the rollover password (p1) becomes invalid:

[root@oLinux7 ~]# date
Mon Aug 23 17:19:51 EDT 2021

[root@oLinux7 ~]# date -s "Mon Aug 29 17:19:00"
Sun Aug 29 17:19:00 EDT 2021

SQL> CONNECT vahid/p2@pdb1
Connected.

SQL> CONNECT vahid/p1@pdb1
ORA-01017: invalid username/password; logon denied
Enter fullscreen mode Exit fullscreen mode

The account status for vahid changes from OPEN & IN ROLLOVER to OPEN:

SQL> SELECT ACCOUNT_STATUS FROM dba_users WHERE username='VAHID';

ACCOUNT_STATUS
-------------------------
OPEN
Enter fullscreen mode Exit fullscreen mode

This concludes the explanation of the Gradual Database Password Rollover feature in Oracle Database.

Top comments (0)