By Mouhamadou Diaw

Starting with Oracle 21c, a password of an application can be changed without having to schedule a downtime. This can be done by using the new profile parameter PASSWORD_ROLLOVER_TIME
This will set a rollover period of time where the application can log in using either the old password or the new password. With this enhancement, an administrator does not need any more to take the application down when the application database password is being rotated.
Let see in this blog how this works

1
2
3
4
5
6
7
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL>

First we create a profile in PDB1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> CREATE PROFILE testgradualrollover LIMIT
 FAILED_LOGIN_ATTEMPTS 4
 PASSWORD_ROLLOVER_TIME 4; 
Profile created.
SQL>

Note that the parameter PASSWORD_ROLLOVER_TIME is specified in days. For example, 1/24 means 1H.
The minimum value for this parameter is 1h and the maximum value is 60 days or the lower value of the PASSWORD_LIFE_TIME or PASSWORD_GRACE_TIME parameter.
Now let’s create a new user in PDB1 and let’s assign him the profile we created

1
2
3
4
5
6
7
8
9
SQL> create user edge identified by "Borftg8957##"  profile testgradualrollover;
User created.
SQL> grant create session to edge;
Grant succeeded.
SQL>

We can also verify the status of the account in the PDB

1
2
3
4
5
6
7
SQL>  select username,account_status from dba_users where username='EDGE';
USERNAME             ACCOUNT_STATUS
-------------------- --------------------
EDGE                 OPEN
SQL>

Now let’s log with new user

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[oracle@oraadserver admin]$ sqlplus edge/"Borftg8957##"@pdb1
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 10 11:14:07 2020
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> show user;
USER is "EDGE"
SQL>

Now let’s change the password of the user edge

1
2
3
4
5
SQL> alter user edge identified by "Morfgt5879!!";
User altered.
SQL>

As the rollover period is set to 4 days in the profile testgradualrollover, the user edge should be able to connect during 4 days with either the old password or the new one.
Let’s test with the old password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[oracle@oraadserver admin]$ sqlplus edge/"Borftg8957##"@pdb1
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 10 11:21:02 2020
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Last Successful login time: Thu Dec 10 2020 11:14:07 +01:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> show user;
USER is "EDGE"
SQL>

Let’s test with the new password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[oracle@oraadserver ~]$ sqlplus edge/'Morfgt5879!!'@pdb1
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 10 11:24:52 2020
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Last Successful login time: Thu Dec 10 2020 11:21:02 +01:00
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
SQL> show user;
USER is "EDGE"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL>

We can see that the connection is successfully done with both cases. If we query the dba_users we can see the status of the rollover

1
2
3
4
5
SQL> select username,account_status from dba_users where username='EDGE';
USERNAME             ACCOUNT_STATUS
-------------------- --------------------
EDGE                 OPEN & IN ROLLOVER

To end the password rollover period
-Let the password rollover expire on its own
-As either the user or an administrator run the command

1
Alter user edge expire password rollover period;

-As an administrator, expire the user password

1
Alter user edge password expire;

Database behavior during the gradual password rollover period can be found here in the documentation

Post Views: 2,152
Thumbnail [60x60]
by
Oracle Team