Very often database user passwords are hard coded in application configuration files and when the application user’s database password is changed there will be an application outage due to an ORA-01017 invalid username or password error – until the new password is changed in all the application configuration files where this password has been hard-coded.
Gradual password rollover is a new feature introduced in Oracle Database 21c and enhanced in Oracle Database 23ai that allows us to change a database user’s password without causing an outage or interrupting applications that are currently using the old password.
This feature allows two passwords to be used or active for the same user for a specified period of time. During this interim time period it is assumed that the new password will be propagated to all the client or application configuration files – in this time period the old password will still be valid and after the expiry of this ‘grace’ period, then the old password will no longer be valid.
Example
Create the APP_OWNER user – note the password used.
SQL> create user app_owner identified by Myoriginalpassword123;
User created.
SQL> grant dba to app_owner;
Grant succeeded.
We have a test script app_conn.sh which has the APP_OWNER user’s password hard-coded.
[oracle@localhost ~]$ cat app_conn.sh
sqlplus app_owner/Myoriginalpassword123@pdb1
[oracle@localhost ~]$ ./app_conn.sh
SQL*Plus: Release 21.0.0.0.0 - Production on Thu May 25 08:24:20 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Thu May 25 2023 08:23:33 +00:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> quit
Create a database profile and note that the profile resource PASSWORD_ROLLOVER_TIME is set to 1 – in this case it means 1 day. It can also be set to values like 1/24 which means 1 hour.
[oracle@localhost ~]$ sqlplus system/Oracle_4U@pdb1
SQL*Plus: Release 21.0.0.0.0 - Production on Thu May 25 08:24:40 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon May 22 2023 06:57:11 +00:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> create profile pw_rollover_time limit password_rollover_time 1;
Profile created.
SQL> alter user app_owner profile pw_rollover_time;
User altered.
SQL> select account_status from dba_users where username='APP_OWNER';
ACCOUNT_STATUS
--------------------------------
OPEN
Change the database password for the user.
Note the ACCOUNT_STATUS for this user – OPEN & IN ROLLOVER
SQL> alter user app_owner identified by Mynewpassword123;
User altered.
SQL> select account_status from dba_users where username='APP_OWNER';
ACCOUNT_STATUS
--------------------------------
OPEN & IN ROLLOVER
Our script app_conn.sh still has the old password for the user hard-coded.
But we are still able to connect to the database even with the old password!
[oracle@localhost ~]$ ./app_conn.sh
SQL*Plus: Release 21.0.0.0.0 - Production on Thu May 25 08:30:54 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Thu May 25 2023 08:24:20 +00:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> sho user
USER is "APP_OWNER"
During this ‘grace’ period where the old and new passwords are still valid, the ACCOUNT_STATUS column has the value OPEN & IN ROLLOVER.
SQL> select account_status,
to_char(password_change_date, 'DD-MON-YY HH24:MI:SS') as password_change_date
from dba_users
where username ='APP_OWNER';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ---------------------------
OPEN & IN ROLLOVER 25-MAY-23 08:29:37
Once the gradual password failover time period has expired (in this example its 1 day), we can see that the ACCOUNT_STATUS value changes to the default value of OPEN.
SQL> select account_status,
to_char(password_change_date, 'DD-MON-YY HH24:MI:SS') as password_change_date
from dba_users
where username ='APP_OWNER';
ACCOUNT_STATUS PASSWORD_CHANGE_DATE
-------------------------------- ---------------------------
OPEN 25-MAY-23 08:29:37
