Password rolling change before Oracle 21c

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.

Proxy User

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 DEMO@atp1_tp> grant create session to APP2020 identified by "2020 was a really Bad Year!";
Grant succeeded.

19:28:50 DEMO@atp1_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 DEMO@atp1_tp> connect APP2020/"2020 was a really Bad Year!"@atp1_tp
Connected.
19:28:52 APP2020@atp1_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 APP2020@atp1_tp> connect APP2020[DEMO]/"2020 was a really Bad Year!"@atp1_tp
Connected.

19:28:53 DEMO@atp1_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.

No authentication

19:28:54 ADMIN@atp1_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 ADMIN@atp1_tp> show user
USER is "ADMIN"

19:28:53 ADMIN@atp1_tp> grant create session to APP2021 identified by "Best Hopes for 2021 :)";
Grant succeeded.

19:28:54 ADMIN@atp1_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 ADMIN@atp1_tp> connect APP2020[DEMO]/"2020 was a really Bad Year!"@atp1_tp
Connected.

19:28:55 DEMO@atp1_tp> show user
USER is "DEMO"

19:28:55 DEMO@atp1_tp> connect APP2021[DEMO]/"Best Hopes for 2021 :)"@atp1_tp
Connected.

19:28:56 DEMO@atp1_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 ADMIN@atp1_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 ADMIN@atp1_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 ADMIN@atp1_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 DEMO@atp1_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.