Oracle database 21c new feature: password step-by-step switching strategy

Hello, everyone. I'm Mr. Tony, who only talks about technology and doesn't cut his hair.

Changing the database user password on a regular basis may result in the interruption of the application. Usually, we need to plan downtime, modify the database user password, then modify the connection information in the application server, and finally restart the application.

However, starting from Oracle 21c, we can define a transition time for password switching. During this time period, we can use the old password and the new password to connect to the database at the same time. This means that we can change the database password, but the application does not need to disconnect and re-establish the connection. This feature is not only available in Oracle 21c, but also migrated forward to Oracle 19c (19.12 patch update).

Create test user

First, connect to the database with a privileged user and create a test user:

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;

create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

Create password step-by-step switch configuration

Create a configuration item of information and put password_ ROLLOVER_ Set time to 1 day and associate it with the test user:

-- drop profile pw_rollover_time_prof;

create profile pw_rollover_time_prof limit
  password_rollover_time 1;

alter user testuser1 profile pw_rollover_time_prof;

We can also use the ALTER PROFILE command to modify the password in the existing configuration item_ ROLLOVER_ Time limit. The following example modifies the transition time to 1.5 days:

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

PASSWORD_ROLLOVER_TIME allows a minimum of 1 hour (1, 24) and a maximum of 60 days.

Test connection

We use the test user to connect to the database and reset the password:

conn testuser1/testuser1@//localhost:1521/pdb1

alter user testuser1 identified by newpasswd1;

In the next 1.5 days, we can use both the old password and the new password to connect to the database.

conn testuser1/testuser1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

Related view

DBA_ A password is added to the users view_ CHANGE_ Date field and account identifying whether the user is in the transition state of switching password_ Status field. The following query returns the status of account TESTUSER1:

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

select account_status,
       to_char(password_change_date, 'dd-mon-yyyy hh24:mi:ss') as password_change_date
from   dba_users
where  username = 'TESTUSER1';

ACCOUNT_STATUS                   PASSWORD_CHANGE_DATE
-------------------------------- -----------------------------
OPEN & IN ROLLOVER               21-nov-2021 19:22:43

SQL>

PASSWORD_ ROLLOVER_ The value of time is available through DBA_PROFILES view:

column resource_name format a25
column limit format a10

select resource_name,
       limit
from   dba_profiles
where  profile = 'PW_ROLLOVER_TIME_PROF'
and    resource_name = 'PASSWORD_ROLLOVER_TIME';

RESOURCE_NAME             LIMIT
------------------------- ----------
PASSWORD_ROLLOVER_TIME    1.5

SQL>

Disable password step-by-step switching

If password_ ROLLOVER_ When time is set to 0, the password step-by-step switching function can be disabled. In the following example, we set this value to 0, and then we can't log in to the database with the old password:

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 0;

conn testuser1/testuser1@//localhost:1521/pdb1
  USER          = testuser1
  URL           = jdbc:oracle:oci8:@//localhost:1521/pdb1
  Error Message = no ocijdbc21 in java.library.path
  USER          = testuser1
  URL           = jdbc:oracle:thin:@//localhost:1521/pdb1
  Error Message = ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>

matters needing attention

If we need to disable and reset the password step-by-step switching function in an emergency, we must ensure that a login operation is performed between the two operations, otherwise the result may not be what we expect.

We first reset the transition time and change the password again.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

conn testuser1/newpasswd1@//localhost:1521/pdb1

alter user testuser1 identified by newpasswd2;

At this point, we can log in with any new password.

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd2@//localhost:1521/pdb1
Connected.
SQL>

Set password_ ROLLOVER_ When time is set to 0, change it back to 1.5 days immediately. There is no new connection during this period.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter profile pw_rollover_time_prof limit
  password_rollover_time 0;

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;

Since there is no new connection between the two modification operations, we can still log in with any password.

conn testuser1/newpasswd1@//localhost:1521/pdb1
Connected.
SQL>

conn testuser1/newpasswd2@//localhost:1521/pdb1
Connected.
SQL>

If you find the article useful, you are welcome to pay attention ❤️, comment 📝, give the thumbs-up 👍!

Tags: Database Oracle

Posted on Wed, 24 Nov 2021 19:12:55 -0500 by bmcewan