DBA Hub

📋Steps in this guide1/6

Gradual Database Password Rollover Time (PASSWORD_ROLLOVER_TIME) in Oracle Database 19c and 21c

From Oracle 21c onward we can define a password rollover time, which allows us to connect using both the old and the new passwords during the gradual rollover time period. This feature was backported to Oracle 19c in the 19.12 release update.

oracle 21cconfigurationintermediate
by OracleDba
20 views
1

Create a Test User

We connect to a privileged user and create a test user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
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;
2

Create a Gradual Database Password Rollover Profile

We create a new profile, setting the limit to 1 day, and associate the test user with this profile. We can also alter the limit of an existing profile using the command. In the following example we set it to 1.5 days. The minimum value is one hour (1/24), and the maximum time is 60 days.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
-- 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;

alter profile pw_rollover_time_prof limit
  password_rollover_time 1.5;
3

Test It

We connect to the test user and reset the password. For the next 1.5 days we can connect using both the old and new passwords.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
conn testuser1/testuser1@//localhost:1521/pdb1

alter user testuser1 identified by newpasswd1;

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

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

Views

The view includes a column, and the column tells us if the user is currently in rollover. The query below shows us the status of the account. The value of the limit is available from the view, like any other profile limit.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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
15-oct-2021 09:22:43

SQL>

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>
5

Disabling Gradual Database Password Rollover

Setting the limit to 0 disables gradual password rollover. In the following example we set the limit to zero, and fail to connect to the test user with the old password.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>
6

A Word of Warning

Imagine the old password were compromised and we needed to disable the current gradual password rollover, so the old password could no longer be used, but we want to enable gradual password rollover for future password changes. We must make sure there is at least one connection attempt between disabling and reenabling gradual password rollover, or we may not get the password reset we desire. We reset the rollover time and change the password again. We can connect with either of the new passwords again. This time set the limit to 0, then immediately back to 1.5 days, with no connection attempt between them. Provided there have been no new connection attempts between the resets, using either the old or new passwords, we can still connect with both passwords, so we have not really disabled and reenabled gradual password rollover. For more information see: - CREATE PROFILE - Gradual Database Password Rollover Time Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;

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

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

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;

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!