The security team wants the application account’s password rotated. It’s a one-line change:

ALTER USER app_user IDENTIFIED BY "Summer_Passw0rd2";

The new password goes into the secrets store, and the app servers pick it up as they restart. Twenty minutes later the application is down, and the error is:

ORA-01017: invalid credential or not authorized; logon denied

followed by:

ORA-28000: The account is locked

ORA-01017 on its own just means a wrong username or password. After a password change it means something more specific: some clients still have the old one. Their retries use up the account’s failed-login limit, the account locks, and from then on it refuses every login, including from the servers that already have the new password. The change was correct. The way it rolled out caused the outage.

Oracle has a feature for exactly this: gradual password rollover, in 19c from Release Update 19.12 and in 21c and later. For a window you choose, the old and the new password both work. This post shows the outage, the feature, the query that tells you which clients are still on the old password, and the two traps that come with it. All of it is reproduced in a lab.

Why one password change takes everything down

Three things combine.

Existing connections don’t notice. A password is checked only at login. Every session that was open before the change keeps working. So the change looks fine at first. The failures start later, when a connection pool recycles, a server restarts, or traffic grows and the pool opens new connections.

Not every client changes at the same moment. App servers restart one at a time. A batch job reads its password from a different file. A reporting tool has the password saved in a connection profile nobody remembers. Some of them are on the old password for minutes, some for days.

Failed logins lock the account, not the client. The profile’s FAILED_LOGIN_ATTEMPTS (10 in a stock DEFAULT profile) counts failures for the account. Once a few clients have retried with the old password, the account is LOCKED(TIMED) for PASSWORD_LOCK_TIME (1 day by default), and the correct password gets ORA-28000 too.

So the incident looks like this: a password change, a quiet period, then ORA-01017 from a few servers, then ORA-28000 from all of them.

Prove it

The lab runs Oracle Database Free in a container. It creates APP_USER on a profile with FAILED_LOGIN_ATTEMPTS 5, and audits its logins. Then it changes the password while “app servers” still log in with the old one:

password change, no rollover               result
old password, attempts 1-5                  ORA-01017: invalid credential or not authorized
old password, attempt 6                     ORA-28000: The account is locked
new password                                ORA-28000: The account is locked
connection opened before the change         still alive, still querying

Then the same change with a one-day rollover window:

password change, PASSWORD_ROLLOVER_TIME 1   result
account status                              OPEN & IN ROLLOVER
old password, logins 1-7                    connected (7 logins, limit is 5 failures)
new password                                connected

Don’t take my word for it — run it. The password-rollover lab asserts every row: ORA-01017 five times, then ORA-28000 for the old and the new password without rollover; both passwords working with it; the audit trail telling them apart; a typo still locking the account during rollover; a second change dropping the middle password; and ending the window early. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

Gradual password rollover

Rollover is a profile limit, not an option of ALTER USER. Set it on the profile the application account uses, then change the password:

ALTER PROFILE app_prof LIMIT password_rollover_time 1;   -- days: 1/24 = one hour

ALTER USER app_user IDENTIFIED BY "Summer_Passw0rd2";

From that moment until the window ends, both passwords log in, and DBA_USERS shows it:

SELECT username, account_status FROM dba_users WHERE username = 'APP_USER';

USERNAME   ACCOUNT_STATUS
APP_USER   OPEN & IN ROLLOVER

The window is in days, from one hour (1/24) to 60 days; anything outside that is refused with ORA-02377 (the lab checks 30 minutes and 61 days). 0, the DEFAULT profile’s value, turns it off. The window starts at the password change, so a profile changed after the password doesn’t help that change.

Pick a window longer than your slowest client takes to pick up the new password, including the weekly batch job and the server that only restarts at patch time. A day is short. A week is common.

⤢ Enlarge

Who is still on the old password?

This is the question the rollover window gives you time to answer, and the database can answer it. With logins audited, every unified audit record for a successful login says which password it used, in AUTHENTICATION_TYPE:

(TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=56796)));
(LOGON_INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI)));

12C-OLD is the old password, 12C-NEW the new one. (That’s what the lab’s 26ai database records; check that your release writes LOGON_INFO before you rely on it. If it doesn’t, the same query without the verifier still lists every host and program that logged in since the change, which is your list of clients to check.) Count them since the change:

SELECT regexp_substr(authentication_type, 'VERIFIER=([^)]*)', 1, 1, NULL, 1) AS verifier,
       userhost, client_program_name, COUNT(*) AS logons
FROM   unified_audit_trail
WHERE  dbusername = 'APP_USER'
AND    action_name = 'LOGON'
AND    return_code = 0
AND    event_timestamp >= (SELECT CAST(password_change_date AS TIMESTAMP)
                           FROM dba_users WHERE username = 'APP_USER')
GROUP  BY regexp_substr(authentication_type, 'VERIFIER=([^)]*)', 1, 1, NULL, 1),
          userhost, client_program_name
ORDER  BY verifier, userhost;

In the lab (all clients on one host, so grouped by verifier alone), after seven logins with the old password and one with the new:

VERIFIER=12C-NEW LOGONS=1
VERIFIER=12C-OLD LOGONS=7

Every -OLD row is a client you still have to update, with its host and program next to it. This needs logins to be audited. The lab creates a policy for the account:

CREATE AUDIT POLICY app_logons ACTIONS LOGON;
AUDIT POLICY app_logons BY app_user;

In the lab’s database nothing audited logins until that policy existed; check AUDIT_UNIFIED_ENABLED_POLICIES on yours. Successful logins can be a lot of rows for a busy pool, so scope the policy to the accounts you’re rotating, and enable it before the change. The same trail answers the outage question, too: without rollover, the failed logins showed up as ORA-01017 x5 and ORA-28000 x2, with the client host, and that’s how you find the server that locked the account. See unified auditing without the noise for keeping policies like this small.

