Gradual Database Password Rollover

Oracle database 21c introduced a new feature called, Gradual database password rollover – the ability to allow service user accounts passwords updated online completely, without downtime – with the latest RU this got back ported to 19c (long term release version 19.12)

 

With this new feature, password of an application user account can be changed without having to schedule a downtime. Which is really a great & this can be done using a new profile parameter PASSWORD_ROLLOVER_TIME, this will set the rollover period of time (a small window period) where the application can still log in using either the old and new passwords, administrator does not need any more to take the application down when the application password is changed.

 

To see how this works, let’s create a profile

 

demo@PDB19> create profile password_rollover_test

  2  limit

  3  password_rollover_time 1/24;

 

Profile created.

 

Note the parameter PASSWORD_ROLLOVER_TIME is set of 1/24 which means 1 hour (the least value that this parameter can have and at max can go with 60 days or the lower value of PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME or whichever is lower).

 

Let’s create a new user and by default associated with “DEFAULT” profile.

 

demo@PDB19> grant create session to app_user1

  2  identified by "Password-1";

 

Grant succeeded.

 

demo@PDB19> conn app_user1/Password-1@pdb19

Connected.

app_user1@PDB19> conn demo/demo@pdb19

Connected.

demo@PDB19> select account_status,profile

  2  from dba_users

  3  where username ='APP_USER1';

 

ACCOUNT_STATUS                   PROFILE

-------------------------------- --------------------

OPEN                             DEFAULT

 

Now lets assign this new user with the profile created above and change the password for the service account created to get started with Password rollover period feature.

 

demo@PDB19> alter user app_user1 profile password_rollover_test;

 

User altered.

 

demo@PDB19> alter user app_user1 identified by "app_user1";

 

User altered.

 

During this rollover period window, we can connect to this service account using either new or old passwords.

demo@PDB19> select account_status,profile

  2  from dba_users

  3  where username ='APP_USER1';

 

ACCOUNT_STATUS                   PROFILE

-------------------------------- -------------------------

OPEN & IN ROLLOVER               PASSWORD_ROLLOVER_TEST

 

demo@PDB19> conn app_user1/Password-1@pdb19

Connected.

app_user1@PDB19> conn app_user1/app_user1@pdb19

Connected.

app_user1@PDB19>

 

We can see that the connection is successfully done with both passwords during the rollover period. To end the rollover period

·         Let the password rollover expire on its own.

·         As either the user or administrator we can run the explicit expire password rollover period

 

demo@PDB19> alter user app_user1 expire password rollover period;

 

User altered.

 

During the rollover period, the unified auditing will keep track of user authentication using old passwords

 

demo@PDB19> select os_username,userhost,terminal,event_timestamp,authentication_type

  2  from unified_audit_trail

  3  where action_name ='LOGON'

  4  and authentication_type like '%VERIFIER%OLD%';

 

OS_USERNAM USERHOST                  TERMINAL        EVENT_TIMESTAMP                AUTHENTICATION_TYPE

---------- ------------------------- --------------- ------------------------------ ----------------------------------------

v-rjeyabal DOMAIN\COZYWVDIXD1517     COZYWVDIXD1517  16-SEP-21 08.19.18.772835 PM   (TYPE=(DATABASE));(CLIENT ADDRESS=((ADDR

                                                                                    ESS=(PROTOCOL=tcp)(HOST=10.69.73.120)(PO

                                                                                    RT=56398))));(CLIENT ADDRESS=());(LOGON_

                                                                                    INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILI

                                                                                    TIES=O5L_NP,O7L_MR,O8L_LI)));

 

Once the rollover period was completed, authentication using old password is not possible.

 

demo@PDB19> conn app_user1/Password-1@pdb19

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

demo@PDB19> conn app_user1/app_user1@pdb19

Connected.

app_user1@PDB19> conn demo/demo@pdb19

Connected.

demo@PDB19> select account_status,profile

  2  from dba_users

  3  where username ='APP_USER1';

 

ACCOUNT_STATUS                   PROFILE

-------------------------------- -----------------------

OPEN                             PASSWORD_ROLLOVER_TEST

 

demo@PDB19>