Viscosity's Blog

Changing your database passwords without application downtime using Gradual Password Rollover

Written by Julio Ayapan | Apr 18, 2023 9:33:08 AM

Available for Oracle Database release 19.12, an application can change its database passwords without an administrator having to schedule downtime.

 

Security best practices require that you change your passwords frequently. In a regular scenario, a database administrator and an application manager normally took the application down when the application database password was being rotated. Then, the password must be updated in the database side, the application and all the interfaces using the database schema.

 

Gradual database password rollover allows you to change the password without the need of application downtime. Both, old and new passwords will be valid for database authentication without locking the user UNTIL ROLLOVER period is finished.

 

I will perform an example of this new feature using an Oracle Database 19.17:

  1. Creating a new profile: the new profile limit PASSWORD_ROLLOVER_TIME indicates the time both old and new passwords are valid for authentication. Minimum value is 1 hour (1/24) and maximum value is 60 days. For testing purposes, I will use 1 hour.

 

SQL> create profile rollover

2 limit password_rollover_time 1/24;

 

Profile created.

 

  1. I will create a new user with the ROLLOVER profile. Also, I will grant the connect role to the user.

SQL> create user VNA identified by roll_pass_1

2   profile rollover;

 

User created.

 

SQL> grant connect to vna;

 

Grant succeeded.

 

  1. The user can connect successfully to the database and the status of the account will be OPEN:

$ sqlplus vna/roll_pass_1@DEV_EA

 

 

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

 

SQL> show user

USER is "VNA"

 

Using sys user:

 

SQL> select username, account_Status from dba_users where username='VNA';

 

USERNAME                 ACCOUNT_STATUS

---------------------- ------------------------------

VNA                    OPEN

 

  1. Now, I will change the original password and will show that the user is able to use both new and old password UNTIL ROLLOVER period is finished.

SQL> !date

Mon Apr 10 13:00:15 CDT 2023

 

SQL> alter user vna identified by roll_pass_new;

 

User altered.

 

Using original password:

 

$ date

Mon Apr 10 13:01:26 CDT 2023

[oracle@vnadapex01 admin]$ sqlplus vna/roll_pass_1@DEV_EA

 

Last Successful login time: Mon Apr 10 2023 12:55:21 -05:00

 

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

 

SQL> show user;

USER is "VNA"

 

Using the new password:

 

$ date

Mon Apr 10 13:03:02 CDT 2023

[oracle@vnadapex01 admin]$ sqlplus vna/roll_pass_new@DEV_EA

 

Last Successful login time: Mon Apr 10 2023 13:01:30 -05:00

 

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

 

SQL> show user;

USER is "VNA"

 

  1. After changing the password, account status will be “OPEN & IN ROLLOVER”.

SQL> select username, account_Status from dba_users where username='VNA';

 

USERNAME          ACCOUNT_STATUS

-------------------- --------------------

VNA               OPEN & IN ROLLOVER

 

 

  1. After 1 hour, the original password won’t work and the only way to get connected is using the last updated password.

$ date

Mon Apr 10 14:03:28 CDT 2023

[oracle@vnadapex01 admin]$ sqlplus vna/roll_pass_1@DEV_EA

 

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

$ date

Mon Apr 10 14:04:08 CDT 2023

[oracle@vnadapex01 admin]$ sqlplus vna/roll_pass_new@DEV_EA

 

Last Successful login time: Mon Apr 10 2023 13:03:23 -05:00

 

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

 

SQL> show user

USER is "VNA"

 

 

  1. And the account status will be OPEN once again.

SQL> !date

Mon Apr 10 14:06:20 CDT 2023

 

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

 

USERNAME          ACCOUNT_STATUS

-------------------- ------------------------------

VNA               OPEN

 

 

Some important notes after finishing:

  • To forcefully end the rollover period, you can execute the following SQL command:

SQL> alter user vna expire password rollover period;

  • if you change the password multiple times only 2 passwords are valid for authentication: the original and the very last password.
  • This feature is available from Oracle Database 19.12 RU and 21c.

This new feature is simple but useful, the application can continue to use the older password until the new password is entire configured in the application and other external systems and you don’t need the DBA and the System Administration working at the same time.

 

 
View full post