Introduction
Gradual Database Password Rollover for Applications is a new feature that was introduced in Oracle Database 21c. Now, it is backported and also available in 19c with RU 19.12. It enables to update an application user’s password while keeping the old password valid for a specific period of time. During this period, the application can successfully connect to the database using either password, the old or the new one. This avoids application downtime while the new password is propagated to the clients.
This feature is for application users only, hence it cannot be used for users with administrative privileges.
And, you do NOT need to increase the COMPATIBLE parameter to 19.12 to use this feature.
The Environment
Oracle Database 19.12.
I provisioned an Oracle Database 19.11 from the Marketplace Image in Oracle Cloud on a compute VM and applied RU 19.12 (Patch 32904851) manually. Check Patching all my environments with the July 2021 Patch Bundles.
-- requires opatch 12.2.0.1.24 or higher
$ORACLE_HOME/OPatch/opatch version
SQL> shutdown immediate
lsnrctl stop
cd <PATCH_TOP_DIR>/32904851
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ORACLE_HOME/OPatch/opatch apply -local
lsnrctl start
SQL> startup
SQL> alter pluggable database all open;
$ORACLE_HOME/OPatch/datapatch -prereq
$ORACLE_HOME/OPatch/datapatch -verbose
SQL> select con_id, action_time, description from cdb_registry_sqlpatch where description like '%19.12%';
ACTION_TIME DESCRIPTION
---------------------------------------- ----------------------------------------------------------------------
CON_ID ACTION_TIME DESCRIPTION
---------- ---------------------------------------- ----------------------------------------------------------------------
3 25-JUL-21 09.02.50.635903 AM Database Release Update : 19.12.0.0.210720 (32904851)
1 25-JUL-21 09.02.50.197237 AM Database Release Update : 19.12.0.0.210720 (32904851)
User Profiles
To use this feature, set the PASSWORD_ROLLOVER_TIME parameter in the user’s profile. The value is specified in days. The minimum value is one hour (1/24) and the maximum value is 60 days. To create a new user profile:
SQL> CREATE PROFILE pwprofile LIMIT PASSWORD_ROLLOVER_TIME 1/24;
Profile created.
To change an existing user profile:
SQL> ALTER PROFILE existingProfile LIMIT PASSWORD_ROLLOVER_TIME 1;
Profile altered.
Users
Create a new user with the new profile assigned:
SQL> CREATE USER newUser IDENTIFIED BY UserPassW__11 PROFILE pwprofile;
User created.
SQL> GRANT CREATE SESSION TO newUser;
Grant succeeded.
Assign the profile to an existing user:
SQL> ALTER USER existingUser PROFILE pwprofile;
User altered.
Log in with the newly created user:
SQL> connect newUser/UserPassW__11@orclpdb
Connected.
Password Change
Now let’s test the new feature. First we change the user’s password:
SQL> ALTER USER newUser IDENTIFIED BY UserPassW__22;
User altered.
Logging in using either the old or the new password works during the password rollover period defined by PASSWORD_ROLLOVER_TIME, which is one hour in our example:
SQL> connect newUser/UserPassW__11@orclpdb
Connected.
SQL> connect newUser/UserPassW__22@orclpdb
Connected.
Query the account status of the user:
SQL> SELECT account_status FROM dba_users WHERE username = 'NEWUSER';
ACCOUNT_STATUS
--------------------------------
OPEN & IN ROLLOVER
If you change the password again within the rollover period, then only the newest password and the original password (before the first change) are valid.
Ending the Password Rollover Period
To end the rollover period, do nothing and just wait until the time period is passed. If you were able to update all clients sooner and want to end the rollover period earlier by yourself:
SQL> ALTER USER newUser EXPIRE PASSWORD ROLLOVER PERIOD;
User altered.
Now, only logins using the new password are successful:
SQL> connect newUser/UserPassW__11@orclpdb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect newUser/UserPassW__22@orclpdb
Connected.
The account status changes to OPEN:
SQL> SELECT account_status FROM dba_users WHERE username = 'NEWUSER';
ACCOUNT_STATUS
--------------------------------
OPEN
Who is Still Using the Old Password?
It is possible to track users to check whether they are using the old or the new password by having a look at the AUTHENTICATION_TYPE column of the unified_audit_trail view:
SQL> select * from unified_audit_trail where action_name='LOGON' and authentication_type like '%VERIFIER=12C-OLD%' and event_timestamp>systimestamp-1;
AUTHENTICATION_TYPE
----------------------------------------------------------
...(LOGON_INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILITIES=...
SQL> select * from unified_audit_trail where action_name='LOGON' and authentication_type like '%VERIFIER=12C-NEW%' and event_timestamp>systimestamp-1;
AUTHENTICATION_TYPE
----------------------------------------------------------
...(LOGON_INFO=((VERIFIER=12C-NEW)(CLIENT_CAPABILITIES=...
Administrative Users
Administrative users are restricted from using this feature, no matter which profile they are associated with. You can assign the new profile to them, but after changing the password, you’ll see that the account status is still OPEN and not “OPEN & IN ROLLOVER”:
SQL> ALTER USER sys PROFILE pwprofile;
User altered.
SQL> ALTER SESSION SET CONTAINER = cdb$root;
Session altered.
SQL> ALTER USER sys IDENTIFIED BY NewSYSpassW__22 CONTAINER = ALL;
User altered.
SQL> ALTER SESSION SET CONTAINER = orclpdb;
Session altered.
SQL> SELECT profile, account_status FROM dba_users WHERE username = 'SYS';
PROFILE ACCOUNT_STATUS
-------------------- --------------------
PWPROFILE OPEN
Additionally, you cannot grant administrative privileges to users who have a password rollover-enabled profile:
SQL> GRANT SYSDBA to newUser;
GRANT SYSDBA to newUser
*
ERROR at line 1:
ORA-28227: Gradual password rollover is not supported for administrative users.
Restrictions
Gradual Database Password Rollover cannot be enabled for the following type of connections:
- Direct logins for Oracle Real Application Security users
- Kerberos-, certificate-, or RADIUS-based externally authenticated connections
- Centrally managed user (CMU) connections
- Administrative connections that use external password files
- The Oracle Data Guard connection between the primary and the standby
GDPR
Most importantly, don’t be confused if people refer to this feature as GDPR 😉
Database Heartbeat 