Ending the window

When the audit trail shows no more -OLD logins for long enough to cover your slowest client, end the window instead of waiting it out:

ALTER USER app_user EXPIRE PASSWORD ROLLOVER PERIOD;

In the lab the status goes back to OPEN, the old password gets ORA-01017, the new one connects, and a connection opened earlier with the old password is still alive and querying. Ending the rollover, like the change itself, only affects new logins.

That’s also the reason to end it deliberately. While the window is open, anyone with the old password can still log in. If the rotation was because the old password leaked, a long window keeps the leak open, so pick the window to match the reason for the change.

Two traps

A wrong password still counts. Rollover accepts two passwords, not any password. In the lab, five logins with a typo during the rollover window got ORA-01017, and then the account was locked: the new password and the old one both got ORA-28000. A client with a mistyped secret locks the account just as it did before. Rollover removes the outage caused by timing; it doesn’t remove the lockout.

A second change drops the middle password. If the password is changed again inside the window (a rerun of the rotation job, a second ticket, someone “fixing” a typo in the new password), the window continues, but only the original and the latest password work:

original password (Spring)        connected
first new password (Summer)       ORA-01017
latest password (Autumn)          connected
account status                    OPEN & IN ROLLOVER

Any client that already moved to the middle password is now failing, and the account status looks exactly the same as it did a minute earlier. If you need a second change, let the first rollover finish (or end it) first.

The rotation, step by step

1. Know the clients. Audit logins for the account for a while before the change. Each distinct USERHOST / CLIENT_PROGRAM_NAME is a client that needs the new password.
2. Set the window on the account’s profile: ALTER PROFILE ... LIMIT password_rollover_time 7. If the profile is shared with accounts you don’t want to roll over, give this account its own profile.
3. Change the password once. ALTER USER ... IDENTIFIED BY .... Check OPEN & IN ROLLOVER.
4. Push the new password to every client, at whatever pace your deployments allow.
5. Watch the audit trail until there are no -OLD logins, and chase the ones that remain.
6. End the window: ALTER USER ... EXPIRE PASSWORD ROLLOVER PERIOD.

If the account is already locked when you start reading this: ALTER USER app_user ACCOUNT UNLOCK, find the client that’s still failing (the audit trail has its host, if failed logins are audited), and fix it before it locks the account again.

What teams get wrong

• Changing the password and the clients “at the same time.” There’s no such moment. Some client is always last, and until it moves, it’s failing logins against a shared limit.
• Setting the rollover limit after the password change. The window starts at the change. Set the profile first.
• Reading OPEN & IN ROLLOVER as “the old password works.” After a second change it’s the original password that works, not the one most clients were just given.
• Raising FAILED_LOGIN_ATTEMPTS to UNLIMITED so rotations stop locking the account. That removes a defence against password guessing for good, to fix a problem that lasts an hour. Use rollover, and keep the limit (see the hardening checklist).
• Leaving the window open for weeks after everyone has moved. It’s two valid passwords. End it once the audit trail is clean.

Frequently asked questions

Why do I get ORA-01017 after changing a user's password?

Because some client is still logging in with the old password. Sessions opened before the change keep working, so the failures appear later, when a connection pool opens new connections or a server restarts. Each failed login counts against the profile's FAILED_LOGIN_ATTEMPTS, and once the limit is reached the account locks and every login, including ones with the new password, fails with ORA-28000.

What is gradual password rollover in Oracle?

A profile limit, PASSWORD_ROLLOVER_TIME, available in Oracle 19c from Release Update 19.12 and in 21c and later. When an account's password is changed, both the old and the new password are accepted for that many days, so clients can move to the new password one at a time without login failures. The account status shows OPEN & IN ROLLOVER during the window. The window can be from one hour (1/24) to 60 days; 0 turns it off.

How do I find which clients still use the old password during a rollover?

Audit logins for the account (CREATE AUDIT POLICY ... ACTIONS LOGON and AUDIT POLICY ... BY the user). Each successful login's AUTHENTICATION_TYPE in UNIFIED_AUDIT_TRAIL includes LOGON_INFO with VERIFIER=12C-OLD or VERIFIER=12C-NEW. Group the logins since the password change by verifier, USERHOST and CLIENT_PROGRAM_NAME; every OLD row is a client still to update. This was verified on Oracle 26ai; on other releases, check that LOGON_INFO is recorded.

How do I end a password rollover period early?

Run ALTER USER username EXPIRE PASSWORD ROLLOVER PERIOD. The account status returns to OPEN and only the new password works for new logins. Sessions that are already connected, including ones that logged in with the old password, are not disconnected.

Does password rollover prevent ORA-28000 account locked?

Only for the old password. During the rollover window the old and new passwords both log in, so clients that haven't moved don't fail. A wrong password still fails with ORA-01017 and still counts toward FAILED_LOGIN_ATTEMPTS, and in the companion lab five wrong logins during the window locked the account for both passwords.

What happens if I change the password twice during a rollover?

The rollover window continues, but only the original password (from before the first change) and the latest password are valid. The password in between stops working immediately, even though the account status still shows OPEN & IN ROLLOVER. Clients that had already moved to the middle password get ORA-01017.

ORA-01017 after a password change isn’t a mystery about the password. It’s a rollout problem: some client is always last, and without a window, the last client locks out the first. Give the account a rollover window, change the password once, watch the audit trail until nobody is on the old one, and close the window yourself. It belongs with the other account controls in the hardening checklist and with the connection errors that look like outages, like ORA-12514. Then prove it the way that ends the argument — with the password-rollover lab, where the same password change locks the account without rollover and causes zero failed logins with it.