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

ALTER USER IDENTIFIED BY password -- syntax 1

versus

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.
jp_mcmahon (AT) hotmail (DOT) com (Jim McMahon in real life)

"Jim McMahon" <jp_mcmahon (AT) hotmail (DOT) com> a écrit dans le message de
news:41bbe93e.109107601 (AT) nntp (DOT) charter.net...
Error 28002 is not related to password verification function but to password expiration:
ORA-28002 the password will expire within n days
This is error 28003 that is related to password function:
ORA-28003 password verification for the specified password failed

So, use (correct) error message instead of (wrong) error number,
we have not always an error messages book at hand.

To answer your question, if password verification function is active then
the error is always fire as Oracle is unable to check the hash value.
There is no way to "unhash". Hashing is one way process, you can't go back
and retrieve the original value.

Btw, is there anyone thinking that enabling the password function creates a hole
in security as then Oracle also stores the current password in a non-hash form?

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> wrote:

<snip>
Thank you for responding and especially for pointing out that I
referenced the wrong error number. My apologies for doing so. I
should have been more careful.

That Oracle can't run password verification when using the hash value
syntax is important to know.

I guess we'll stick with what I originally recommended to my DBA, of
turning off the password verification, changing the password, and then
turning it back on.

Thanks again for your help. I'll be interested in seeing what others
have to say about your final question, but I'm not qualified to
comment.
Being ordinary and nothing special is a full-time job.
jp_mcmahon (AT) hotmail (DOT) com (Jim McMahon in real life)

Michel, to the best of my knowledge Oracle does not store the original
password in unhashed form. Why would you think it does?
HTH -- Mark D Powell --

"Mark D Powell" <Mark.Powell (AT) eds (DOT) com> a écrit dans le message de
news:1102888481.359209.158580 (AT) c13g2000cwb (DOT) googlegroups.com...
I think it does because when you activate the password verification function
and change the password of a user, Oracle gives you the _old_ and new
passwords in clear form.
If it can do that then, i think, it stores the old password somewhere in
an unhashed form.

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> wrote

Michel,

I too agree with Mark. Oracle does not store the old password in clear text form. If password history is enabled, then
oracle stores the password hash.
You seem to be referring to the password verification function which requires the old password to be
supplied to it.
In that case, the old password needs to be supplied by the user when changing the password.
In sqlplus this would be done by the "password" command.


Anurag

"Anurag Varma" <avdbi (AT) hotmail (DOT) com> a écrit dans le message de
news:mWpvd.7$DQ3.4 (AT) twister (DOT) nyroc.rr.com...
Yes, i was talking about the later and i missed that you have to provide the old passord.
I thought Oracle provides it automatically when you execute an "alter user ... identified by".

In addition, for those like me that didn't know it, with "alter user" you can provide the old
password with the "replace" clause:
alter user ... identified by <newpsw> replace <oldpsw>;
but be aware that the old password is only used/checked if you change your own password
_and_ if you don't have the "alter user" system privilege.

Many thanks for the clarification.
Michel Cadot