Oracle recreate user without knowing password

For pure testing purpose, we will use a dummy password. The same procedure tested in Oracle 21c, high chance it will work in other versions as well.

SQL> alter user donghua2 identified by "OneTimePassword_DemoOnly";

User DONGHUA2 altered.

Retrieve the encoded password. There are parts starting with "S:" and "T:".

S:

• Total 60 characters = 30 bytes
  • Password hash (20 bytes) = sha1(password + salt (10 bytes))
  • salt (10 bytes) (B09C6257E62C93E07E10 in below example)
• Based on SHA1

T:

• From 12.1.0.2 onwards
• Total 160 characters = 80 bytes
• Based on PBKDF2-based SHA512 hashing algorithm
• Used to enforce 12c only client through "SQLNET.ALLOWED_LOGON_VERSION_SERVER".

Other password part like "H" is possible, to enforce XDB authentication.

SQL> select spare4 from user$ where name='DONGHUA2';

SPARE4
--------------------------------------------------------------------
S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10;T:F9D263BC89140FE935283431FB4D63FE2A055C3C32B85C9AB3347C38CDEE3BB374086798825867E4FF9D662B256AA22ABED4EA0B3F43A1921706E10B0EAE0953B32BA71D93E35ACD258D9C83EC9579F7

Let's recreate the user using the password hash, without knowing the password. This is the same way datapump used to recreate users.

SQL> create user donghua3 identified by VALUES 'S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10;T:F9D263BC89140FE935283431FB4D63FE2A055C3C32B85C9AB3347C38CDEE3BB374086798825867E4FF9D662B256AA22ABED4EA0B3F43A1921706E10B0EAE0953B32BA71D93E35ACD258D9C83EC9579F7';

User DONGHUA3 created.

Grant privilege to allow created user to logon database, and verify whether old password works for it:

SQL> grant create session to donghua3;
Grant succeeded.

SQL> conn donghua3/OneTimePassword_DemoOnly@pdbn1
Connected.

Further more, we can use only "S:" part of the hash to recreate the user, internally database will setup the password hash with both "S:" and "T:".

SQL> alter user donghua3 identified by VALUES 'S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10';
User DONGHUA3 altered.

SQL> conn donghua3/OneTimePassword_DemoOnly@pdbn1
Connected.