Table of contents
Preamble
One of my Oracle contact (thanks Marco !) sent me an interesting feature for us: Gradual Database Password Rollover. This feature simply allow you to temporarily allow two different passwords for an account. The period where the two passwords are usable is self define.
This feature is really interesting as you do not need to have your DBA and your application manager to work in concert to change in database and in application at the same time. The DBA can make the change in advance and then the application manager change when (s)he is ready to do it. We can even imagine the DBA to change the password over the week and take the opportunity of an applicative patch over the week-end to restart the application with the new password.
While digging a bit on this feature I have (re-)discovered in the new features guide that Oracle keep a list of the Release Update (RU) features that are (mainly) backported from 21c to 19c:
This document is worth consulting after every new RU as now Oracle does not wait a major release to give access to new features. By the way looking at the list I have seen plenty of cool other stuff to investigate…
My testing has been done a 19.12 Oracle database (minimum RU to test this feature) running on a Red Hat Enterprise Linux Server release 7.9 (Maipo) bare metal server (12 cores and 64GB of memory).
Gradual Database Password Rollover testing
The feature comes with a new parameter in database profiles called PASSWORD_ROLLOVER_TIME. The minimum value you can specify for PASSWORD_ROLLOVER_TIME is one hour and it obviously sets the period where the two passwords will be usable. The granularity is 1 second and you supply the value in fraction of days i.e. 1/24 for one hour, 1/1440 for one minute and 1/86400 for one second.
To test it I create a dummy profile:
SQL> CREATE PROFILE profile01 limit password_rollover_time 1/24; PROFILE created. |
SQL> SET lines 200 SQL> col PROFILE FOR a15 SQL> col limit FOR a20 SQL> SELECT * FROM dba_profiles WHERE PROFILE='PROFILE01' AND resource_name='PASSWORD_ROLLOVER_TIME'; PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP --------------- -------------------------------- -------- -------------------- --- --- --- PROFILE01 PASSWORD_ROLLOVER_TIME PASSWORD 3600 NO NO NO |
I have done the testing on my own personal account (yjaquier). I change the profile of my account to use the newly created one:
SQL> ALTER USER yjaquier PROFILE profile01; USER altered. |
And then I change the password of my account:
SQL> ALTER USER yjaquier IDENTIFIED BY "new_secure_password"; USER altered. |
For a period of one hour I can use the old and the new password:
SQL> CONNECT yjaquier/secure_password@pdb1 Connected. SQL> CONNECT yjaquier/new_secure_password@pdb1 Connected. |
The account status has taken a newly created value:
SQL> SELECT account_status FROM dba_users WHERE username='YJAQUIER'; ACCOUNT_STATUS -------------------------------- OPEN & IN ROLLOVER |
And obviously after 1 hour the old password can no longer be used:
SQL> CONNECT yjaquier/new_secure_password@pdb1 Connected. SQL> CONNECT yjaquier/secure_password@pdb1 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected TO ORACLE. |
And the account status has come back to its original state:
SQL> SELECT account_status FROM dba_users WHERE username='YJAQUIER'; ACCOUNT_STATUS -------------------------------- OPEN |
You can also reduce the password rollover period with a new ALTER USER option. If you are sure that everything now use the new password you can end up immediately the rollover period with:
SQL> SELECT account_status FROM dba_users WHERE username='YJAQUIER'; ACCOUNT_STATUS -------------------------------- OPEN & IN ROLLOVER SQL> ALTER USER yjaquier expire password rollover period; USER altered. SQL> SELECT account_status FROM dba_users WHERE username='YJAQUIER'; ACCOUNT_STATUS -------------------------------- OPEN |
To drop the profile use:
SQL> DROP PROFILE profile01; PROFILE dropped. |
