By Franck Pachot
You may have read about Gradual Password Rollover usage from Mouhamadou Diaw and about some internals from Rodrigo Jorge. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems apparently because of a bug when using SQL*Net native encryption). But your production is not yet in 21c anyway. However, here is how you can achieve a similar goal in 12c,18c or 19c: be able to connect with two passwords for the time window where you are changing the password in a rolling fashion in the application server configuration.
If your application still connects with the application owner, you do it wrong. Even when it needs to be connected in the application schema by default, and even when you can’t to an “alter session set current_schema” you don’t have to use this user for authentication. And this is really easy with proxy users. Consider the application owner as a schema, not as a user to connect with.
My application is in schema DEMO and I’ll not use DEMO credentials. You can set an impossible password or, better, in 18c, set no password at all. I’ll use a proxy user authentication to connect to this DEMO user:
19:28:49 [email protected]_tp> grant create session to APP2020 identified by "2020 was a really Bad Year!"; Grant succeeded. 19:28:50 [email protected]_tp> alter user DEMO grant connect through APP2020; User DEMO altered.
The APP2020 user is the one I’ll use. I named it 2020 because I want to change the credentials every year and, as I don’t have the gradual rollover password feature, this means changing the user to connect with.
19:28:50 [email protected]_tp> connect APP2020/"2020 was a really Bad Year!"@atp1_tp Connected. 19:28:52 [email protected]_tp> show user USER is "APP2020"
This user can connect as usual, as it has the CREATE SESSION privilege. There is a way to prevent this and allow PROXY ONLY CONNECT, but this is unfortunately not documented (Miguel Anjo has written about this) so better not using it.
However, the most important is:
19:28:52 [email protected]_tp> connect APP2020[DEMO]/"2020 was a really Bad Year!"@atp1_tp Connected. 19:28:53 [email protected]_tp> show user USER is "DEMO"
With proxy connection, in addition to the proxy user credentials I mention the final user I want to connect to, though this proxy user. Now I’m in the exact same state as if I connected with the DEMO user.
19:28:54 [email protected]_tp> alter user DEMO no authentication; User DEMO altered.
As we don’t connect through this user anymore (and once I’m sure no application uses it) the best is to set it with NO AUTHENTICATION.
New proxy user
Now that the application uses this APP2020 for months, I want to change the password. I’ll add a new proxy user for that:
19:28:54 [email protected]_tp> show user USER is "ADMIN" 19:28:53 [email protected]_tp> grant create session to APP2021 identified by "Best Hopes for 2021 :)"; Grant succeeded. 19:28:54 [email protected]_tp> alter user DEMO grant connect through APP2021; User DEMO altered.
Here I have another proxy user that can be used to connect to DEMO, in addition to the existing one
19:28:54 [email protected]_tp> connect APP2020[DEMO]/"2020 was a really Bad Year!"@atp1_tp Connected. 19:28:55 [email protected]_tp> show user USER is "DEMO" 19:28:55 [email protected]_tp> connect APP2021[DEMO]/"Best Hopes for 2021 :)"@atp1_tp Connected. 19:28:56 [email protected]_tp> show user USER is "DEMO"
During this time, I can use both credentials. This gives me enough time to change all application server configuration one by one, without any downtime for the application.
Lock previous account
19:30:00 [email protected]_tp> select username,account_status,last_login,password_change_date,proxy_only_connect from dba_users where username like 'APP____'; USERNAME ACCOUNT_STATUS LAST_LOGIN PASSWORD_CHANGE_DATE PROXY_ONLY_CONNECT ___________ _________________ ________________________________________________ _______________________ _____________________ APP2020 OPEN 27-DEC-20 07.28.55.000000000 PM EUROPE/ZURICH 27-DEC-20 N APP2021 OPEN 27-DEC-20 07.28.56.000000000 PM EUROPE/ZURICH 27-DEC-20 N
After a while, I can validate that the old user is not used anymore. If you have a connection recycling duration in the connection pool (you should) you can rely on last login.
19:30:00 [email protected]_tp> alter user APP2020 account lock; User APP2020 altered.
Before dropping it, just lock the account, easier to keep track of it and unlock it quickly if anyone encounters a problem
19:30:00 [email protected]_tp> connect APP2020[DEMO]/"2020 was a really Bad Year!"@atp1_tp Error starting at line : 30 File @ /home/opc/demo/tmp/proxy_to_rollover.sql In command - connect ... Error report - Connection Failed USER = APP2020[DEMO] URL = jdbc:oracle:thin:@atp1_tp Error Message = ORA-28000: The account is locked. Commit
If someone tries to connect with the old password, he will know that the user is locked.
19:30:01 @> connect APP2021[DEMO]/"Best Hopes for 2021 :)"@atp1_tp Connected. 19:30:02 [email protected]_tp> show user USER is "DEMO"
Once the old user locked, only the new one is able to connect, with the new user credentials. As this operation can be done with no application downtime, you can do it frequently. From a security point of view, you must change passwords frequently. For end-user passwords, you can set a lifetime, and grace period. But not for system users as the warning may not be cached. Better change them proactively.