Infrastructure at your Service

Mouhamadou Diaw

Oracle 21c Security : Gradual Database Password Rollover

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

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

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

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

SQL>  select username,account_status from dba_users where username='EDGE';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------
EDGE                 OPEN

SQL>

Now let’s log with new user

[[email protected] 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

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

[[email protected] 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

[[email protected] ~]$ 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

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

    Alter user edge expire password rollover period;

-As an administrator, expire the user password

Alter user edge password expire;

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

5 Comments

  • Dan Morgan says:

    Good monograph.

    It’s a nice new feature for those that have not embraced the enhancements in 18c such as NO AUTHENTICATION and PROXY users.
    With proxy users the ability to have active multiple application passwords has been available for more than a decade.

  • Friedhold Matz says:

    Great thanks!

  • prashant k says:

    i get thiserror if I enable at profile level pasword_rollover_time to 1

    ORA-03114: not connected to ORACLE

    Error accessing package DBMS_APPLICATION_INFO

  • prashant k says:

    SQL> create user edge identified by “Borftg8957##” profile testgradualrollover;

    User created.

    SQL> grant create session to edge;

    Grant succeeded.

    SQL> select username,account_status from dba_users where username=’EDGE’;

    USERNAME
    ——————————————————————————–
    ACCOUNT_STATUS
    ——————————–
    EDGE
    OPEN

    SQL> alter user edge identified by “Morfgt5879!!”;

    User altered.

    SQL> select username,account_status from dba_users where username=’EDGE’;

    USERNAME
    ——————————————————————————–
    ACCOUNT_STATUS
    ——————————–
    EDGE
    OPEN & IN ROLLOVER

    SQL> conn edge/”Borftg8957##”@pdb1
    ERROR:
    ORA-03114: not connected to ORACLE

    Error accessing package DBMS_APPLICATION_INFO
    Connected.

    SQL> conn edge/”Morfgt5879!!”@pdb1;
    Connected.
    SQL>
    once rollover start old passord if try to login gives error dbms_application_info

  • Mouhamadou Diaw says:

    Hi prashant

    Did you try without your sqlnet.ora. I did the test on premise without sqlnet.ora

    Regards

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Senior Consultant