Rotate passwords with zero down time

All we need is an easy explanation of the problem, so here it is.

Big question: How to rotate passwords in an Oracle database in a zero downtime (ZDT) way?

My current thought is to rotate the users. Originally, I had MY_USER that had all the tables and such. Now, I have:

CREATE USER MY_USER NO AUTHENTICATION;
GRANT CREATE SESSION TO MY_USER;

CREATE USER MY_USER_PROXY_1 IDENTIFIED BY "abc123";
GRANT CREATE SESSION TO MY_USER_PROXY_1;
ALTER USER MY_USER GRANT CONNECT THROUGH MY_USER_PROXY_1;

When I want to rotate the password, I simply create MY_USER_PROXY_2 and give the ‘connect through’ grant to MY_USER. This way, the app can continue to create new connections until I deploy it using the new user. Because of the ‘connect through’, the new user is essentially the same as the old user so everything should continue to work without much fanfare. Afterwards, I can decommission MY_USER_PROXY_1 or let the password normally expire

This seems like a reasonable approach if there is 1 ‘physical’ user (MY_USER)

However, I am already using proxy users for multi-tenancy. Same as before, but instead of 1 user, there could be hundreds of users going through the same proxy user:

CREATE USER TENANT_PROXY_1 IDENTIFIED BY "abc123";
GRANT CREATE SESSION TO TENANT_PROXY_1;

-- During tenant onboard
CREATE USER TENANT_1234 NO AUTHENTICATION;
GRANT CREATE SESSION TO TENANT_1234;
ALTER USER TENANT_1234 GRANT CONNECT THROUGH TENANT_PROXY_1;

The issue with creating TENANT_PROXY_2 is recreating all the connect through grants. I could iterate through all the users like ‘TENANT_%’ and apply the grant that way but will always be a window of opportunity after TENANT_PROXY_2 is created and updated with the grants BUT BEFORE the app is restarted to use the new proxy user. So it would add the grant to TENANT_PROXY_1 and thus fail when the proxy user is rotated to TENANT_PROXY_2

I tried using roles (both as the connector and the connectee) but looks like only users are supported (Is it possible to configure Oracle's CONNECT THROUGH based on roles? confirms this):

ALTER USER TENANT_1234 GRANT CONNECT THROUGH TENANT_PROXY_ROLE;
GRANT ROLE TENANT_PROXY_ROLE TO TENANT_PROXY_1;
-- or
ALTER ROLE TENANT_PROXY_ROLE GRANT CONNECT THROUGH TENANT_PROXY_1;
GRANT ROLE TENANT_PROXY_ROLE TO TENANT_1234;

My only thought is to pre-create TENANT_PROXY_1 through TENANT_PROXY_N and apply the grant to all N proxy users during onboarding and manually round-robin through the N users. Not quite as graceful, but still seems reasonable

I also tried 2 level proxying (e.g., TENANT_PROXY_1[TENANT_PROXY[TENANT_1234]] or TENANT_PROXY_1[TENANT_PROXY][TENANT_1234]) but that was a no-go as well

Any thoughts on either solution? Databases have been around for decades, is there any official solution or pattern to ZDT password rotation problem?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Make sure you are applying patches because password rollover is possible since 19.12 (backported from 21c)

This allows you to update the password in the DB and have a grace period in which your applications can still connect using the older password.

Alter profile my_profile limit password_rollover_time 7;

Password my_user

Enter new password: ***

You can still use the old password for 7 days so you can just work through your application config and servers and restart them one at a time.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply