Question: How can
I write the PL/SQL to test a password to see if it is valid
for an Oracle user? We want to use one connection but
create one Authentication form to enter username and
password and will hash that password same like oracle are
doing and we will compare . the problem is that how to hash
string same like oracle hash password. we will compare that
hashing values in .NET Application level.
How do I test for an Oracle password?
Answer: Testing for a proper
Oracle password requires the following PL/SQL.
First, note that Oracle uses the below methods to store
1. A SALT value gets generated by Oracle
(this will be generated in random by Oracle. However I
haven't figured out how this gets generated.).
Password (case-sensitive) and SALT value gets concatenated.
3. Oracle uses SHA1 hashing algorithm to generate the
hash for the concatenated value.
4. Thus 11g password
hash becomes: 'S:' + [SHA1 hash for (Password + SALT)] plus
So in this case if you have created a database user
Train1 with password Train1, then you can use the below
query to view the password hash and the salt that Oracle has
generated for that password.
SUBSTR (spare4, 3, 40) hash_password,
SUBSTR (spare4, 43, 20) salt,
WHERE name = 'TRAIN1';
Here the column hash_password is the password by which
you can check. Also It would be better if you can create a
function to check for the user password. I have created the
function here for you. I have already tested this:
OR REPLACE FUNCTION validateUser (username IN VARCHAR2,
passwd IN VARCHAR2)
lv_pwd_raw RAW (128);
lv_enc_raw RAW (2048);
lv_user_hash RAW (128);
lv_user_salt RAW (128);
lv_hash_found VARCHAR2 (300);
SELECT SUBSTR (spare4, 3, 40)
WHERE name = UPPER (username);
SUBSTR (spare4, 43, 20)
WHERE name = UPPER (username);
:= UTL_RAW.cast_to_raw (passwd) || HEXTORAW (lv_user_salt);
lv_enc_raw := sys.DBMS_CRYPTO.hash
lv_enc_raw = lv_user_hash
'An error was encountered - ' ||
SQLCODE || ' -ERROR- ' || SQLERRM);
function returns 1 if user name and password is correct,
else returns 0 if either user name or password is incorrect.
You can execute the function using select query as given
'Train1') from dual; -- This returns 1
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.