The gradual password rollover feature introduced a new parameter for user profiles called "PASSWORD_ROLLOVER_TIME". When this is set, a user could have two passwords for authentication for the duration specified by the value set for PASSWORD_ROLLOVER_TIME. Once the rollover period ends only the new password is valid.
This could be demo as below using 19.12. A new profile is craeted with password rollver time set to 1.
SQL> create profile test limit PASSWORD_ROLLOVER_TIME 1; Profile created.A user is assigned the new profile
SQL> alter user asangaro profile test; User altered.The current status of the account is open.
SQL> select username,account_status from dba_users where username='ASANGARO'; USERNAME ACCOUNT_STATUS ---------- --------------- ASANGARO OPENChange the password for the user and check the account status
SQL> alter user asangaro identified by hello123##; User altered. SQL> select username,account_status from dba_users where username='ASANGARO'; USERNAME ACCOUNT_STATUS ---------- ------------------- ASANGARO OPEN & IN ROLLOVERAccount status is now open but in the rollover period. During this period user can use both the old password and new password set above.
The rollover period could be manually ended by using the following command.
SQL> alter user asangaro expire password rollover period; User altered. SQL> select username,account_status from dba_users where username='ASANGARO'; USERNAME ACCOUNT_STATUS ---------- ----------------- ASANGARO OPENMain thign to remember is the below
Oracle Database does not send any special messages to the database clients that indicate that the user account is in the password rollover period. This design avoids any errors from applications that may not be equipped to handle error and warning messages when a user logs in.UCP will not automatically update itself with the new password. However, its behaviour may seems it has updated the password and working fine.
Universal Connection Pool (UCP) does the authentication only during the intial creation of the connection. For example if the inital size of the connection pool is set to 10, then those 10 connections will be authenticated using the password provided. There's no authentication happening when these connections are checked out the pool later on. This could be easily tested and verified (no need for PASSWORD_ROLLOVER_TIME configuration) by creating a UCP with initial set of connections and then changing the password. The connection created would still work and would be able to run DB queries. Because of this fact, even after rollover period has ended UCP may continue to function normal unless additoinal connections are created.
If more connections are needed than the intial amount, then authentication take place when those are created in the connection pool. Going by the above example, if password was changed after initializng the pool with 10 connections then during the creation of the 11th connection an ORA-1017 will be thrown. So after the rollover period has ended, if the UCP needed more connection than it had during the rollover period this would result in ORA-1017.
There's no way to update the UCP with the new password without recreating it. The property check interval only concern itself with resizing the pool. Not with password changes. Even if there is a UCP manger, methods such as purge, recycle, refresh would not update the password in the pool.
Only possible solution it seems is to destroy and recreate the pool. During this period the source where password is read must have been updated with the new password.
Alternatively, application could have a rolling restart with the new password.
