Kannan Arumugam, March 21, 2001 - 7:10 pm UTC
Thanks
A reader, April 23, 2001 - 8:42 am UTC
just what i was looking for. Thanks a lot.
Great
Rajashekar Vellore Natt, June 01, 2001 - 11:45 pm UTC
Great Job Tom. U made my job easy.
USE FUL
RAMANA, June 15, 2001 - 4:55 am UTC
ALL THE CASES CAN BE USED
DEPENDING ON THE REQUIREMENT
Very good
Rajesh George, June 18, 2001 - 9:24 am UTC
Thanks
Thanks
Ariel, June 19, 2001 - 9:34 am UTC
It was just that I need.
It solved all custom user authentification problem
Ade Realdy, June 21, 2001 - 9:03 am UTC
Thanks very much
Cool one
Gururaj Kulkarni, June 26, 2001 - 3:11 pm UTC
Thanks Tom
thanks, i look for it for a long time.
michaelzhang, July 13, 2001 - 3:45 am UTC
Password encryption & Decryption
Vijay Fernando, July 24, 2001 - 4:45 am UTC
Hey Tom,
Great stuff regarding this encryption and decryption !!! Thanks a lot friend!
Regds,
Vijay
Greate solution!
dulimin, September 03, 2001 - 9:43 pm UTC
Greate solution!
dulimin, September 03, 2001 - 9:44 pm UTC
Great help
Anjani Shetty, September 10, 2001 - 3:27 am UTC
Thank you, Thank YOU, THANK YOU
Paul Hefford, September 19, 2001 - 4:01 am UTC
That information is just super! Thanks!
I need a clarification
Mrs.Srikanth, November 27, 2001 - 7:04 am UTC
I would like to know whether it is possible to decrypt the password when it is encrypted using Hashing algo (DBMS_UTILITY.get_hash_value) or is there any method in which I can achieve it
November 27, 2001 - 7:50 am UTC
Ummm, sorry I thought that was clear from the statement:
....
Well, most passwords are not
stored encrypted, but rather they are stord HASHED or DIGESTED. For example, in
Oracle, the password is not encrypted (that would imply there is a decrypt but
there is not). Instead, to validate a username/password we take them, plus some
magic "salt" and hash it. This results in a fixed length string of some bytes
of data. We compare that to the stored hash and if they match -- you are in.
If not -- you are not.
.......
A hash or digest is NOT reversible (be neat if it were -- it would be the universal compression algorithm of choice!!! ). Two different strings can (and will sometime) hash to the same exact value. We are mapping a potentially infinite space (the set of all strings) into a very finite space (the size of our hash). They are not reversible -- they do not have to be reversible. All we need to do is given a username and password verify if that username and password is valid, hashing does this very well and very securely (for the simple reason that it is NOT reversible)
Thanks Tom,
Andre Whittick Nasser, November 27, 2001 - 8:46 am UTC
Living and learning...
This last consideration was very interesting...
Thanks
Raj, November 27, 2001 - 7:22 pm UTC
Hats off to Tom !!!
Works right but one more thing
Godwin, January 12, 2002 - 7:46 am UTC
Tom can u give me a simple PLSQL(procedure code) that will accept the username and password passed as parameters to the procedure from forms data block items to check/authenticate the user. Thus the procedure should retrieve the user from a database table and compare it to the parameter being passed to the procedure.
e.g procedure logon(usern varchar2,passw varchar2) IS
declare
user_1 varchar2(40),
passw_1 varchar2(4),
select username, passw into user_1,passw_1
from myuser_tab;
begin
IF user_1=usern AND passw_1=passw THEN
...Accept user)
ELSE
message(not a valid user try again)
end if;
end;
Can this code do the job?
January 12, 2002 - 10:17 am UTC
you appear not to have learned how to program PLSQL. You will not get very far without this knowledge. I suggest you read:
</code>
http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/toc.htm <code>
pretty much from cover to cover.
I would not call this procedure logon as it appears you are using forms and forms has a builtin by that name.
procedure verify_credentials( p_username in varchar2, p_password in varchar2 )
is
l_cnt number;
l_password varchar(255) default get_hashed_pw( p_password );
begin
select count(*) into l_cnt
from myuser_tab
where username = p_username
and password = l_password;
if ( l_cnt = 0 ) then
raise_application_error( -20001, 'Invalid username/password' );
end if;
end;
DBMS_UTILITY.GET_HASH_VALUE
Lokesh Wuluvarana, April 10, 2002 - 1:37 pm UTC
Tom,
I was planning on using using get_hash_value to create ID for customers by putting the full name and address to make this unique. I didn't want to use the Oracle sequence, because those can be typed by a customer incorrectly and to us it could be valid ID for some other customer!
I am not sure if this is given out by Oracle. I wanted to know the what algorithm is involved within this function. Is this something that I can get from you or is it proprietory? The reason I want to know is because our system is in Oracle9i but a system related this (having same data) is in Paradox Database. In the paradox database the same algorithm needs to be written in Delphi (or C) to do similar masking and validating for the customer ID. We want to keep the two systems in sync. Please let me know.
Thanks
Lokesh
April 10, 2002 - 3:01 pm UTC
But a hash won't be unique!
Given a full name "Tom" and address "123 Main Street", i can get a hash of "ABCD"
Given a full name "Bob" and address "4522 5th Avenue", I can get a hash of "ABCD"
You have even a worse problem -- not only that but you don't solve this issue:
"because those can be typed by a customer incorrectly and to us
it could be valid ID for some other customer! "
with the hash either (at least i don't see how you do!)
We change the hash algorithm from release to release (it has happened at least once in the past and could happen again). You need to look at alternative ways.
Reader
A reader, April 30, 2002 - 7:34 am UTC
Tom,
I like to get 1000 unique numbers to concatenate with a
column to make it unique
If I do
dbms_utility.get_hash_value(to_char(sysdate,'yyyymmddh24miss.sssss'),1000,1000000)
How to estimate if there will be collision or not , and
what is the probablity.
Date itself is unique but it will take 7 bytes, if I can
get a smaller number I can use save space
Thanks
April 30, 2002 - 7:41 am UTC
Well, there is almost a 100% chance since
to_char(sysdate,'yyyymmddhh24miss.sssss')
would undoubtably return the same string if called in a loop (it is only down to the second, hopefully your code executes faster then that).
I don't know what you mean by "date itself is unique".
I don't follow what you are trying to do, but why not juse use the numbers 1..1000?
Reader
A reader, April 30, 2002 - 8:00 am UTC
I am using date down to fraction of a second, this is why
I am saying date itself will be unique
yyyymmddh24miss.sssss
Thanks
April 30, 2002 - 11:27 am UTC
sssss is the number of seconds since midnight.
It is not the fractional seconds.
9i has fractional second and even the -- the risk of a duplicate is still THERE.
storing passwords in the database
Shajan Joseph, April 30, 2002 - 9:47 am UTC
This is helpful and supportive
Confused
Pratap, May 02, 2002 - 8:24 am UTC
Hi Tom
You have used dbms_utility.get_hash_value to generate a password. But you have also mentioned that two different strings can generate the same hash value. (Hash values are not unique). Then is there a possibility that a person entering the wrong password can enter the database just because that combination of userid and password generates the same hash value as the real userid and password? Also how does Oracle then do a hash join on tables when the hash values are not unique.
Thanks and regards
Pratap
May 02, 2002 - 9:20 am UTC
Yes, this is true of Unix, of Oracle the database, and of this method (because they all use the same technique -- a hash).
It is considered to an a rare enough freak occurrence as to be statistically impossible.
The hash space is HUGE, in my example, you have a 1 in 1,000,000,000 chance of taking some arbitrary string and having it be equivalent to your password. Your odds of winning the lottery are many order of magnitude larger.
When doing a hash join, the hash is just the first part. That gets us to where the data is POTENTIALLY located. We still have to look at the data to see if it is the "right" data. The hash gets us to the data, we then look at the actual key in the data to see if it matches (and we look at all of the data that hashed to that place)
simply great
Deva, May 18, 2002 - 8:01 am UTC
simply great tom
How to calculate the probability of getting a collision
Pratap, June 18, 2002 - 12:19 am UTC
Hi Tom
Is there any way we can calculate the chances of getting a collision in the get_hash_value procedure.
Best Regards
Pratap
June 18, 2002 - 7:05 am UTC
It is based on
o number of elements you are hashing
o size of hash table
as it is with all hash tables. (in short, yes and no. if you have 100 elements and a hash table of 50, you expect N collisions -- at least 50. In a perfect world, there would be 50 but, in real life there will be more then 50 in all probability).
hashing
mo, September 18, 2002 - 10:55 pm UTC
TOm:
1.can you explain why you combine username and password in hashing as below? why not just password? No one can read any hex anyhow?
2. what is the power(2,30) and rpad statement for?
Are you putting blanks before the hashed password and for what? I used on a varchar2(15) column and it did not work?
do I need leading blanks in my hashed password.
return ltrim( to_char( dbms_utility.get_hash_value(
upper(p_username)||'/'||upper(p_password), 1000000000, power(2,30) ), rpad( 'X',29,'X')||'X' ) );
September 19, 2002 - 7:36 am UTC
1) if we just did password and you and i both used "tiger" as the password -- it would hash to the same value.
I would know your password then. by putting the username in there, our passwords "tiger" hash to different values.
2) select out the power( 2, 30 ) and you'll see its a number -- i'm setting the bounds for the hash table. rpad is used to build a string of x's for me to convert from decimal to hex.
Thank you Tom and Kannan Arumugam
Sarath Chander, October 04, 2002 - 3:44 am UTC
This has been a point in our web based application. Though we deferred from implementing any logic immediately, I hope we can implement the logic provided here.
Thanks once again.
An important clarification on Storing Passwords
Suresh, October 04, 2002 - 8:23 am UTC
AS YOU SAID
We change the hash algorithm from release to release (it has happened at least once in the past and could happen again). You need to look at alternative ways
***********
What do you mean by this. Will the password created using this hash algorithm may become unreadble after a new release? If the password is 'tiger' do we get the same hash value through an old hash algorithm and new hash algorithm?
What impact does an database upgrade have on this password hashing technique?
Please clarify. This is important for implementation of this technique.
October 04, 2002 - 6:31 pm UTC
it means you might consider using your own hashing algorithm if you want to permanently store a hash just for your own protection (although if you use the MD5 hash in the dbms_obfuscation_toolkit package available with 817 and up -- you would be safe since MD5 hashes are standard)
context of customized password verification
Chandra, January 29, 2003 - 10:07 am UTC
Hi Tom,
In what context one would be using this kind of a customized credential checking? I am little confused ... because in order to run the digest function and compare the passed credentials with that stored in the DB, one need to connect to DB. Are you saying we'll be using some kind of generic user (say LOGON_USER) which will have just select privileges on the password stored table. And once the credentials supplied are correct, it will disconnect and re-connect as the new user?? Not really convincing ... I know I am missing something ... please throw some light ...
Thanks, Chandra
January 29, 2003 - 10:20 am UTC
it is when you are doing your own authentication -- connect to db as common user and verify that the user/pw the end user gave you is valid and then continue.
it is for the "do it yourself" people.
A reader, January 29, 2003 - 11:38 am UTC
Why did you use rpad( 'X',29,'X')||'X'
and not simply
rpad( 'X',30,'X')
January 29, 2003 - 12:38 pm UTC
good question -- if I had a reason, I've forgotten what it was ;)
probably snipped a piece of code where the 29 was "length(variable)-1" and in the event variable was null -- I wanted at least one 'x' (but I'm totally guessing ;)
Excellent
Mike, January 29, 2003 - 2:13 pm UTC
Great thread -- very beneficial to me.
Nat, January 29, 2003 - 2:19 pm UTC
could not find question
Scott, February 20, 2003 - 9:07 pm UTC
G'day Tom.
RE: </code>
http://asktom.oracle.com/pls/ask/f?p=4950:61:15468414540126688::::P61_ID:258715248980 <code>
The two suggestions for password changing algorithms have been fair, but with minor drawbacks, well, major because they can't be used.
The first, by changing the password, changing it back if invalid etc. does not work when the profile of the user has restrictions on the type of password that can be used - ie - can't re-use the same password. (default PASSWORD_VERIFY_FUNCTION)
The 2nd idea of creating 26 'dummy' one letter accounts doesn't go down to well with our DBA, besides the fact that it does restrict the combined username||password to 30 characters.
I hate to ask (but my DBA would like me to), but is there *another* alternative?
And in attempting to find this alternative, I found another minor problem: The package used to change the user's password MUST be AUTHID CURRENT_USER, and so must be the caller.
On a side note, as far as parameter passing goes for verifying these passwords, are the plain text password strings sent across the web during this validation? Should this be an issue?
And as for your upgrade, is there a particular reason why we can't add a review after navigating to a question from the "Your Questions" tab (no question date either)? I had MUCH difficulty finding the post (linked at the top of the page) to add this review (even looked through the archives). This is the most similar question I could find.
Thanks.
February 21, 2003 - 8:53 am UTC
set up a test database -- one you want to "crack against".
then in the "real database"
select 'create user ' || username || ' identified by values "' || password || '";'
from dba_users;
spool that, run it in the test database and test them. I'm assuming you want to do a password verification, to make sure they are "good passwords" so you would be guessing from a dictionary somewhere. A sample database on a machine elsewhere in the network will do that for you.
26 dummy accounts shouldn't be a major issue -- the accounts would be locked -- i don't see the issue there (other then the obvious hit on the data dictionary).
the package does not need to be authid current_user, the owner of the package would need ALTER USER granted to them (directly, NOT via a role)
If the passwords are sent in plain text "over the web" -- that would be only because you are not using SSL between the browser and the webserver. If you use SSL -- no clear text. If you do not use SSL, clear text. That choice is yours.
If you are concerned about the network between the webserver and the database -- we have encryption technologies for that as well.
I'll ask them to add that to the "your questions" linked documents.
Can't do it remotely
Scott, February 27, 2003 - 2:31 am UTC
February 27, 2003 - 7:36 am UTC
i know you cannot -- if you want to do it in "real time", you need to do it as described above or not at all.
If your goal is to "crack" -- verify the passwords are not guessable -- you would not be doing this over a dblink.
those are the ONLY options.
dbms_obfuscation_toolkit and MD5?
Chuck Jolley, February 27, 2003 - 10:05 am UTC
I am using your digest function to mangle passwords for our application users so that they can't log into the database using other tools. (They don't know their REAL password)
I was going to convert over to the suggested MD5 functionality so that we won't have to reset everyones password if we upgrade.
But I can't find ANY documentation about MD5 hashing using the obfuscation toolkit.
Our DB version is 8.1.7
Any pointers would be helpfull.
Thanks,
Chuck
February 27, 2003 - 7:52 pm UTC
If you have my book "Expert one on one Oracle" -- I've got it in there (even pointed out "they forgot to document it"
but basically -- the routines are very simple:
-------------------------------- MD5 --------------------------------------
-- The following routines generate MD5 hashes of data.
-- There are two versions: one for raw data and the other for strings.
---------------------------------------------------------------------------
PROCEDURE MD5(input IN RAW,
checksum OUT raw_checksum);
FUNCTION MD5(input IN RAW)
RETURN raw_checksum;
PROCEDURE MD5(input_string IN VARCHAR2,
checksum_string OUT varchar2_checksum);
FUNCTION MD5(input_string IN VARCHAR2)
RETURN varchar2_checksum;
send a string in, get a checksum back. Suggestion
o use the RAW functions
o use utl_raw.cast_to_raw(str) on input
o assign the output to a varchar2 -- it'll be a big hex string that is "safe"
MD5
Doug, February 28, 2003 - 10:47 am UTC
-------------------------------- MD5 --------------------------------------
-- The following routines generate MD5 hashes of data.
-- There are two versions: one for raw data and the other for strings.
---------------------------------------------------------------------------
PROCEDURE MD5(input IN RAW,
checksum OUT raw_checksum);
FUNCTION MD5(input IN RAW)
RETURN raw_checksum;
PROCEDURE MD5(input_string IN VARCHAR2,
checksum_string OUT varchar2_checksum);
FUNCTION MD5(input_string IN VARCHAR2)
RETURN varchar2_checksum;
END dbms_obfuscation_toolkit;
1. How can you have two procedures (or functions) named the same? It seems like you would have to select the type of routines you want (Raw or string) and then 'comment out' or rename the other type.
2. What if you wanted to use both types? We can't rename the procedure or function because the package body is wrapped.
February 28, 2003 - 11:32 am UTC
1) it is called overloading -- it works.
2) use them, they work
ops$tkyte@ORA920> declare
2 something_raw raw(20) := utl_raw.cast_to_raw( 'Hello World' );
3 something_cooked varchar2(20) := 'Hello World';
4 begin
5 dbms_output.put_line
6 ( dbms_obfuscation_toolkit.md5( input => something_raw ) );
7
8 dbms_output.put_line
9 ( dbms_obfuscation_toolkit.md5( input_string => something_cooked ) );
10
11 dbms_output.put_line
12 ( utl_raw.cast_to_raw(
13 dbms_obfuscation_toolkit.md5( input_string => something_cooked )
14 ));
15 end;
16 /
B10A8DB164E0754105B7A99BE72E3FE5
±
±dà uA·©ç.?å
B10A8DB164E0754105B7A99BE72E3FE5
PL/SQL procedure successfully completed.
Works Great!
Chuck Jolley, February 28, 2003 - 11:41 am UTC
Naturaly, the book was 18" from my elbow. :)
I just didn't think to look.
Here's what I came up with.
function blenderize_logon_user(username varchar2, password varchar2) return varchar2
is
v_count number := 0;
v_new_pw varchar2(256);
begin
select count(*)
into v_count
from sys.dba_role_privs
where grantee = upper(username)
and granted_role = 'APP_ROLE';
if v_count > 0 then --if app user user
v_new_pw := dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(upper(username)||'|'||upper(password)));
v_new_pw := 'x' || substr(v_new_pw, 1, 29);
else
v_new_pw := password;
end if;
return v_new_pw;
end;
Thanks,
chuck
Reversibility of the Hashing Algorithm
Nurlan, March 10, 2003 - 12:41 am UTC
Hello Tom,
The message roll has been very useful to me. I was actually reading some stuff on hashing algorithm. Let me ask one question, maybe I am missing something here.
If I change password to a new value and keep old password's hash value from dba_users view and issue
ALTER USER USER_NAME IDENTIFIED BY VALUES 'HASH_VALUE',
I shall end up with the old password. Could it be considered as hash algorithm reversing?
March 10, 2003 - 7:22 am UTC
No, do you know the password? Nope. It would be reversing if you could get the password back.
all you have is a bunch of bytes.
pin number generation
vj, August 26, 2003 - 3:20 am UTC
Tom,
My Database : 9IR2.
We got to generate the PIN number for the account holders. This has got to be an 8 digit number. Which is the right approach for the generation / cryption.
i came up with something like this. but the overall feeling is exposing the magic key...
PACKAGE BODY Pk_Encryption AS
--declare the values of the key
v_keystring VARCHAR2(32):='MYMAGICSTRING';
v_encryptstr VARCHAR2(2048);
v_decryptstr VARCHAR2(2048);
--function encrypt encrypts the string
FUNCTION fn_encrypt(i_inputstring IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
v_encryptstr := NULL;
DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT(input_string=> i_inputstring,key_string =>v_keystring,encrypted_string =>v_encryptstr);
-- RETURN UTL_RAW.cast_to_raw(v_encryptstr);
RETURN v_encryptstr;
END fn_encrypt;
--function decrypt encrypts the string
FUNCTION fn_decrypt(i_inputstring IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
v_encryptstr := NULL;
v_decryptstr := NULL;
DBMS_OBFUSCATION_TOOLKIT.des3decrypt(input_string=>i_inputstring,key_string=>v_keystring,decrypted_string=>v_decryptstr);
RETURN v_decryptstr;
END fn_decrypt;
END Pk_Encryption;
Rgds
A reader, August 27, 2003 - 9:19 am UTC
Very Nice
Srinivas, March 25, 2004 - 3:15 am UTC
Its great!!!!!!!!!!!!!!!. You are great Tom
Excellent Thread
Doug, April 08, 2004 - 12:54 pm UTC
Very useful information. We've implemented the 'one-way' hash for our passwords.
What if you had a table that contained data for an 'electronic Suggestion Box'. The author could remain anonymous or have his identify known.
create table suggestion_box (
author_id number(9),
suggestion varchar2(4000),
anonymous_ind varchar2(1) )
(where author_id is a foreign key to the employee table)
Because of the potential for sensitive data, mamnagement wants to 'protect' the author -- even from the DBA's. Unlike the one-way hash of a password, this author info may need to be displayed/printed. Any ideas on how to protect this info, even from us trusted DBAs?
April 08, 2004 - 3:36 pm UTC
this would call for encryption (dbms_obfuscation_toolkit)
just keep the key safe and private (how to do that is application specific and upto you...)
Function Digest
Anton, May 03, 2004 - 5:12 am UTC
Hi Tom
Can you please explain what is the reason for Function Digest instead of simply using the result of Get_Hash_value?
Also - can you please comment on chances for password (being sent from an Oracle client as a plain text over TCP/IP) to be intercepted?
May 03, 2004 - 7:32 am UTC
function digest is more readable than the alternative?
that's all.
Oracle database passwords are transmitted encrypted.
However, a user might issue "alter user tkyte identified by foobar", that would be sent "clear". If network eavesdropping is a concern, the network traffic may be encrypted as well using ASO (advanced security option)
On Encryption!!!!!!!!
A reader, September 06, 2004 - 7:13 pm UTC
Hi Tom,
We have been using the DBMS_OBFUSCATION_TOOLKIT for encryption and decryption.
But when using the following sql it does not change it to RAW datatype:
select HEXTORAW(('AFFD6C48DBDC2EEB')) from dual;
which is this ¯ýlHÛÜ.ë character set.
Also in case of a simple conversion of '7D' it fails. Which should have been '{'.
So is this the normal behaviour or are we doing something terribly wrong.
Thanks in advance,
Paola
September 07, 2004 - 7:14 am UTC
I'm not sure what you mean.
you asked for a hex to raw conversion, which it would have done, returning a raw string, which when selected from dual in sqlplus would be converted implicitly using rawtohex -- since sqlplus refused to "print raw data" on the screen.
so, you have "raw" data there, not "a string with characters". not sure what you expected?
A reader, September 07, 2004 - 3:02 pm UTC
useless article. I have better encryption utils
September 07, 2004 - 3:12 pm UTC
well, bravo for you! excellent. have a nice day.
must be cool to be you :) this is as useful as many of your other followups
Mr. Cool!!!!!
A reader, September 07, 2004 - 3:13 pm UTC
To Mr. Cool above,
care to share......
Just out of curiousity
Mark A. Williams, September 07, 2004 - 9:38 pm UTC
Just out of curiousity...
Why do people who "negative post" so very often hide behind the "A Reader" label? I am seriously asking, not looking for a flame war... I mean is it not worth being yourself?
Thanks,
Mark
=======================================
Mark A. Williams
Oracle DBA
Author, Professional .NET Oracle Programming
</code>
http://www.apress.com/book/bookDisplay.html?bID=378 <code>
show hextoraw as raw
Dharma, September 09, 2004 - 8:14 pm UTC
Hi Tom,
In 8.1.7 an existing application has used a function to convert hex to raw and return as varchar2.
function hex2raw(p_hex varchar2) return varchar2
is
l_temp long;
begin
select hextoraw(p_hex) into l_temp from dual;
return l_temp;
end;
and this returns a raw value.
We had to move to 9.2.0.4 and it stopped working.The function showed the same hex value being sent in.
I changed it to
select utl_raw.cast_to_varchar(hextoraw('7D')) from dual
which works a charm and fixed the problem. This also works without the hextoraw as there is an implicit conversion.
select utl_raw.cast_to_varchar('7D') from dual
Which one should I stick with.(with hextoraw or the implicit conversion)
Is this the right way to go?
Thanks as always,
-dharma
September 10, 2004 - 7:32 am UTC
wow, wonder why they wrote hex2raw, when they could have just called hextoraw??!??
wonder why people select something into something_else from dual; when
something_else := something;
would suffice.
but in any case, that function should always return what was sent.
You take a string (say 7D)
You apply hextoraw on it converting it into a single byte.
You PLACE a RAW into a VARCHAR and the implicit conversion rules say "rawtohex" that data.
ops$tkyte@ORA817DEV> create or replace function hex2raw(p_hex varchar2) return varchar2
2 is
3 l_temp long;
4 begin
5 select hextoraw(p_hex) into l_temp from dual;
6 return l_temp;
7 end;
8 /
Function created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
ops$tkyte@ORA817DEV> column x format a20
ops$tkyte@ORA817DEV> select hex2raw( '7D' ) x from dual;
X
--------------------
7D
So -- not really sure what you were trying to accomplish there in the past. You always needed to use a RAW, not a STRING?
It is hard to say what to do given I'm not sure what you are trying to accomplish.
If want you want is to pass in HEX and get a RAW byte back,
select hextoraw('7D') from dual;
would be correct and your APPLICATION would fetch that into a RAW bind variable.
my mistake there in the function
Dharma, September 10, 2004 - 6:57 pm UTC
Hi Tom,
The fucntion is slightly different.
In 8.1.7.0
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Sep 10 15:31:43 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
me@x> CREATE OR REPLACE FUNCTION ELIC_HEXTORAW(P_HEX VARCHAR2) RETURN VARCHAR2
2 IS
3 TEMP LONG;
4 BEGIN
5 TEMP := HEXTORAW(P_HEX);
6 RETURN TEMP;
7 END;
8 /
Function created.
me@x> select elic_hextoraw('7D') from dual;
ELIC_HEXTORAW('7D')
--------------------------------------------------------------------------
}
me@x>
See the "}" symbol, It shows as raw value
In 9.2.0.1
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Sep 10 15:28:44 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
scott@krypton.8NBP931> CREATE OR REPLACE FUNCTION ELIC_HEXTORAW(P_HEX VARCHAR2) RETURN VARCHAR2
2 IS
3 TEMP LONG;
4 BEGIN
5 TEMP := HEXTORAW(P_HEX);
6 RETURN TEMP;
7 END;
8 /
Function created.
scott@krypton.8NBP931> select elic_hextoraw('7D') from dual;
ELIC_HEXTORAW('7D')
------------------------------------------------------------------------------------------------------------
7D
scott@krypton.8NBP931>
But in 9i it shows the same. I am not in this application, all that I know now is that they spool data as a tab delimited file and sent across to some other application.
To make it work as in 8.1.7 in 9i I used the utl_raw function. so back to my original question which is the right way to call utl_raw.cst_to_varchar
Should I pass a raw value(hextoraw('7D')) to utl_raw.cast_to_varchar or can I pass a varchar2 value ('7D') to utl_raw.cast_to_varchar for a implicit conversion.
and my apologies for causing the unncessary trouble of giving you a wrong function to test.
Thank You,
Dharma
September 10, 2004 - 8:00 pm UTC
if anything, this is a bug in 8i.
3 TEMP LONG;
4 BEGIN
5 TEMP := HEXTORAW(P_HEX);
that is logically equivalent to
temp := rawtohex( hextoraw(p_hex) );
Really Good One - Thank Tom.
Praveen, September 14, 2004 - 6:18 pm UTC
This is what I was exactly looking for.
I have a requirement to hash using SHA-1 algorithm instead of MD5 on Oracle 9i to hash the string. Is there is anyway to hash it using SHA-1 instead of MD5
Functions.
Kashif, December 10, 2004 - 5:32 pm UTC
Hi Tom,
I liked your solution above where you created the digest function to store the password in a hashed fashion. My question to you is, is it a security hole if the code for this function is readable/accessible by other users? I don't think so, since I can't imagine what someone else would be able to do with this code, and more importantly, be able to figure out what the password is based on just the code, but I wanted to check with you. Thanks.
Kashif
December 10, 2004 - 8:15 pm UTC
if having the code available for review gives someone enough information to hack the code -- then you have what is known as security by obscurity -- meaning "no security"
Always have to laugh when people want to protect the code from prying eyes for 'security' reasons. If you need to do that, you have no security in the first place.
The digest is one way, you cannot go backwards and determine the password from it, even knowing the algorithm. You'd have to brute force it.
I got the same question as Praveen above ...
Greg, January 25, 2005 - 8:18 am UTC
Not sure if you skipped his question because you didn't have an answer handy, or just missed it ... ;)
So, I'll just double-check ...
Same question:
"I have a requirement to hash using SHA-1 algorithm instead of MD5 on Oracle 9i
to hash the string. Is there is anyway to hash it using SHA-1 instead of MD5 "
.. except, of course, I'm using Oracle 8i (we are ugrading, but for now ... just 8i) ... so is there an SHA-1 algorithm in Oracle 8i and/or 9i ??
What algorithm does the dbms_utility.get_hash_value use, anyway??
January 25, 2005 - 9:38 am UTC
dbms_utility is just a simple hash function -- nothing fancy -- it is not generating anything remotely close to a cryptologically secure hash key.
md5 is what was provided in the dbms_obfuscation_toolkit in 9i (no sha-1, you'd have to hash in the client, using a client library)
10g provides a new function in the dbms_crypto package for sha-1
</code>
http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_crypto.htm#1002023 <code>
and if you are upgrading, it would not be in your interest to goto 9i at this late date -- 10g should be the only considered path.
Performance problem
Michael, January 31, 2005 - 11:42 am UTC
Hi Tom,
I created a function like the above (way above) digest function.
I want to use this function as part of select statement that might return many rows.
When I ran this it took me very long time to execute since for every row I called the function.
My situation is this:
I have table base:
CREATE TABLE base_table
( a NUMBER,
b NUMBER,
c NUMBER,
d NUMBER);
for the short example the table can have 100 records (in reality there will be millions)
I want to run a query that will select hash value from a||Â’,Â’||b||Â’,Â’||c||Â’,Â’||d
Is there a faster way to create the hash function?
January 31, 2005 - 11:50 am UTC
say it takes 1/1000 of a second to call.
every thousand is 1 second
10,000, 10 seconds
100,000 100 seconds
1,000,000 1,000 seconds
we are talking "some amount of time"
Might be faster to pipeline (search for that on this site to get examples) if you can. it would avoid the constant back/forth between sql and plsql.
but what are you utlimately trying to DO, why would you (frequently) digest millions of rows?
the story
Michael, February 01, 2005 - 3:45 am UTC
What IÂ’m trying to do is this.
I have a very large table that I want to select data from. This table is like a DW fact table that contains the data and keys for dimension tables.
I select data from the table according to a certain filter and aggregation.
Since IÂ’m selecting according to the same filter several times IÂ’m creating a temp table that contain the data according to the filter.
In addition IÂ’m also creating a small temp table that hold all the members for aggregation.
For example: the fact table looks like:
CREATE TABLE fact_table
( a NUMBER,
b NUMBER,
c NUMBER,
d NUMBER
sales NUMBER);
if I decide to aggregate the table according to columns A, B I create a small table from
create population table as(
select sum(sales)
from fact_table
group by a, b);
the combinations table is created as
create combination table as (
select distinct a, b seq.nextval as comb_id
from fact_table);
currently, in the combinations table I assign each combination with a sequence.
I wanted to add the comb_id to the population table in order to join the tables by this column and not all the aggregation columns – this is because I’m creating an index on all these columns and it might be unlimited number of columns (not just 2 as in the example).
So what I want to do is to replace the sequence with a value that will be unique for every combination.
I thought to encrypt the string a=X,b=y and by that to get a unique key that can be created in both tables.
I know that this sound like the wrong approach to select data from a large table but take into consideration that this is not the whole story and there are other functions that is difficult to explain.
February 01, 2005 - 8:49 am UTC
a hash or digest is not unique. the sequence seems to be
a) fast
b) scalable
c) more than infinite enough in its set of values
but at the end of the day, a hash or digest is not unique. perhaps "sys_guid()" is more appropriate/appealing to you (it is a 16 byte raw key)
but do something millions of times and it adds up, pure math here. and hashing/digesting (especially digests like sha or md5) is really expensive.
Suggestion
A reader, February 01, 2005 - 10:48 am UTC
A function based index might speed up the search.
License of your coding
May, February 02, 2005 - 5:49 am UTC
any license for your coding?
February 02, 2005 - 7:53 am UTC
everything here -- is for you. please use it freely.
(if you copy it to another website however, attribution is expected -- point to the source).
How do i block some users from accessing this column
Sonali, February 02, 2005 - 3:02 pm UTC
How do I block some users say user1 and user2 from accessing this column. No permission on this column but keep all other permissions that they have.
Thanks
February 03, 2005 - 1:33 am UTC
create a view for them would be the most common way.
desc all_users
desc dba_users
and see the difference.
Really SECURE hash function
Edgar, February 03, 2005 - 3:16 am UTC
Hi, this works on 8.1.5 and above, if Java is loaded, offcourse. You can use also MD5 secure hash same way.
SQL> create or replace and resolve java source named "Sha1" as
2 import java.security.*;
3 public class Sha1 {
4 public static byte[] GetHash ( byte[] s ) throws NoSuchAlgorithmException {
5 MessageDigest sha = MessageDigest.getInstance("SHA-1");
6 return sha.digest(s);
7 }
8 }
9 /
Java created.
CREATE OR REPLACE
PACKAGE sha1 IS
FUNCTION GETHASH ( s IN VARCHAR2) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE
PACKAGE BODY sha1 IS
FUNCTION GETHASH_RAW( s IN RAW) RETURN RAW
AS LANGUAGE JAVA NAME 'Sha1.GetHash( byte[] ) return byte[]';
FUNCTION GETHASH ( s IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
return rawtohex(GETHASH_RAW(UTL_RAW.CAST_TO_RAW(s))) ;
END;
END;
/
SQL> select sha1.gethash('Edgars') from dual;
SHA1.GETHASH('EDGARS')
--------------------------------------------------------------------------------
6641339B83BF1591B85EED5CF193A378A15E2991
P.S.
Edgar, February 03, 2005 - 3:37 am UTC
My code, my be, is not too smart, but idea is clear, i think.
But keep in mind possible complications (risks): character set conversions, version upgrades -
when you store in database secure-hash-encripted passwords, there is no way to reveal them in plain text.
So, during system lifetime, hash algorithm must be "stable".
I think also, it is good practics to use industry standarts.
hextoraw not converting back
mary W, March 08, 2005 - 8:58 am UTC
Tom,
I have a query:
select rawtohex(1234), hextoraw(rawtohex(1234)) from dual;
why does it return:
RAWTOHEX(1234) HEXTORAW(RAWTOHEX(1234))
C20D23 C20D23
and not
RAWTOHEX(1234) HEXTORAW(RAWTOHEX(1234))
C20D23 1234
????
March 08, 2005 - 10:51 am UTC
because sqlplus cannot display "raw" -- to it rawtohex's implicity any raw, so that was really:
select rawtohex(1234), rawtohex(hextoraw(rawtohex(1234))) from dual;
A reader, April 11, 2005 - 9:11 am UTC
Thanks a lot Tom, was however looking for something else too
Abhijit Ghosh, May 10, 2005 - 5:54 pm UTC
We have Oracle Financials 11.0.3 and in that passwords are stored in an Financials Application table applsys.fnd_user. We wanted to write a database trigger on the password column of this table, so that if the password was updated on this table, it would also update the Oracle password in the sys.dba_users / sys.all_users table (We have the same usernames for both the Financial Applications as well as for Oracle). Is there any way we could do this ?
I couldn't think of anything, so am asking the master who knows all about Oracle.
Any help from you Tom, would be greatly appreciated.
Thanks in advance,
Abhijit
May 10, 2005 - 11:28 pm UTC
the password is not available to you in the clear is it.
(this is a question about a 3rd party app, not the database really....)
Excellent!
Anil Bakhshi, July 12, 2005 - 7:03 pm UTC
Thanks. Saved me a whole lot of time!
John, August 29, 2005 - 1:12 pm UTC
Very nice algorithm.
Do you still consider it is the best for 10g? Is there any better approach?
Thanks.
August 29, 2005 - 2:02 pm UTC
other than the fact you have more hashes to choose from, same concept applies. see the dbms_crypto package.
md5 function
Peter Kmet, September 28, 2005 - 12:39 pm UTC
Hi Tom,
thanks for this most helpful tip,
this is function that works for me
producing PHP compatible MD5 hashes
FUNCTION md5(v_string IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN
LOWER(utl_raw.cast_to_raw(
dbms_obfuscation_toolkit.md5( input_string => v_string ))
);
END;
select md5('hello world') from dual
khalid, December 01, 2005 - 11:38 pm UTC
thanks tom . ur very good in helping us. thanks again
password
omer, January 06, 2006 - 4:52 am UTC
Hi!
I need some clarification.
SQL> create user temp_user
2 identified by tiger
3 /
User created.
SQL> create user temp_user1
2 identified by tiger
3 /
User created.
SQL> select username, password
2 from dba_users
3 where username like 'TEMP%'
4 /
USERNAME PASSWORD
------------------------------ -----------------------------
TEMP_USER AD368675E7B3B89B
TEMP_USER1 783B0D13216F2867
My questions are
1-Can i get password of user temp as "tiger" from database by any way?
2-Does oracle concat username in password when saving password as a hashed value?
3-Is hashed password of user is reversible?
4-Does oracle store password as hashed as well as simple string?
regards
January 06, 2006 - 1:53 pm UTC
1) lets hope not.
2) username, password and salt
3) one way hash.
4) just hashed.
Great Explanation but what is the salt?
subbu, January 09, 2006 - 11:43 pm UTC
Thanks for the explanation.
Is the salt mentioned is something that will check if i pass the "hash value of username, password and salt" while using statements like the below to reset password of users like below?
alter user x1 identified by values '4FEEDB77EE7609F2';
will oracle hash (x1+'4FEEDB77EE7609F2'+salt)='4FEEDB77EE7609F2'?
January 10, 2006 - 7:58 am UTC
'4FEEDB77EE7609F2' is the hash. It will be stored "as is"
later, when you go to log in, you typically take
string_to_hash := f( username, password, some_salt_string );
then you hash the string to hash and verify it is '4FEEDB77EE7609F2', if it is - you got the right password for that user.
password
omer, January 16, 2006 - 7:45 am UTC
Hi!
Tom, what does oracle forms do to return password in simple string like "tiger" using built-in get_application_property.
Is it taking password from DB and change it to simple string or it stores password somewhere when we give it to connect?
regards
January 16, 2006 - 9:40 am UTC
you gave it the password when you connected, it is just giving it back to you.
Password Encryption
PM, March 09, 2006 - 2:16 pm UTC
Hi Tom,
What encryption method Oracle uses to store the password in DBA_USERS? Can I use the same encryption logic to encrypt the password for application authentication? How?
We are developing an application which will store the userid and encrypted password in a table which will be used for authentication. Further, some of the application user may access the database directly. I was curious to see if it is possible to create the database account for such users with the password same as the one stored in the table (using IDENTIFIED BY VALUES clause).
Thanks in advance
March 09, 2006 - 3:46 pm UTC
it is not encrypted, it is hashed - a one way hash.
wait - that is what the original answer way above says! :)
dbms_crypto.hash not equal to OID ldap userpassword
Ken MacPherson, March 29, 2006 - 11:57 am UTC
Tom:
I used the code snippet below, it doesnt equal the same thing produced by OID's hashing.
Added two users, set the OID ldap (Oracle Internet Directories) default hashing method to SH1 & MD5 respectively. Output for comparison below. I assume the OID hashing is correct. So therefore, the plsql code is doing something wrong.
Is it a nls_language issue?
my database is not UTF8.
show parameter nls_language
nls_language string AMERICAN
---plsql -----
set serveroutput on
declare
l_in_val varchar2(2000) := 'welcome1';
l_hash raw(2000);
begin
l_hash := dbms_crypto.hash (utl_i18n.string_to_raw (l_in_val,'AL32UTF8'),dbms_crypto.hash_sh1);
dbms_output.put_line('Hash='||l_hash);
end;
/
clear text: welcome1
userpassword={MD5}IB8AtcpdZaHBGOXjJDFRTA==
userpassword={SHA}41vs5sXm4OhspR0EQOkigqnWrIo=
Hash=E35BECE6C5E6E0E86CA51D0440E92282A9D6AC8A
ldapadd -D cn=orcladmin -w welcome2 -f addu2.ldif
----addu2.ldif -----
dn: cn=w1,cn=Users,dc=campuseai,dc=org
uid: w1
cn: w1
sn: w1
objectclass: orcluserv2
objectclass: orcluser
objectclass: organizationalperson
objectclass: inetorgperson
objectclass: person
objectclass: top
userpassword: welcome1
mail: admin@testy.com
===============================
ldapsearch -D cn=orcladmin -w welcome2 cn=w1
March 29, 2006 - 12:40 pm UTC
I don't know how they hash it - what salt's/other bits might be added before the hash happens.
utl_encode.base64_encode
Ken MacPherson, June 08, 2006 - 4:37 pm UTC
I got the answer elsewhere (not metalink), posting the response here for the poor soul who might need it.
There are no salts or anything. The md5 and sha hash produce output which needs to be base64 encoded to make the value ascii safe instead of binary. So prepend the output with {MD5} ldap stuff for compatibility.
CREATE OR REPLACE FUNCTION OID_MD5(p_in VARCHAR2) RETURN VARCHAR2 AS
base64key varchar2(2000) := null;
BEGIN
base64key :=
utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_obfuscation_toolkit.md5(input
=> utl_raw.cast_to_raw(p_in))));
RETURN base64key;
END;
About reversibility
Marco Coletti, August 01, 2006 - 5:22 am UTC
Solving the following reversion problem is enough to break our security strategy:
given an hash key, find a string that hashes to the key.
Implementation details for DBMS_UTILITY.GET_HASH_VALUE() are not documented afaik, so there is no public scrutiny about the reversibility of this function; with the source code at hand there could be some opportunity to find a calculation computationally simple enough to make possible to solve the reversion problem in useful time. Another concern is that Oracle does not guarantee the stability of GET_HASH_VALUE implementation through the next versions.
A better alternative would be DBMS_OBFUSCATION_TOOLKIT.MD5(); this is a standard implementation of MD5 which is regarded as a secure hashing function, except for the fact that Lenstra, Wang, and De Weger found a viable solution to a problem somewhat related: find two strings that hash to the same key (i.e. a collision).
</code>
http://en.wikipedia.org/wiki/MD5 The Wang solution does not imply that the reversion problem is solved.
Wiki: "A number of MD5 reverse lookup databases exist, which make it easy to decrypt password hashed with plain MD5. To prevent such attacks you can add a salt to your passwords before hashing them."
One of such databases is
http://md5.rednoize.com/ and it does work pretty well.
Adding a salt (or somewhat transforming the password before applying MD5) makes unlikely that a precomputed hash database already exists.
Therefore a malicious hacker would most likely use a "dictionary attack" to solve the reversion problem for some particular key: take a big word database, then apply the hash function to each word and compare the result with the given key. The dictionary attack can be prevented only keeping the hash function secret, which is not a good idea because for the burden to keep it secret (that would be "security through obscurity"). The dictionary attack can be made too hard to be practical by means of a hash function that takes enough time to be computed and for which nobody knows a faster algorithm:
http://en.wikipedia.org/wiki/Key_derivation_function <code>
My proposal is as follows. Of course you should choose your own salt and tune the iteration count to make the execution time as long as it is acceptable for regular use.
create or replace
function PWDIGEST( p_password in varchar2 )
return varchar2 -- 32 characters from set [0-9A-F]
deterministic
/*
A password-based key derivation function based on principles from PBKDF2 (specified in RFC 2898).
*/
is
l_salt constant varchar2(8) := 'fko4%@>.';
l_buffer raw(16) := DBMS_OBFUSCATION_TOOLKIT.MD5(input => UTL_RAW.CAST_TO_RAW(p_password||l_salt));
l_counter pls_integer;
begin
/* an old approach:
return to_char( DBMS_UTILITY.GET_HASH_VALUE(p_password, 0, to_number('40000000','XXXXXXXX')), 'FM0XXXXXXX' );
*/
for l_counter in reverse 0..1000 loop
l_buffer := UTL_RAW.BIT_XOR( l_buffer, DBMS_OBFUSCATION_TOOLKIT.MD5(input => l_buffer) );
end loop;
return l_buffer;
end PWDIGEST;
/
August 01, 2006 - 7:32 am UTC
when md5 became available, we mentioned it as a good option above.
Just an additional note
Fredrik Rams berg, August 03, 2006 - 3:50 am UTC
All algorithms metioned here make a hash or digest of username || '/' || password. It seems to me that it would be a better idea to use password || '/' || username, at least when using MD5. The way I understand MD5, it scans the string from left to right. If the left part of the string that was digested is known, a hacker wanting to make an attack by trying common passwords from a dictionary could first calculate an MD5 of the that part (username || '/') and save the internal state of the MD5 algorithm to be reused for every password tried. This would save time and make the hacking attempt more likely to succeed.
Adding more salt after the password would also slow the process down.
I'm not sure if the effect of this change is small or big, but it's a trivial change and it should be an improvement at least.
Its very useful
Naveen, August 09, 2006 - 5:36 pm UTC
I was not understanding what exactly the salt value is? after reading this thread I understood and got what exactly I need.
Check digit
VA, August 10, 2006 - 5:49 pm UTC
See </code>
http://www.augustana.ca/~mohrj/algorithms/checkdigit.html <code>
Are there builtin routines in Oracle to generate a 1-digit check digit like the above page mentions? Do you or anyone else know of any ready-made routines that do this kind of stuff?
I want to avoid re-inventing the wheel unless I have to.
Thanks
August 10, 2006 - 11:26 pm UTC
check digit for WHAT exactly. what is your goal here, what are you tring to accomplish.
Check digit
A reader, August 11, 2006 - 6:59 am UTC
A check digit for a 6-digit number.
August 11, 2006 - 11:01 am UTC
you can use ora_hash or dbms_utility.get_hash.
ops$tkyte%ORA10GR2> select user_id,
2 ora_hash( user_id, 9 )h1,
3 dbms_utility.get_hash_value( user_id, 0, 10 )h2
4 from all_users
5 where rownum <= 10
6 /
USER_ID H1 H2
---------- ---------- ----------
0 9 3
5 8 1
11 8 9
19 9 1
35 9 6
21 0 6
24 6 5
25 9 4
34 8 6
36 8 1
10 rows selected.
not quite the same, but if you want exactly the same, you'll take their algorithm and write it up in plsql.
Check digit
A reader, August 11, 2006 - 11:27 am UTC
Do you know the algorithm behind the get_hash_value function?
Does it offer protection against user-input errors like that document I refered to?
I am all for using Oracle builtin functions, don't want to write code unless I have to!
Thanks
August 11, 2006 - 12:17 pm UTC
it is just a hash. It'll take an "infinite set of inputs" and hash it into one of 10 digits.
No, it does not offer the "side effects" of the check digit algorithms you pointed to. They are specialized routines each geared towards spotting a "particular type of error"
Since all you have said is "i have a 6 digit number" we have no idea what your real requirements are. Why are you using a "check digit", what is your GOAL here.
If you read those algorithms and their descriptions - they are all very specialized.
Check digit
A reader, August 11, 2006 - 1:17 pm UTC
The goal is to prevent human error in typing the numbers and inadvertently getting another valid no. i.e. I type 11223 when I mean 12223. The check digit is supposed to prevent this.
Looks like I need to use one of those specialized algorithms. Most of them are pretty trivial to implement, I was just trying to avoid writing code unless I have to.
Thanks
August 11, 2006 - 1:21 pm UTC
only of 11223 is not a valid value!!!
these things work for very specialized domains. If you have one of those specialized domains (like a credit card number for example, they are generated by algorithm) go for it.
If this is just a six digit number - any six digit number - they are all equally valid.
You don't really give enough specifics
Check digit
A reader, August 11, 2006 - 1:34 pm UTC
Yes, I agree it is a specialized domain, I thought my example made that clear. A valid account number is 1122334 where the last digit (4) is a check digit. If I type in 1112334 by mistake, it is guaranteed to be an invalid number because 111233 should never generate the same checkdigit (4) as 112233. In other words, the point is to prevent "inadvertently" landing up on a valid number.
[Of course, if you are lucky/unlucky to mis-type the real number part and also mis-type the checkdigit so that the number again matches the checkdigit, nothing can prevent that!]
August 11, 2006 - 1:55 pm UTC
it is not guaranteed - it is better assured.
If it were guaranteed - there would be all of 10 numbers in your domain.
that is all.
1112334 - could be a mistake, might not be a mistake. Don't rely on this to be 100%, it cannot be. There must be more than ONE six digit number that ends with 4 as the check digit.
Reader
A reader, September 08, 2006 - 9:09 am UTC
Hi,
how do we implement the SHA-1 using DBMS_CRYPTO
for eg: this doesnt work
vi_raw:= DBMS_CRYPTO.encrypt(src => UTL_RAW.cast_to_raw('mystringaspwd'), typ => DBMS_CRYPTO.HASH_SH1, key =>UTL_RAW.cast_to_raw('passkey'));
Leo, September 20, 2006 - 4:59 pm UTC
Hi Tom,
Could you please explain me the use of RPAD in the TO_CHAR format. When i use the function with out RPAD i am getting only numeric as my output. When i use along with RPAD i am getting alpha numeric values. What exactly the to_char does here? I know the use of RPAD. Here i could not understand the usage of RPAD. Please help.
return ltrim(to_char(
dbms_utility.get_hash_value(upper(p_usr)||'/'||upper(p_pwd)
,1000000000
,power(2,30)
)
,rpad( 'X',30,'X')
)
);
Thanks
Leo.
September 21, 2006 - 1:58 am UTC
get hash value returns a number.
to_char is used to convert numbers into strings.
the "X" format says to convert the number into HEX (0-9 A-F)
so, this returns a number formated into a 30 character field of hex characters.
A reader, September 21, 2006 - 8:53 am UTC
Thankyou Tom.
Leo.
Calling Oracle Forms from HTMLDB
Leo, September 22, 2006 - 5:48 pm UTC
Hi Tom,
I am using sample HTMLDB menu application (downloaded from HOW To's of HTMLDB). This application has its own authentication. I would like to call Oracle Forms from this menu application using Oracle 10g AS forms servlet. Since HTMLDB application supports one authentication at a time, i am not able to use Oracle AS SSO authentication. Due to this, i am thinking of passing username as a parameter and validate the user using ON-LOGON trigger of the called form. Basically, this form runs on a different database. To logon into this database i am thinking of using a secondary table (in htmldb database) where i will store the username and password (match the forms database) and validate using LOGON built-in. After reading this thread i would like to avoid storing the password in the database.
Is there any other way can i achieve this?
Please help.
Thanks
September 24, 2006 - 1:14 pm UTC
likely best place to ask this would be on otn.oracle.com in the htmldb forum.
I don't get why you say "cannot use sso", since htmldb (apex) does in fact support that.
Can we find the length of password ?
A reader, December 07, 2006 - 9:57 am UTC
Hi Tom,
Is there any way we can find the length of passwords and the time user last changed his/her password ?
We need to enforce a new poloci at our company where all users should chnage their passwords in a week and new passwords must be at least 10 characters in length.
Thanks
December 07, 2006 - 1:09 pm UTC
How to impliment it for existing users
A reader, December 07, 2006 - 5:16 pm UTC
Hi Tom,
Thank you so much for your post. I have used the very same doc to create a profile and password verification function. It only checks for new users and password changes. If users do not change their password, they are fine.
We already have some 800 plus users and want to lock all users that have not changed password in last two weeks or if they have password that is less than 10 in length.
Is there a way to find the length of password ?
Thanks in advance
December 07, 2006 - 5:53 pm UTC
if you didn't have this on.....
just have everyone expire in two weeks, seems sufficient doesn't it?
the length of the password, unless you were using this, isn't something tracked.
Aplying this concept to BLOBs (>1MB)
Gonzalo Aguerrevere, April 20, 2007 - 6:11 am UTC
Hi Tom.
I have been looking for some time to apply the concept discussed here to calculating an MD5 hash for a DB stored BLOB.
I used the following code:
CREATE OR REPLACE
function ASSIXLOB.md5lob (p_data IN BLOB) return RAW is
checksum_raw RAW(16);
begin
checksum_raw := dbms_obfuscation_toolkit.md5(input => p_data);
return checksum_raw;
end;
... gives me an ORA-06502 Numerical failure. I tried casting the BLOB to RAW but it didn't work either.
Am I missing something here? Can the dbms_obfuscation_toolkit.md5 be used to obtain MD5 hashes from large BLOBs (larger than 1MB)?
Thanks in advance.
April 20, 2007 - 7:15 am UTC
DBMS_CRYPTO is only for ORA 10g
Gonzalo Aguerrevere, April 20, 2007 - 7:49 am UTC
Our system runs on Oracle 9i where as far as I know, DBMS_CRYPTO is not available. Or is it?
April 20, 2007 - 9:58 am UTC
it is 10g, you didn't say anything about a version before.
dbms_obfuscation_toolkit will not work on raws > 32k.
you could do something like:
offset = 1
while offset < length(blob)
loop
hash = obfuscation-toolkit.md5( hash || substr( blob, offset, 32000 ) )
offset = offset+32000
end loop
for example
password encryption algorithm for storing passwords in oracle
Shivdeep Modi, April 26, 2007 - 12:43 pm UTC
Hi Tom,
What encryption algorithm does oracle use for storing passwords? Is is propriety or standard encryption algorithms?
Regards,
Shivdeep
April 27, 2007 - 10:19 am UTC
please read the original answer which includes words to the effect:
... For example,
in Oracle, the password is not encrypted ...
password encryption
Piyush, April 29, 2007 - 1:04 pm UTC
Hi Tom,
I want to know available options in both Oracle Pl/SQL 9i and 10G respectively for password encryption and decryption using default method and using customized keys. Also suggest if I can apply some other validations like password length, alphanumeric , etc in such methods you suggest.
What would be the best way of doing so ?
As per current laws, which ones are applicable now for UK and US, if any.
Which method would you like to recommend us for a mission critical web-applications ?
For a web application would you suggest encryption and decryption methodology in database or at the application front-end languages like java, dot net ?
What is the best method of resetting password for forgot password option? Accepting email and other personal info and emailing the preset password at the accepted email id ? In such case, how can we dynamically generate the passwords. Can you suggest any other way. How does image verification works at sites additionally and how can we implement this in oracle . Any suggestion. Should we store the images files with their id as their content and check what has been entered by the user ?
I have found your replies most valuable and this would certainly help in making some major decisions.
Regards,
Piyush
April 29, 2007 - 1:37 pm UTC
that would be, well, in the documentation. direct link above, in 10g, there is the newer dbms_crypto package.
but, I'll say it again - do not encrypt passwords. You don't need to store them encrypted - every.
As for "laws", you'll want to research that elsewhere to see what applies to you, in your business. (but if you DONT STORE the passwords, you don't have to encrypt them - hash em!!!!)
that way you don't have to worry about keys at all!
as for the best reset method, it depends on who you are and who your customers are. My online bank - the reset method is done via the phone after they ask a ton of questions. The DMV, I have to do it via snail mail. It really depends.
Password Hashing Routine
A reader, June 29, 2007 - 9:25 am UTC
How can we access the hashing routine to check that a username/password combination currently exist? For example, I would like to write a PL/SQL function that takes username and password as parameters and then checks to see whether this username and password combination exists in the database, and then returns true or false.
July 02, 2007 - 2:14 pm UTC
you cannot.
Password Hashing (continued)
A reader, July 05, 2007 - 8:50 am UTC
You cannot? I got the impression from an earlier reply of yours in this thread that it would be possible. You are referring to Oracle Forms and you say:
"I would not call this procedure logon as it appears you are using forms and forms has a builtin by
that name.
procedure verify_credentials( p_username in varchar2, p_password in varchar2 )
is
l_cnt number;
l_password varchar(255) default get_hashed_pw( p_password );
begin
select count(*) into l_cnt
from myuser_tab
where username = p_username
and password = l_password;
if ( l_cnt = 0 ) then
raise_application_error( -20001, 'Invalid username/password' );
end if;
end;"
Is there no way we can do the equivalent of this in a PL/SQL procedure?
July 05, 2007 - 1:10 pm UTC
that is not the oracle password, the password in that example is not hashed, not encrypted, it is entirely application managed.
I don't see how you tied this into the question at all.
How to authenticate from 3rd party app
Robert, August 14, 2007 - 1:15 pm UTC
Tom,
We have a requirement for a new 3rd party app, that the Users log into this new app with their existing Oracle username/password (database 'PROD'). They will not be logging in to 'PROD'; the 3rd party app will just authenticate their 'PROD' username/password before allowing them in to the new app.
Is there a way for this 3rd party app validate a username/password without actually logging in to 'PROD'? For example if they could hash the given password using Oracle's password algorithm, they could match it with dba_users.password in 'PROD'.
Or perhaps they could pass the username/password as arguments to a function in 'PROD' and the 'PROD' datbase could hash the given password and match it against dba_users.password, and then return true or false.
Do you know of a good way to accomplish this requirment?
Thanks,
Robert.
August 20, 2007 - 12:29 pm UTC
Alexander the ok, September 06, 2007 - 8:07 am UTC
Hi Tom,
Good to have you back. We have auditors asking us to run a query like
select * from dba_users where password IN ('D4C5016086B2DC6A', 'D4DF7931AB130E37', 'F894844C34402B67', 'E066D214D5421CCC', '4A3BA55E08595C81') To check for the default sys, system, a couple others, passwords.
Is there any legitimacy to this or is this nonsense?
September 11, 2007 - 8:03 am UTC
well, in 11g - that won't work anymore
but yes, that would find the default passwords
select * from dba_users where password IN ('D4C5016086B2DC6A',
'D4DF7931AB130E37', 'F894844C34402B67', 'E066D214D5421CCC', '4A3BA55E08595C81')
3 ops$tkyte%ORA10GR2> /
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
-------------------------------------------------------------------------------
SCOTT 54 F894844C34402B67
OPEN
MANUAL TEMP 30-JUN-05
DEFAULT DEFAULT_CONSUMER_GROUP
SYSTEM 5 D4DF7931AB130E37
OPEN
SYSTEM TEMP 30-JUN-05
DEFAULT SYS_GROUP
OUTLN 11 4A3BA55E08595C81
EXPIRED & LOCKED 08-JUL-06 08-JUL-06
SYSTEM TEMP 30-JUN-05
DEFAULT DEFAULT_CONSUMER_GROUP
Doesn't work in 11g anymore?
A reader, September 11, 2007 - 10:33 am UTC
What changes in 11g that would make this no longer work? Is the encryption of the passwords changing to produce different values? Or, will the passwords no longer be available to look at any longer? Or a different option that I have no idea about?
September 15, 2007 - 3:28 pm UTC
the hashed password is suppressed in the DBA view.
hashed passwords
A reader, September 15, 2007 - 11:40 pm UTC
Does this mean that there is no longer a way to see hashed passwords in 11g at all? We use this to grab and change passwords temporarily when making modifications to our database.
September 16, 2007 - 11:29 am UTC
great time to either
a) use grants to do things and keep your identity intact
b) use proxy users (and keep your identity intact) instead...
Hashed password
Michel Cadot, September 16, 2007 - 2:23 am UTC
MD5 Hash using DBMS_CRYPT vs. in .NET
Sunj, September 20, 2007 - 3:20 pm UTC
What I get when I hash my password + salt in PL/Sql using DBMS_CRYPT.hash, doesn't compare with the same hash my .NET developer does in his application.
What could be the reason? I doubt if it is a character set issue.
I appreciate your input.
September 24, 2007 - 7:49 am UTC
since I know nothing about .nyet, I have no comment - sorry.
great article
Srikanth, September 25, 2007 - 8:34 am UTC
hi friend,
this is my first visit to your site and looking into the content i was just thrilled and not only this article but other articles are also pretty(any way i din't go through all).
great job....
thanks
Error "PLS-00201: identifier 'DBMS_CRYPTO' must be declared"
Hemal Deshmukh, May 13, 2008 - 2:14 am UTC
Hello Tom,
I have created a function which is using DBMS_CRYPTO.
I am getting following error "PLS-00201: identifier 'DBMS_CRYPTO' must be declared"
I am a normal user.I am not having DBA previlege.
Our DBA is also getting the same error.
Please advice.
Thanks and Best Regards
-Hemal
get_hashed_pw must be declared
Gaetano, May 18, 2008 - 3:07 am UTC
Hi Tom,
I have used verify_credential procedure, but it is not compiled and return the error:
"PLS-000201 - the identifier get_hashed_pw must be declared"
How can resolve this problem ?
Thank You.
May 19, 2008 - 3:59 pm UTC
you write it, it is your "hashing function", whatever you decided to use to hash your passwords prior to storing them.
see original example above.
hash
A reader, February 09, 2009 - 5:21 pm UTC
Tom:
Is oracle hashing strings same way regardless whether they are uppecase or lowercase? if yes, how do you implement application account controls that have to be case sensitive.
SQL> select hash('ABC') from dual;
HASH('ABC')
------------------------------------------------------------
66FE2EC9
1 row selected.
SQL> select hash('abc') from dual;
HASH('ABC')
------------------------------------------------------------
66FE2EC9
1 row selected.
February 09, 2009 - 7:48 pm UTC
no, it does not
hash is something you wrote:
ops$tkyte%ORA10GR2> select hash('ABC') from dual;
select hash('ABC') from dual
*
ERROR at line 1:
ORA-00904: "HASH": invalid identifier
ops$tkyte%ORA10GR2> select ora_hash('abc') from dual;
ORA_HASH('ABC')
---------------
1194017796
ops$tkyte%ORA10GR2> select ora_hash('ABC') from dual;
ORA_HASH('ABC')
---------------
3949159625
ora_hash is something we wrote
hash
A reader, February 09, 2009 - 7:58 pm UTC
Tom:
You wrote the hash. The ora-hash must be new one. It is not part of the oracle supplied packages. Do you have that here.
FUNCTION HASH ( p_password in varchar2 )
return varchar2
is
begin
return to_char( dbms_utility.get_hash_value(upper(p_password),1000000000,
power(2,30) ), rpad( 'X',10,'X')||'X' ) ;
end hash;
February 10, 2009 - 6:59 am UTC
so, did you actually *read* the code smk?
ora_hash is a builtin function in current releases of the database.
but - really - did you *read* the code provided?
If you *read* the code provided, I don't see how you could think to ask
... Is oracle hashing strings same way regardless whether they are uppecase or lowercase? ....
The code includes:
... dbms_utility.get_hash_value(upper(p_password), ...
and in my code, it was called DIGEST, not hash - you wrote HASH
hash
A reader, February 09, 2009 - 8:10 pm UTC
Tom:
This is 9iR2 database.
I think ora-hash is for 10g and above.
Is there a way around this for 9i
February 10, 2009 - 7:00 am UTC
a workaround?
No, there is no *workaround*
but if you *read* the code, you would see that I'm doing an upper() - on purpose - because I wanted it case insensitive.
What might you do to make it case *sensitive*??
ASO
A reader, June 18, 2009 - 12:59 pm UTC
Tom:
I am losing you here. I think you misunderstood me
What i meant is that when i run this hash function on the server it is not using any KEY. Is it ?
FUNCTION HASH ( p_password in varchar2 )
return varchar2
is
begin
return to_char( dbms_utility.get_hash_value(upper(p_password),1000000000,
power(2,30) ), rpad( 'X',10,'X')||'X' ) ;
end hash;
2. I wrote the flow on white paper.
I can create ENCRYPT function using Powerscript Language.
a)User enters (Mike/Tiger) on the client.
b) Powerbuilder client encrypts "Tiger" to "487A49833!@" on client machine.
If I must use a key like you say then it will be hardcoded on client or i select it from an oracle table before running the encrypt function. You might say there is a security hole here but the whole database userid/password is stored too.
c) Client sends "487A49833!@" to Oracle Function.
Oracle hashes this encrypted string and compares it to the hash value of the password column in database and return a code to client.
June 18, 2009 - 3:33 pm UTC
1) how did the password get to the server - therein lies my point. You need to send the password over, but you are trying to protect the password. think about it.
2) if you select the key from the table, I can see the key - if I can see the key, I can decrypt your passwords. Game over.
If you hard code the key into the client, I'll use strings on your code and find it, or a debugger, or whatever. And when the key becomes compromised - and you need to REKEY - what then, you have to redeploy everything.
and you are correct, since you have hard coded your passwords into your binary files, you are as insecure as they come, we should terminate this discussion now - you cannot fix anything until you fix the root problem that goes way back in time (and now we are having a discussion we had last year or so - you have security via obscurity which is worse than no security at all)
your flow - come on, think about it. So, all I need to do is sniff 487A49833!@ off the wire (i'll see it as a bind variable, it'll be very visible). That is now my password isn't it. I can write an application to send that to your database and get in.
ASO
A reader, June 18, 2009 - 4:35 pm UTC
Tom:
The password gets to the server using the NET8 connection that client establishes to send data over.
I see your point about sniffing 487A49833!@ and retransmitting the packets and executing transactions.
1. So basically even if oracle server hashes it on the server, I do not need to know the hashing algorithm.
I only retransmit the transaction using 487A49833! Correct??
Sort of like using SSL for the WEB. You can never have security unless you use SSL on both ends and encrypt the whole message. Here we need ASO or SSH pipe + DATABASE ACCOUNTS and then we have secure system.
2. The only way to make this method secure is that client and server need to agree on some secret code (key)
where one encrypts using Algorithm A and KEy 1 and server decrypts using Algorithm A and key 1?
But then again i do not see how this is secure if the key is visible or the key does not change on every connection. I can still steal "487A49833!@" to server and execute transaction. Correct?
then if the key is insecure nothing is.
June 19, 2009 - 3:33 pm UTC
.. The password gets to the server using the NET8 connection that client
establishes to send data over.
....
so, it is in the binary isn't it. wouldn't take too smart of a person to get it. hard coding a password into a binary and distributing it to the desktop - hmmmmm.
1) I have no idea where you are going with this comment:
... So basically even if oracle server hashes it on the server, I do not need
to know the hashing algorithm.
...
I have no context for that, I don't know why you said it, what point you were trying to make - or even if it makes sense as written. Of course the oracle SERVER will hash things on the SERVER.
but anyway.
.... I only retransmit the transaction using 487A49833! ....
you say you have a stored procedure that if I send it that string, you'll give me access to the world, I don't need to "replay" anything - I have the inputs to the procedure that give me the kings to the kingdom, all is mine. I'll just use sqlplus, call your procedure and I'll have what I need.
2) you need to read a book on encryption - think about it, it obviously can be done (SSL, Oracle passwords). You are taking a very basic view of everything here.
ASO for example provides:
o encrypted network communication - so that the conversation cannot be READ by others
o cryptological check summing to prevent packet replay, it is NOT possible to replay an ASO protected conversation
This is hard to emulate stuff - you won't be able to do it "yourself". You should use the tools provided.
You should definitely stop putting passwords into binaries.
You should definitely stop doing your own I&A (identification and authorization)
You should definitely not go down the path of "we'll implement our own encryption"
advanced seucrity
A reader, June 19, 2009 - 5:26 pm UTC
Tom:
1. What i was trying to say is that even if i implemented a solution
where the client encrypts the password and the server hashes that encrypted string sent by the client - it is still insecure.
Anyone can still steal the encrypted identitiy and send over the transaction again. He does not need to know the HASH algorithm at the server.
Correct?.
2. I understand what we are doing is not that secure. but it meets this requirement
"Implement a feature so that system never transmits unencrypted passwords over the network"
To implement this, would you encrypt at client and store that encrypted string in oracle table? or would you do double encryption one at the client and one at the server and store that.
3. Your hash algorithm does not use a KEY. is that because it is one way encryption?
June 20, 2009 - 4:30 pm UTC
1) that would be what I said in the first place, yes
2) oh good, you meet the letter of the law but you don't actually do anything, great.
3) hashing is one way, it takes an infinite set of inputs and hashes them into a finite set of values - it is not isomorphic, you cannot take a hash and derive what was "hashed" in the first place, it is not reversible.
aso
A reader, June 20, 2009 - 4:51 pm UTC
Tom:
Thanks for the security course - you are true expert.
Is there any favorite data traffic sniffer tool and binary reader tool you use to test how the data is going from client to server to verify if an application is secure or not?
hash
A reader, June 22, 2009 - 9:20 pm UTC
Tom:
1. In 9iR2 is there a way to get SHA-1 hash value or the function is only available with DBMS_CRYPTO which is available in 10g?
2. Since SHA-1 algorithm is known to everyone and client program uses that to send and store a pssword to DB, is this method more secure that oracle GET_HASH_VALUE?
June 26, 2009 - 8:43 am UTC
1) if you can find a java implementation, you could always load that/use that - there are java stored procedures.
2) define "more secure". The get_hash_value in the database is a very simple thing to hash an input into a number between N and M. It isn't really about cryptology or anything. If you are using this as a one way message hash/digest, you would probably want to use something a tad more sophisticated than get_hash_value, yes.
A reader, April 26, 2012 - 12:19 pm UTC
Hi Tom,
I have the following situation:
- I have a java application that will integrate with Forms 11g.
- this java application will run a module forms Through a URL (eg:
http://myapp:8001/forms/module1?parameter1=XXX+parameter2=YYY )
- Because of a characteristic of the java application I need to connect to the database with the username / passwords being sent in the URL. NOTE: I'm still not connected to the bank.
- I'm sending the credentials for connecting to the database encrypted (DES3 algorithm).
But seeing this post, I thought I would also use the HASH.
With the user id and user's password (the password is hashed), you can make a connection to the database?
In all the reviews I've seen so far, it generates the hash value of password / User, etc., and then compares this value with the hashed password retrieved, for example from the DBA_USERS. If this two values are equals, the authorization is given.
But in my case, I need to get the userid and the password (hashed) and provide a connection to the database
with these values.
Is this possible?
Thanks in Advance.
Franco
April 26, 2012 - 12:24 pm UTC
you cannot use a hash if your goal is to use the credentials to log in via the form.
why wouldn't you use SSL for this - rather than deal with key management. If you are sending an encrypted string in a URL that would imply the recipient has the key somewhere - key management is hard and if the key is compromised - useless.
digest
A reader, June 07, 2012 - 5:17 am UTC
The user passwords are generated by using digest function. Now a new application whose main data is in sql server and the users are same. User Id and hashed password from oracle are moved to sql server. In sql server how password can be compared which were previously generated by dbms_utility.get_hash_value from oracle?
June 07, 2012 - 7:10 am UTC
that is left for you to deal with.
Not my issue :)
use whatever technique EXCEPT ENCRYPTION to store hashed passwords as you want. Do the hashing outside of the database if you truly honestly really believe you'll be in this situation over and over and over again (you won't be)
digest
A reader, June 07, 2012 - 7:16 am UTC
it means there is no way which exacatly produce same hash from there. Now is it time to get password from user and then get these settle?
June 07, 2012 - 7:21 am UTC
huh? I don't know what you mean.