Gradual Database Password Rollover hands-on

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:

gradual_database_password_rollover01
gradual_database_password_rollover01

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.

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>