Changing passwords periodically is a common practice, but if you’re dealing with database users who serve to support applications, it can be quite a headache.
Let’s start by understanding what goes on behind the scenes and then we can see how to overcome the problems that arise.
Let’s imagine the following scenario:
We have a group of applications that connect to the database db1, and they do it with the webapp user identified with password dyingPass. This user has associated the profile webuser.
Standard procedure
Modified password
The password of the database user used by the applications is changed to the value brandNewPass.
SQL> alter user webapp identified by brandNewPass;1
2
Application downtime
Problems begin: any attempt to connect to the database with the previous password is not allowed, thus interrupting the availability of applications.
java.sql.SQLException: ORA-01017: invalid username/password; logon deniedUpdate password
A race against time is started, to update passwords in all application configuration files, to minimize application downtime.
<Resource
name="jdbc/myoracle"
auth="Container"
type="javax.sql.DataSource"
username="webapp"
password="brandNewPass"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@server1:1521:db1"
/>3
Gradual password rollover
At this point it is clear that when changing the password of a database user associated to an application, the application is affected and no longer available until its configuration is updated to reflect the new password.
Fortunately, since Oracle Server 19.12 it is possible to overcome this problem and ensure that the service continues to be provided uninterruptedly.
This is achieved by allowing the old password to remain valid for a defined interval, i.e.: for the same database user there are 2 valid passwords simultaneously!
Without further delay, let’s see how to achieve this.
Changes to profile
To allow applications to use the old and new password, it is necessary to modify the webuser profile associated to the webapp user, assigning a value greater than zero to the password_rollover_time.
alter profile webuser password_rollover_time 3/24;
The allowed values for this parameter range from 0 (default value) to 60, expressed in days.
The minimum accepted is one hour, which is 1/24, but for our example we have considered a limit of 3 hours.
1
2
Modify password
We proceed to change the password of the database user used by the applications, to the value brandNewPass.
SQL> alter user webapp identified by brandNewPass;Update password
Applications continue to provide uninterrupted service.
We can gradually update the database user password in all application configuration files.
<Resource
name="jdbc/myoracle"
auth="Container"
type="javax.sql.DataSource"
username="webapp"
password="brandNewPass"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@server1:1521:db1"
/>3
4
Updating continues
As long as the set period is not exceeded, we can continue to update the passwords in the applications, and they continue to provide uninterrupted service.
<Resource
name="jdbc/myoracle"
auth="Container"
type="javax.sql.DataSource"
username="webapp"
password="brandNewPass"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@server1:1521:db1"
/>Additional considerations
1. Period exceeded
java.sql.SQLException: ORA-01017: invalid username/password; logon denied2. Oracle Data Guard
In a configuration with Oracle Data Guard, in which the standby database allows read-only connections, if the first connection attempt is made after the 3 hours limit we set in our example, it will be denied with the ORA-16000 error.
ORA-16000: database or pluggable database open for read-only accessTo prevent this from happening, the instance parameter adg_account_info_tracking must be changed to the value global (requires restart).
SQL> alter system set adg_account_info_tracking=global scope=spfile;3. Require the use of the new password
If we want to immediately accept only connections with the new password, we will have to execute the following command.
SQL> alter user webapp expire password rollover period;Conclusions
Following a rather simple procedure, it is now possible for applications to continue using the previous password, while the new password is being updated in the configuration files.
What traditionally implied a high impact on the availability of applications, is no longer the case!