A database password change reaches the database immediately, while application instances may load their credentials at different times. Connection pools make this harder to see: an already authenticated session can continue serving requests, but the next physical connection must authenticate with an accepted password. A controlled rotation moves every consumer to the new credential and proves that each can reconnect. On supported Oracle Database 19c releases, a temporary password rollover period gives clients time to make that change.
Oracle DBA Lesson 76 Part 2 — Rotate Application Credentials Safely
Where gradual rollover applies
Gradual password rollover is for application service accounts that authenticate with a database password. The statements below use APP_SERVICE, assigned to profile APP_ACCESS, in one pluggable database. Two application instances use that account.
Administrative accounts, and administrative connections that use an external password file, need a different procedure. External or global authentication, Data Guard, and connected-user database links have additional requirements of their own.
Existing sessions and fresh authentication
An existing database session has already passed authentication. A password change, or the end of a rollover period, does not disconnect that session. Application work through it can succeed when that client would fail to open another connection.
For each consumer, verify all three:
- It has loaded the intended new secret reference or credential version.
- It creates a new physical database connection using that credential. Borrowing a connection from a pool can reuse an existing session. A pool checkout alone is insufficient.
- The newly authenticated connection can perform the application's required work.
Use the pool's drain, refresh, or recreation procedure. Plan transaction completion and available capacity before draining connections. Password validity and existing-session handling are separate controls. See altering user accounts.
Verify release support and patch state
Gradual database password rollover was introduced in 19c release update 19.12. A banner that says only "19c" does not establish that the database has this feature. See release update 19.12.
SELECT banner_full FROM v$version;
SELECT patch_id, action, status, action_time, description
FROM dba_registry_sqlpatch
ORDER BY action_time DESC;BANNER_FULL includes the database release and version. DBA_REGISTRY_SQLPATCH records SQL patch installation attempts, including APPLY and ROLLBACK, with SUCCESS or WITH ERRORS. Read the chronology. An old successful apply can be followed by a rollback, and the newest row can concern another patch. Identify the effective release update. A single successful row is not enough.
Reconcile that history with the Oracle home that is actually serving this database, using its binary inventory:
"$ORACLE_HOME/OPatch/opatch" lsinventoryThis reads the selected home's inventory. It does not apply a patch, and it does not prove that every SQL patch action completed. Confirm the home and the container before interpreting the rows. A missing dictionary privilege does not prove that the feature is absent. See V$VERSION, the SQL patch registry, and patch maintenance.
Check the account's effective profile
Inspect the account in its container:
SELECT username, account_status, profile
FROM dba_users
WHERE username = 'APP_SERVICE';When the assigned profile is APP_ACCESS, read the rollover setting and the related password limits:
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile IN ('APP_ACCESS', 'DEFAULT')
AND resource_type = 'PASSWORD'
AND resource_name IN (
'PASSWORD_ROLLOVER_TIME',
'PASSWORD_LIFE_TIME',
'PASSWORD_GRACE_TIME',
'PASSWORD_REUSE_TIME',
'PASSWORD_REUSE_MAX',
'PASSWORD_VERIFY_FUNCTION')
ORDER BY profile, resource_name;Resolve a DEFAULT value through the same parameter in the DEFAULT profile. Read the profile that is actually assigned. A parameter row shows that the parameter exists. Its effective value decides whether rollover is enabled. Check the release and the patch inventory as well. When gradual rollover is unavailable, use a coordinated maintenance procedure, or another application identity whose grants and ownership dependencies have been tested.
Before changing a shared profile, list its other users:
SELECT username, account_status
FROM dba_users
WHERE profile = 'APP_ACCESS'
ORDER BY username;A profile change affects every account assigned to that profile. Leave the shared DEFAULT profile unchanged. See DBA_PROFILES and CREATE PROFILE.
Prepare a finite handover
Inventory the application instances, pools, scheduled jobs, and other consumers. For each one, record where it reads its credential, whether it reloads that credential on its own, the connection-drain method it supports, the fresh-login test, and the application action that must succeed. Include a way to stop the rollout and restore service before the deadline.
When one hour is a suitable overlap, and the effective lifetime and grace settings allow it:
ALTER PROFILE APP_ACCESS LIMIT
PASSWORD_ROLLOVER_TIME 1/24;The unit is days. 1/24 day equals one hour. This prepares rollover. The window starts when the database account's password is changed.
Oracle documents a minimum enabled duration of one hour. The upper bound is 60 days, and it is also limited by the account's password lifetime and grace. The Security Guide describes the handling when grace is zero. Resolve the effective values and check the rules for that release before choosing a duration. A default of zero disables gradual rollover. One hour shows the mechanism. It is not a maintenance window for every application. See rollover configuration and limits and ALTER PROFILE.
Start the change, then update clients
Change the password through the secret-management procedure. SQL*Plus can prompt for it:
PASSWORD APP_SERVICEEnter the new password at the prompt. The command leaves the password out of the script. The new password must satisfy the effective reuse rules and the verification function.
The password change starts the rollover period. During that bounded period, both the original password and the newest password can authenticate the application account when the account is otherwise eligible to log in. Distribute the new credential and update each client. Database rollover does not distribute secrets or reload applications.
For instance 1, change its secret reference, drain or recreate connections with the supported procedure, open a fresh connection with the new credential, and verify the application work. Instance 2 can keep using the original credential while the overlap remains valid. Repeat the update and the checks for instance 2 and for every remaining consumer.
Record one row for each consumer:
| Consumer | New reference loaded | Fresh login with new credential | Application work |
|---|---|---|---|
| Instance 1 pool | New secret reference or credential version | New physical connection with the new credential | Required application work succeeds |
| Instance 2 pool | New secret reference or credential version | New physical connection with the new credential | Required application work succeeds |
| Each job or other consumer | New secret reference or credential version | New physical connection with the new credential | Required application work succeeds |
During the overlap, an open account can show this status:
| ACCOUNT_STATUS |
|---|
OPEN & IN ROLLOVER |
That status describes the account's rollover state. It does not name which client loaded which credential. On login, Oracle checks whether the period has elapsed, so the dictionary can need a login before the displayed state changes. Use a fresh authentication from each client as the completion test. See DBA_USERS.
Close the overlap deliberately
After every consumer has passed its checks, end the period early:
ALTER USER APP_SERVICE
EXPIRE PASSWORD ROLLOVER PERIOD;Fresh logins then stop accepting the original password. PASSWORD EXPIRE is a different statement: it requires a password change at login. Confirm that the new credential still authenticates, and, in a controlled check, that the original credential does not. A failed attempt with the old password counts toward the account's failed-login policy, so avoid repeated retries. Drain or end existing sessions with the procedure already chosen for them. This statement leaves those sessions connected. See ALTER USER.
If the rollout stalls, stop updating further consumers and use the backout while its prerequisites still hold. Restoring an old client configuration restores authentication only while the original credential is still valid and the account can still log in. Another password change during rollover does not restart the original clock. Oracle keeps the original password and the latest replacement. It does not keep every password used in between. After the overlap closes, restoring service takes a new planned change. The old credential is not a permanent fallback.
If the credential may be compromised, do not leave the old password valid for convenience. Oracle documents one operation that changes the password and ends rollover together for that case. A routine overlap and an immediate revocation handle existing sessions differently.
Recap
Confirm release update 19.12 and the effective PASSWORD_ROLLOVER_TIME, then change the password to open a bounded window in which both the original and the new password can authenticate. Prove each consumer with a new physical connection and its application work, then end the rollover period. Ending the period stops the original password on fresh logins and leaves existing sessions to the drain plan.
No comments:
Post a Comment