Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> ORA-28002 When Using Hash

ORA-28002 When Using Hash

From: Jim McMahon <>
Date: Sun, 12 Dec 2004 06:46:25 GMT
Message-ID: <>

I'm using Oracle 8.1.7 and trying to figure out the effect of using

ALTER USER IDENTIFIED BY password -- syntax 1


ALTER USER IDENTIFIED BY VALUES 'hashcode' -- syntax 2, single quotes

in terms of how it relates to a locally defined password verification function designated in the user's profile.

Firstly, I'm not a DBA so forgive me if I don't articulate my question properly in DBA terminology. I'm an applications programmer who was tasked with writing the password verification function later installed by our DBA.

The password verify function works properly under all conditions when a password is beng changed in the "normal" fashion using syntax 1, above. That is, for various rules put into place (min length, not same as userid, lowercase, uppercase, etc) the verification function fires, does the required checking, and raises appropriate exceptions depending on any rules that may be broken and embeds them into a generic ORA-28002 exception.

However, the DBA on my team has encountered problems when trying to migrate user passwords from a production environment to a testing environment.

When he issues the ALTER statment using syntax 2, ORA-28002 is raised (w/out embedded custom exceptions raised by the verify function).

If he issues the ALTER statement using syntax 1 using an "invalid" password (e.g. too short), the ORA-28002 contains the appropriate embedded custom message from the verify function. If he issues the ALTER statement using syntax 1 with a "valid" password, the operation succeeds.

It's obvious to me that there is something different about using the two syntaxes, but I'd be very surprised if Oracle didn't "unhash" the password before attempting to execute it's password management mechanisms. We did try a third syntax:

  ALTER USER IDENTIFIED BY VALUES "hashcode" -- note double quotes

but it didn't seem to make any difference.

Since user creation and changing of their password is done through my application, I can add one last clue in that when the password was established/last changed on the instance it's being migrated from, the double quote syntax would have been used. That is:

  CREATE USER IDENTIFIED BY "password" -- note double quotes   ALTER USER IDENTIFIED BY "password" -- note double quotes

Can anyone help me realize what's going on? Thanks in advance.

Being ordinary and nothing special is a full-time job. (Jim McMahon in real life) Received on Sun Dec 12 2004 - 00:46:25 CST

Original text of this message