This post is also available in:
Português
Oracle Database 21c introduced the new feature called "Gradual Database Password Rollover". This allows the database password of the application user to be altered while allowing the older password to remain valid for the time specified by the PASSWORD_ROLLOVER_TIME limit (PROFILE parameter).
With this new feature, a password of an application can be changed without having to schedule a downtime, which is great..
However, you may be wondering how oracle internally store and validate the old and new hashes in the dictionary. In this article, I will investigate and show how it works.
Getting started
First of all, to recap, we have the user_history$ table that was introduced some time back to keep the old user password hashes for reuse control.
Before we start, let me change the DEFAULT profile and enable the ROLLOVER feature:
SQL> select LIMIT from dba_profiles where PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_ROLLOVER_TIME'; LIMIT -------- 0 SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_ROLLOVER_TIME 1/24; Profile altered. SQL> select LIMIT from dba_profiles where PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_ROLLOVER_TIME'; LIMIT -------- .0416
The PASSWORD_ROLLOVER_TIME is given in number of days. Giving 1/24 is 1 hour, meaning the user will still be able to use his old and new passwords for this period.
Now, let's start creating a new user and checking that:
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select SYSDATE FROM DUAL;
SYSDATE
-------------------
2020-12-14 20:42:49
SQL> create user C##DBA identified by "welcome1";
User created.
SQL> grant create session, dba to C##DBA;
Grant succeeded.
SQL> select user_id from dba_users where username='C##DBA';
USER_ID
----------
117
SQL> select USER#,PASSWORD_DATE from user_history$ where USER#=117 order by PASSWORD_DATE desc;
USER# PASSWORD_DATE
---------- -------------------
117 2020-12-14 20:43:10
SQL> select row_number() over (order by PASSWORD_DATE asc) as seqnum, password from user_history$ where USER#=117 order by seqnum asc;
SEQNUM PASSWORD
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
SQL> select replace(spare4,';',chr(10)) spare4 from user$ where USER#=117;
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:F0D6ED816568FCC320228B39CF2101E7D7F151A25217196D2FBA0E58E854
T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
The "S" entry in SPARE4 column represents the SHA1 hash (11g authentication) while the "T" entry is the SHA2 (12c authentication). We can note that the value stored in the user_history$ (control password reuse) has only the SHA2 entry.
PS: When I say "SHA2", please read "PBKDF2-based SHA512 hashing algorithm".
Let's change the user password and see what happens:
SQL> alter user C##DBA identified by "welcome2"; User altered. SQL> conn C##DBA/welcome1 Connected. SQL> conn C##DBA/welcome2 Connected.
So after changing the password, you can note I still could connect using both password. How is it possible internally?
SQL> select USER#,PASSWORD_DATE from user_history$ where USER#=117 order by PASSWORD_DATE desc;
USER# PASSWORD_DATE
---------- -------------------
117 2020-12-14 20:47:25
117 2020-12-14 20:43:10
SQL> select row_number() over (order by PASSWORD_DATE asc) as seqnum, password from user_history$ where USER#=117 order by seqnum asc;
SEQNUM PASSWORD
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
2 T:2F85521FC89E01B445B622C6D4686062BCCD0AF8B030871946F85423049EB0576D3B3CDCDAB0936D299223E141B0C0889A0540443ED3857B572E54AA1A23A4E773B783F8C84531ECC4FBCF5DFF174510
SQL> select replace(spare4,';',chr(10)) spare4 from user$ where USER#=117;
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:F0D6ED816568FCC320228B39CF2101E7D7F151A25217196D2FBA0E58E854
T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
t:DF10CCD2DC546657A975E81604E99D48BD1A2729F804EA811B2111E43D64021CCA4027588D61ED9C718DAECEA5940C875CF6659049062AABA571073AD790774F451CC4B224409EFA64D5E5FBF6BB4EB5
V:2F85521FC89E01B445B622C6D4686062BCCD0AF8B030871946F85423049EB0576D3B3CDCDAB0936D299223E141B0C0889A0540443ED3857B572E54AA1A23A4E773B783F8C84531ECC4FBCF5DFF174510
s:7EE335579302B734BD399D2F342924862A8870F75217196D2FBA0E58E854
U:5B1D91705F57E211D52F9F7546D1E6EF85130600F3CC762601AF5C2A8657
As you can note, SPARE 4 column has now 4 new attributes (s, t, U, V). Before we only had S and T uppercase. Matching their values with the past execution, we can find out that:
- S: old SHA1 hash
- T: old SHA2 hash
- V: new SHA2 hash
- s, t, U: ???
Let's change the password again and see what happens:
SQL> alter user C##DBA identified by "welcome3"; SQL> conn C##DBA/welcome1 Connected. SQL> conn C##DBA/welcome2 ERROR: ORA-01017: invalid username/password; logon denied SQL> conn C##DBA/welcome3 Connected.
Now I could only connect using the oldest one and the newest one. The one used in the middle is not working. Why?
SQL> select USER#,PASSWORD_DATE from user_history$ where USER#=117 order by PASSWORD_DATE desc;
USER# PASSWORD_DATE
---------- -------------------
117 2020-12-14 20:51:14
117 2020-12-14 20:47:25
117 2020-12-14 20:43:10
SQL> select row_number() over (order by PASSWORD_DATE asc) as seqnum, password from user_history$ where USER#=117 order by seqnum asc;
SEQNUM PASSWORD
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
2 T:2F85521FC89E01B445B622C6D4686062BCCD0AF8B030871946F85423049EB0576D3B3CDCDAB0936D299223E141B0C0889A0540443ED3857B572E54AA1A23A4E773B783F8C84531ECC4FBCF5DFF174510
3 T:B5019D0C1BEE94E5AA794DF474349F18DAFF533C791C02B9C7391B39B3D1E438B1901CF8C0565FDE9459FBBEE4B89C661C64945F4F80B7561F47BD5D7E19B243CA3487E88F01ED5924DB4014AA2850CD
SQL> select replace(spare4,';',chr(10)) spare4 from user$ where USER#=117;
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:F0D6ED816568FCC320228B39CF2101E7D7F151A25217196D2FBA0E58E854
T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
t:B761C2BE40827859924A320C2485B9CE850D39AB8343F2FAEBBDBD09916D63B2F6912AEE24FA4690CEC4742E5836D7315BC14C2FB78C5DBAB89D6D0DFAF41794451CC4B224409EFA64D5E5FBF6BB4EB5
V:B5019D0C1BEE94E5AA794DF474349F18DAFF533C791C02B9C7391B39B3D1E438B1901CF8C0565FDE9459FBBEE4B89C661C64945F4F80B7561F47BD5D7E19B243CA3487E88F01ED5924DB4014AA2850CD
s:9DB5F1A429351277A882362544C35A48B83B9F015217196D2FBA0E58E854
U:DAD93E9BEEABC8997868EF2C430AE18B6D62DF8930CAF6BAC606D428A708
So the "S" and "T" entries are still as before, with the old SHA1 and SHA2 (welcome1 password) entry. All the others hashes changed. And we can still confirm that "V" has the new SHA2 hash matching with the user_history$ table.
Let's now expire the rollover period and see what happens:
SQL> ALTER USER C##DBA EXPIRE PASSWORD ROLLOVER PERIOD; SQL> conn C##DBA/welcome1 ERROR: ORA-01017: invalid username/password; logon denied SQL> conn C##DBA/welcome2 ERROR: ORA-01017: invalid username/password; logon denied SQL> conn C##DBA/welcome3 Connected.
As expected, the 2 old password versions are not working anymore (the second was already failing when I created the third). Now checking once again the dictionary:
SQL> select USER#,PASSWORD_DATE from user_history$ where USER#=117 order by PASSWORD_DATE desc;
USER# PASSWORD_DATE
---------- -------------------
117 2020-12-14 20:51:14
117 2020-12-14 20:47:25
117 2020-12-14 20:43:10
SQL> select row_number() over (order by PASSWORD_DATE asc) as seqnum, password from user_history$ where USER#=117 order by seqnum asc;
SEQNUM PASSWORD
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 T:00D396D65A0A899837CA952D3122C6CC146F8883D2B307EE744FE23E53EF489307721CD9D726F9075593807D6E470A69824D63AF0CFE2119B012CA90FE7A4C7E451CC4B224409EFA64D5E5FBF6BB4EB5
2 T:2F85521FC89E01B445B622C6D4686062BCCD0AF8B030871946F85423049EB0576D3B3CDCDAB0936D299223E141B0C0889A0540443ED3857B572E54AA1A23A4E773B783F8C84531ECC4FBCF5DFF174510
3 T:B5019D0C1BEE94E5AA794DF474349F18DAFF533C791C02B9C7391B39B3D1E438B1901CF8C0565FDE9459FBBEE4B89C661C64945F4F80B7561F47BD5D7E19B243CA3487E88F01ED5924DB4014AA2850CD
SQL> select replace(spare4,';',chr(10)) spare4 from user$ where USER#=117;
SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:DAD93E9BEEABC8997868EF2C430AE18B6D62DF8930CAF6BAC606D428A708
T:B5019D0C1BEE94E5AA794DF474349F18DAFF533C791C02B9C7391B39B3D1E438B1901CF8C0565FDE9459FBBEE4B89C661C64945F4F80B7561F47BD5D7E19B243CA3487E88F01ED5924DB4014AA2850CD
What is curious is that now I'm back to only "S" and "T" entries.
- The "S" (current SHA1) received what was on "U"
- The "T" (current SHA2) received what was on "V"
Now we can conclude that:
- S: old SHA1 hash
- T: old SHA2 hash
- U: new SHA1 hash
- V: new SHA2 hash
What about s and t (lowercase)?
Using john for password recovery and trying to use the 3 passwords versions I used in this article, surprisingly they both had the latest used password (welcome3 password).
Why Oracle keeps 2 password formats for the same password?
If you note, the "s" and "S" have both the same SALT (last 20 characters) for the SHA1 and "t" and "T" also have the same SALT (last 32 characters) for the SHA2.
So is seems that the reason Oracle keeps the new hashes using the same salt is to use a single salt during the authentication flow . That being said, it will be transparent for any client to support this new feature. All the server needs to do is compare the provided hash with the 2 possible options. And once the rollover period is over, the password using the new SALT will be used for security.
So, in summary:
- S: old SHA1 hash
- T: old SHA2 hash
- U: new SHA1 hash
- V: new SHA2 hash
- s: new SHA1 hash using old SALT
- t: new SHA2 hash using old SALT
The SALT of "s" and "S" (in the case of a 11g authentication), or "t" and "T" (in the case of a 12c authentication) will be the one processed during the client/server authentication on the rollover period and once it expires, the "U" and "V" entries (with new SALT) will replace the "S" and "T" entries.
That's it!
Have you enjoyed? Please leave a comment or give a 👍!
