Skip to content

Oracle 21c Security : Gradual Database Password Rollover

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

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

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