Rotate passwords with zero down time
-
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 toMY_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 decommissionMY_USER_PROXY_1
or let the password normally expireThis 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 theconnect 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 afterTENANT_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 toTENANT_PROXY_1
and thus fail when the proxy user is rotated toTENANT_PROXY_2
I tried using roles (both as the connector and the connectee) but looks like only users are supported ( https://dba.stackexchange.com/questions/16887/is-it-possible-to-configure-oracles-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
throughTENANT_PROXY_N
and apply the grant to allN
proxy users during onboarding and manually round-robin through theN
users. Not quite as graceful, but still seems reasonableI also tried 2 level proxying (e.g.,
TENANT_PROXY_1[TENANT_PROXY[TENANT_1234]]
orTENANT_PROXY_1[TENANT_PROXY][TENANT_1234]
) but that was a no-go as wellAny thoughts on either solution? Databases have been around for decades, is there any official solution or pattern to ZDT password rotation problem?
-
Make sure you are applying patches because https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/gradual-database-password-rollover-applications.html 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.