2

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • Well, I'll be. We are using OCI ATP. Let me see what minor version our instance is on. I was told the free tier uses 21c but production uses 19c and they told me twice 'we have no current plans to move to 21c'. Also, is this a 'new' feature with last couple years? I mean, how have databases existed for decades and people only thought of this now?
    – Dave
    Commented May 19, 2022 at 18:08
  • Both answers were helpful, but there can only be one and you won by three minutes, so marking this answer as the accepted one
    – Dave
    Commented May 23, 2022 at 16:38
  • Old passwords is something that security audits will flag and they are being increasingly necessary as cyber security becomes more and more important. Application architectures have become bigger and bigger in order to provide high availability. I imagine that clients (the big ones with deep pockets and massive infrastructure) became increasingly frustrated about how they have to take their entire "highly available" system down just to make a simple change in order to pass an audit.
    – Andrew Sayer
    Commented May 23, 2022 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.