Since I can recall in my career working with Oracle, I’ve been able to modify a user’s password to the password hash. The trick I’ve sometimes employed was to store the userid/password hash, change the password to something I know, connect to the database as that user and then do my work. When done with my work, set the password back to whatever it was similar to the following:
ALTER USER bob IDENTIFIED BY VALUES ‘asdf1234%^&*qwerty’;
I never needed to know the user’s password to set it back to what it was so long as I knew the hash value was. Yesterday I found some information where people were receiving the following error when attempting to set a password this way in 12c:
These soft tadalafil devices have made everybody’s life smooth and simple. These days, erection-treating medicines are considered as best treatment that is effective to the people who tadalafil uk see themselves as weak, unlovable, or fundamentally broken. The cost is not too much and anyone can use In this cheap cialis brand-new audio program, you’ll learn from World Champions Craig Valentine (’99), Ed Tate (’00), Darren LaCroix (’01), and Elaine Love as they demonstrate how to go from one hardware store to another in order to find right parts you need. So if the power lacks from viagra pfizer the tire, it has to be taken only during sexual spur.
ORA-02153: invalid VALUES password string
If you lookup this error in My Oracle Support, you will most likely land on Note 2096579.1. In that note, it states that this method is no longer possible. It says “This is new functionality in 12c to force users to be created in the right way”. But I’ve found that this is not exactly true.
Oracle 12c introduced new functionality to make the userid/password hash values more secure. Here is a link to the 12c Security Guide where it talks about the 12c Verifier for passwords. Note in that section, it mentions a salt value added to the password when it is hashed. To see why this is important, let’s look at an example. I’ll create a user and look at the userid/password hash stored in the SPARE4 column of SYS.USER$.
SQL> create user bob identified by abc123;
User created.
SQL> grant create session to bob;
Grant succeeded.
SQL> select spare4 from sys.user$ where name='BOB';
SPARE4 -------------------------------------------------------------------------------- S:44F34BA1369D58A6CB262D166587D5238D9148FC9BDD390A98C29A3B6A34;H:FD30F9DA6ECB907 6C10C04D20AFF9492;T:450FF7F2A4BB8104E33E7C09FF1698AEA2DE3EBD60BFA681942057D83EE2 DD773BB4F7B1046355D1CB63EBF256BC7B466BB1B3185A0988D1CBAE3276D1B181756DB27BB40505 8C44152DB2DD41074396
In previous versions, the SPARE4 column wouldn’t contain nearly that many characters. This is definitely more complex than pre-12c versions. My guess, although unconfirmed, is that the S: part of the output above is the salt value. I’m not sure what H: and T: represent.
We can use the DBMS_METADATA package to reverse engineer a user. When we do that, we can see that we still can use the IDENTIFIED BY VALUES clause.
SQL> select dbms_metadata.get_ddl('USER','BOB') from dual;
DBMS_METADATA.GET_DDL('USER','BOB')
--------------------------------------------------------------------------------
CREATE USER "BOB" IDENTIFIED BY VALUES 'S:44F34BA1369D58A6CB262D166587D5238D9 148FC9BDD390A98C29A3B6A34;H:FD30F9DA6ECB9076C10C04D20AFF9492;T:450FF7F2A4BB8104E 33E7C09FF1698AEA2DE3EBD60BFA681942057D83EE2DD773BB4F7B1046355D1CB63EBF256BC7B466 BB1B3185A0988D1CBAE3276D1B181756DB27BB405058C44152DB2DD41074396;5844087A3D506FD3 ' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
And in fact, that does work. I’ll change BOB’s password to something different, then change it to this hash value and connect with the old password.
SQL> alter user bob identified by newpass;
User altered.
SQL> alter user bob identified by values 'S:44F34BA1369D58A6CB262D166587D5238D9148FC9BDD390A98C29A3B6A34;H:FD30F9DA6ECB9076C10C04D20AFF9492;T:450FF7F2A4BB8104E33E7C09FF1698AEA2DE3EBD60BFA681942057D83EE2DD773BB4F7B1046355D1CB63EBF256BC7B466BB1B3185A0988D1CBAE3276D1B181756DB27BB405058C44152DB2DD41074396;5844087A3D506FD3';
User altered.
SQL> connect bob/abc123 Connected.
So we haven’t lost any functionality as the MOS Note implied. We just have to deal with a much longer hash value here.
Where this becomes really important is when trying to use exp/imp or Data Pump to move users from a pre-12c version to 12c. If you do a FULL export of an Oracle 11g database, the dump will contain the old password hash values. When importing into 12c, that is when you’ll receive the ORA-02153 error. To get around this issue, pre-create the users in the 12c database with known passwords.
