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
password
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.).
2.
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
[SALT].
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.
SELECT
SUBSTR (spare4, 3, 40) hash_password,
SUBSTR (spare4, 43, 20) salt,
spare4
FROM sys.user$
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:
CREATE
OR REPLACE FUNCTION validateUser (username IN VARCHAR2,
passwd IN VARCHAR2)
RETURN NUMBER
IS
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);
BEGIN
SELECT SUBSTR (spare4, 3, 40)
INTO lv_user_hash
FROM sys.user$
WHERE name = UPPER (username);
SELECT
SUBSTR (spare4, 43, 20)
INTO lv_user_salt
FROM sys.user$
WHERE name = UPPER (username);
lv_pwd_raw
:= UTL_RAW.cast_to_raw (passwd) || HEXTORAW (lv_user_salt);
lv_enc_raw := sys.DBMS_CRYPTO.hash
(lv_pwd_raw, DBMS_CRYPTO.hash_sh1);
lv_hash_found :=
UTL_RAW.cast_to_varchar2 (lv_enc_raw);
IF
lv_enc_raw = lv_user_hash
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
WHEN OTHERS
THEN
raise_application_error (
-20001,
'An error was encountered - ' ||
SQLCODE || ' -ERROR- ' || SQLERRM);
END;
This
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
below:
select validateUser('Train1',
'Train1') from dual; -- This returns 1
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
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
Note:
This Oracle
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
Oracle forum.
Verify
experience!
Anyone
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
publish
their Oracle
qualifications.
Errata?
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
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
