Note:

Set up Gradual Password Rollover in Oracle Database on Oracle Cloud Infrastructure

Introduction

Gradual password rollover is a feature in Oracle Database that allows to change a database user’s password without interrupting applications that are currently using the old password. With gradual password rollover, the old password remains valid for a specified period of time, after which it is no longer accepted, that is two different passwords are allowed for a user temporarily. This allows the propagation of new password to all client applications without any downtime. Password rollover period begins the moment password is changed.

Gradual password rollover feature is exposed as a user profile password resource setting (password resource limit) PASSWORD_ROLLOVER_TIME, which is applied to the user sessions. User profiles in Oracle Database are a set of limits on database resources - user level configuration. User profile helps regulate the amount of resources used by each database user. A database gets created with a default profile named DEFAULT, which is assigned to all users of the database if not specified otherwise. We can use default profile or create a new profile to add password resource limits.

In this tutorial, we will only focus on PASSWORD_ROLLOVER_TIME, and go through a step-by-step guide for using this feature in an Oracle Autonomous Transaction Processing (ATP) database service. We will use Oracle SQL Developer Web tool from OCI Console for this purpose.

There are other things that one should know before using the password rollover feature.

Objectives

Prerequisites

Task 1: Check the Default Profile in ATP

We will see the default profile and the value of PASSWORD_ROLLOVER_TIME resource in an ATP.

Open Oracle SQL Developer Web tool and run the following SQL query to check the limits of DEFAULT profile.

select * from dba_profiles where profile = 'DEFAULT';

Image showing query result.

In the following image, we see that DEFAULT profile has many resources, and the limit (value) of PASSWORD_ROLLOVER_TIME is 0. This means gradual password rollover is disabled for DEFAULT profile. Limit of resources in the DEFAULT profile can be changed, but we will create a new profile to demonstrate this feature.

Task 2: Create a New User Profile

Create a profile with FAILED_LOGIN_ATTEMPTS set to 10 and PASSWORD_ROLLOVER_TIME limit as 1 hour (1/24 day).

  1. Run the following SQL query to create a profile named PW_ROLLOVER_TIME_1HR_PROF.

    create profile PW_ROLLOVER_TIME_1HR_PROF limit FAILED_LOGIN_ATTEMPTS 10 PASSWORD_ROLLOVER_TIME 1/24;

    Image showing query result.

  2. Run the following SQL query to check the PASSWORD_ROLLOVER_TIME limit.

    select * from dba_profiles where profile = 'PW_ROLLOVER_TIME_1HR_PROF';

    Image showing query result.

    The resource limit for PASSWORD_ROLLOVER_TIME is 0.416 (1/24 day), and failed login attempts is set to 10.

Task 3: Create a User Account

We will create a user account and assign the created profile PW_ROLLOVER_TIME_1HR_PROF to the user.

  1. Run the following SQL query to create a user named db_test.

    create user db_test identified by "Pass_Rollover@2023" profile = 'PW_ROLLOVER_TIME_1HR_PROF';

    Image showing query result.

    An existing user can also be attached to a profile using the following command.

    alter user <user_name> profile <profile_name>;

  2. Run the following SQL query to grant CREATE SESSION privilege to test the database connection.

    GRANT CREATE SESSION TO DB_TEST;

    Image showing query result.

  3. We will check the account status by using the following SQL query.

    select user_name, profile, account_status from dba_users where user_name = 'DB_TEST';

    Image showing query result.

Task 4: Change the User Password

Change the user password and see account status.

  1. Run the following SQL query to change user password.

    alter user DB_TEST identified by "New_Pass@2023";

  2. Check the account status.

    Image showing query result.

    Account status now shows IN ROLLOVER, which means DB_TEST user account is currently in rollover and both old and new password can be used to connect to the user for 1 hour. After 1 hour, old password will no longer be accepted, and connections can be established using new password only.

Task 5: Expire Rollover Period Forcefully

Password rollover feature allows us to expire rollover forcefully when we do not want the old password to be valid for remaining rollover period.

  1. Run the following SQL query to expire password rollover for the user.

    alter user DB_TEST expire password rollover period;

    Image showing query result.

  2. Check the account status.

    Image showing query result.

    The account status has changed to OPEN, and we can no longer connect to the database using old password. So, when a user is in rollover such as both new and old passwords are functional, the account status in DBA_USERS table is OPEN & IN ROLLOVER, and account status changes to OPEN after the rollover period expires.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.