Thanks for the question, pasko.

Asked: June 28, 2002 - 10:38 am UTC

Last updated: March 09, 2009 - 1:05 pm UTC

Version: 8.1.6 /9.0.1

Viewed 1000+ times

You Asked

Hi Tom
First of all , thanks very for proving us with this Great Oracle support platform..

Today my query is about the DBMS_OBFUSCATION_TOOLKIT.

My requirement is to generate fixed length string (7) to be used as a password , and in that string only characters aA-zZ 0-9 should appear.

My client needs to use Known Key Generation Standards to generate the Key .
I have gone through your password generation Logic in this site and also the spell checker, but could not see how i could do this..

Basicall i need to store this string in the Database as a password and then users will supply this to get some infomartion from this Table back...

My logic was :

for i in 1 .. 7 loop
get number from dbms_random.value(1, 35) ;
substitute all characters not required
append charcter until length 7 is reached
and return it as a password .
end loop

But i saw this from Oracle manuals: Quote...
"Do not use DBMS_RANDOM as it is unsuitable for cryptographic key generation"

1.
So i would like to ask you , how could use
DBMS_OBFUSCATION_TOOLKIT.DES3GetKey or
DBMS_OBFUSCATION_TOOLKIT.MD5
routines to generate Unique/nearly Distinct Random keys..
An example would be Greatly appreciated .

2. If i use sys_guid() as my primary key for table that will be storing passwords..
Do you see a way on how this raw(16) value could be used with the
DBMS_OBFUSCATION_TOOLKIT Functions? ( its Unique)

3. Is it possible to write a Java stored procedure to read Random numbers from Unix Random number file..

Best Regards

and Tom said...

Well, first of all -- i would NEVER store a password in the database, encrypted or otherwise.

A hash -- sure, the password -- no. Unix doesn't store the password, Oracle doesn't store the password, why should you?

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:95412348059 <code>

for how to hash.

If you want to encrypt the password, you'll not only have to generate a secure key -- but (and this is the really really hard part) you'll have to MANAGE IT!!! If someone gets your key -- they got your data. Tell me, where will you store this key you use to encrypt the password? (better store it somewhere safe, it'll take something like 2 trillion years to recover your data if you lose it).....

My suggestion (take it or leave it) don't encrypt this data, you don't NEED to, you don't WANT to.

Rating

(34 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Triple DES

Andre, June 28, 2002 - 2:07 pm UTC

There are no problems with key management. That's what key managers are for. That's what key management departemnts are for.

There are whole databases for storing encrypted things, like those at VeriSign and other trusted entities.

2 trillion years with triple DES ? Wow ! What's the brand of your calculator ?

There are prime-number based and other complex heuristics to break it in some hours, days at most, even with really long keys. You must have gotten that from some book or what... Sounds like sci-fi to me.

As to having a trustworthy encryption key, use the the new GETKEY feature in 9i. It will produce a "good" FIPS-140 certified encyption key. Do not use DBMS_RANDOM, because it produces weak keys. From de 9i App Dev Guide-Fundamentals:

[Quote]

Oracle9i Data Encryption Capabilities
While there are many security threats that encryption cannot address well, it is clear that an additional measure of security can be achieved by selectively encrypting sensitive data before storage in the database. Examples of such data could include:

Credit card numbers
National identity numbers
Passwords for applications whose users are not database users
To address these needs, Oracle9i provides a PL/SQL package to encrypt and decrypt stored data. The package, DBMS_OBFUSCATION_TOOLKIT, is provided in both Standard Edition and Enterprise Edition Oracle9i. This package currently supports bulk data encryption using the Data Encryption Standard (DES) algorithm, and includes procedures to encrypt (DESEncrypt) and decrypt (DESDecrypt) using DES. The DBMS_OBFUSCATION_TOOLKIT also includes functions to encrypt and decrypt using 2-key and 3-key DES, in outer cipher block chaining mode. They require keylengths of 128 and 192 bits, respectively.

The DBMS_OBFUSCATION_TOOLKIT includes a cryptographic checksumming capabilities (MD5), and the ability to generate a secure random number (GetKey). Secure random number generation is important part of cryptography; predictable keys are easily-guessed keys, and easily-guessed keys may lead to easy decryption of data. Most cryptanalysis is done by finding weak keys or poorly-stored keys, rather than through brute force analysis (cycling through all possible keys).

--------------------------------------------------------------------------------
Note:
Do not use DBMS_RANDOM as it is unsuitable for cryptographic key generation.

[End Quote]

Hoping to help...

June 28, 2002 - 5:02 pm UTC

I don't know -- what is your brand of calculator dude?? I'm not sure what you are getting at. I understand that two trillion would be a conservative number for brute force cracking DES3:

<quote source=</code> http://www.prlsoftware.com/DES_Info.htm <code>, one of MANY sources that will confirm that it takes a really utterly long time. 2 trillion might even by "short">
Triple DES (3DES)

Triple DES does not make your encryption 3 times harder to break. It makes it 5 x 10 33 (5 Billion Trillion Trillion) times harder to break. This is because Triple DES means you are performing 3 DES computations with three separate keys. The result is that your key length is effectively increased to 24 characters (168 bit encryption). This means that the number of possible key combinations that are available are:

2168 = 3.7x 10 50 (370 Trillion Trillion Trillion Trillion) different combinations.

Assuming that you can crack the keys at a rate of 1 Million per minute...... you would be working away for the next...... well let's just say that you still be working on the first couple of percent when the earth ends and.... and assuming the universe implodes you still won't have finished computing every different combination.

This is why Triple DES is considered so strong.
</quote>

you do the math, I'm too tired right now.... Anyway, NOT that it is even relevent.

As for the "there is no problem with key management, that is what key managers are for", well.... I like things

o complete
o simple
o small
o easy

so, if you like things complex, hard, big, with many moving parts -- by all MEANS introduce yet more software products into the mix. I've never heard of anyone using a key manager for something as so totally simple as "storing a password". Seems like taking an atom bomb to crach a walnut -- it'll certainly crack the walnut but hey, it is a little overkill you know what I mean.

I'll stand by the answer here. You do not want to encrypt the password, you want to one way digest it, perhaps with the MD5 checksum. That way, when someone presents you with a username+password, you can say "yup, that is your password" but there is NO WAY anyone can take your username and hashed password and derive your real password. Much more secure, simple to do, no complex encrypt/decrypt with a key manager -- no muss, no fuss.

Always looking for the easy way...

Triple DES

Andre, June 28, 2002 - 6:05 pm UTC

There are people breaking 3k triple DES keys with HP calculators -- believe it or not.

You are talking of dummy algorithms that take too long. But experts and natural code-breakers can do it much faster, like I said with bounded algorithms that work with prime numbers for example.

Okay, I know how Oracle hashes users passwords. Yes, "complete", "simple", "small", "easy".

You said you would NEVER store passwords in a database -- nope, too radical. So, you would never work at a digital certification company or a goverment agency ? They have their user data encrypted (not hashed), and have very well secured keys. The reader didn't say that wouldn't be his case...

June 28, 2002 - 7:01 pm UTC

show the proof. go for it. I don't believe you. A calculator -- now maybe you are smoking something. If it is "real", you should be able to prove it to us right. Go for it.

Let us see the one link somewhere that says "hey, these guys cracked des3 in less then your lifetime". Come on -- I'll eat the humble pie. I know I would have heard of des3 getting beaten - and I haven't.

I don't believe you -- but that should be trivial for you to correct as I'm sure that the event of des3 being cracked would have made at least one newspaper -- don't you?

It is so hard to actually key in a google search that returns zero hits but:

"triple des cracked"
"des3 cracked"
"des3 broken"
"triple des broken" (well, ok that returns a hit, of a search engine for someone looking for triple des broken)

do...

Now, the search for

"cracking triple des"

returns alot, alot of articles discussing how it hasn't happened.

A calculator -- now that is currently the stuff of SCI-FI.

I said, in response to this question, don't store the passwords encrypted. That is not a radical response.

So, when is the askandre site opening up anyway.

Mr Andre Please do not spoil the decorum of this Forum

A reader, June 28, 2002 - 7:41 pm UTC

If you have a Question Ask otherwise Please leave.
Do not waste yours and Others time Please.
U are better of focusing on the World Cup Final for Brazil rather then trying to verbally show Tom is wrong without proofs and with just hollow statements.
An Idle Mind is a Devils Workshop.
Final Goodbye.

A reader, June 29, 2002 - 10:10 pm UTC

Tom You Are The Best !!!!!!!!!

So how do we verify/Authenticate User

A reader, June 29, 2002 - 11:38 pm UTC

Please Explain further
Well, first of all -- i would NEVER store a password in the database, encrypted
or otherwise.

A hash -- sure, the password -- no. Unix doesn't store the password, Oracle
doesn't store the password, why should you?

My Question is So how do we do this User verification if we have nothing in our database to cross check with. also
Even How does Oracle Do it If passwords are not stored.

June 30, 2002 - 9:53 am UTC

Please read the link to which I referred:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:95412348059 <code>

it sort of answers that (well, not sort of -- it demonstrates how to do exactly that)

to andre

Richard Ji, June 30, 2002 - 12:39 am UTC

Andre,

Sounds like you might be confused 3DES with DES. Yes
DES has been cracked but not triple DES. Please show
us a proof (a link) which shows triple DES has been
cracked. So I can sue my bank for not using secure
encryption. :)

Richard

How to use MD5 instead of dbms_utility.get_hash_value

Pasko, July 01, 2002 - 2:59 am UTC

Hi Tom

Thanks you very much for your Response and all other contributers to this query..

1.
Could you please provide us with another version of your digest routine which uses MD5 instead of dbms_utility.

2.Is there a chance that your digest function would return Unprintable characters and also what would happen if i would like to skip all values which contain "ambiguous" characters such as 1IO0 - from the generated values outputted from your digest function.
--and does your digest routine always return 8 Byte strings

3.I saw from above :
"use the the new GETKEY feature in 9i"
but i don't really see this function/procedure from the package or are they talking about DBMS_OBFUSCATION_TOOLKIT.DES3GetKey ?

Could you please provide us with an example of how to use this function/procedure to generate secure keys as it's indicated from the Docs.

4. From my orignal query, could i somehow use raw values generated from sys_guid() and convert them to strings of say 6 , 7 or 8 printable characters .

Thanks in advance.

July 01, 2002 - 7:16 am UTC

1)

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      function digest( p_username in varchar2, p_password in varchar2 ) return varchar2
  3      is
  4      begin
  5          return dbms_obfuscation_toolkit.md5
  6                             ( input => utl_raw.cast_to_raw( upper(p_username)||'/'||upper(p_password) ) );
  7      end digest;
  8  begin
  9      for x in ( select username from all_users where rownum < 20 )
 10      loop
 11          dbms_output.put_line( 'User: ' || rpad( x.username , 30 ) ||
 12                                ' digest: ' || digest( x.username, 'TIGER' ) );
 13      end loop;
 14  end;
 15  /
User: SYS                            digest: 1F01F328A9343C6A822967C153827ECD
User: SYSTEM                         digest: 94FB743169267E709CE426B4DE4E7637
User: OUTLN                          digest: C21A6A62391092F1DDBB37FD285FC8CA
User: TEST_OWNER                     digest: D928C75947D8B2D4752DF483CAC4C335
User: OPS$ORA817                     digest: 21AC742C0811907D1CD48DC48490A75B
User: CTXSYS                         digest: C6910F5BE313CF6EA4F4E8106C83D24F
User: DBSNMP                         digest: AB08AC4006A9FDA674C79631843BCF9A
User: OPS$CLBECK                     digest: 35EC540E9852150ED58B0E96CC3677A1
User: APPS                           digest: 44AF9D0127E0A89577DD0649976C3333
User: TMC                            digest: 6AC936E720116C8A84DD498C43DF483D
User: WEB$CLBECK                     digest: 9942BC161F4F3BE1BA48DFA4CFE26247
User: SDILLON                        digest: 63893AA51B17DCDDD64D805ED46E19A9
User: TYPES                          digest: E8FCCCFF0F88F640015D2AB11E9D7BD9
User: TPS_SECURITY_MANAGER           digest: 071B6D68C44DC5353A7B3BC6D5C93745
User: COMMON                         digest: 46657377218B05A0613325A56EDB3D7F
User: U2                             digest: EA3F0861195433A8CDE4CEDE02E48E94
User: U1                             digest: 0E88AB91C65C39CC91464D9C31170BEB
User: USER_A                         digest: DACDF46B0B225FA9C7CA12ABA6F70231
User: SINGLE_ACCT                    digest: A5E30115B4B1C9E483C44340E6D6537B

PL/SQL procedure successfully completed.


2) no, it is returning a HEX string (0..9 A..F)

I don't understand what you mean by "ambigous characters" -- 1IO0 doesn't seem to contain any ambigous characters?  Are you trying to verify a supplied username/password or GENERATE a password??? What I am demonstrating is not a password GENERATION method, it is a password VERIFICATION method.

This digest method (above) returns 32byte strings, the other 8 byte (although you control that by controlling the size of the hash table in that one)

3) I suppose they meant the des3getKey -- that routine is straight forward enough to call:

ops$tkyte@ORA9I.WORLD> variable s varchar2(80)
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> exec :s := dbms_random.string( 'A', 60 ) || dbms_random.string( 'A', 20 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> print s

S
--------------------------------------------------------------------------------------------------------------------------------
UpKYrZHeiooBqkvpJHuImXrLOmVzYhgBhJcNLQLwkKYAhKgoZKnXPDBjcgYPGnfPyQOBAGmtRTJUhXAo

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> declare
  2          l_seedr   raw(255);
  3      l_keyr    raw(255);
  4  
  5          l_seed    varchar2(255);
  6      l_key     varchar2(255);
  7  begin
  8          l_seed := :s;
  9          l_seedr:= utl_raw.cast_to_raw( l_seed );
 10  
 11          dbms_obfuscation_toolkit.desGetKey( seed => l_seedr, key => l_keyr );
 12          dbms_output.put_line( '1) Key Length = ' || length( utl_raw.cast_to_varchar2(l_keyr) ) ||
 13                            ' Key = "' || l_keyr  || '"' );
 14  
 15          dbms_obfuscation_toolkit.desGetKey( seed_string => l_seed, key => l_key );
 16          dbms_output.put_line( '2) Key Length = ' || length( l_key ) ||
 17                            ' Key = "' || l_key  || '"' );
 18  
 19          dbms_obfuscation_toolkit.des3GetKey( seed => l_seedr, key => l_keyr );
 20          dbms_output.put_line( '3) Key Length = ' || length( utl_raw.cast_to_varchar2(l_keyr) ) ||
 21                            ' Key = "' || l_keyr  || '"' );
 22  
 23          dbms_obfuscation_toolkit.des3GetKey( seed_string => l_seed, key => l_key );
 24          dbms_output.put_line( '4) Key Length = ' || length( l_key ) ||
 25                            ' Key = "' || l_key  || '"' );
 26  
 27  
 28          dbms_obfuscation_toolkit.des3GetKey( which => dbms_obfuscation_toolkit.ThreeKeyMode,
 29                                                                                   seed => l_seedr, key => l_keyr );
 30          dbms_output.put_line( '5) Key Length = ' || length( utl_raw.cast_to_varchar2(l_keyr) ) ||
 31                            ' Key = "' || l_keyr  || '"' );
 32  
 33          dbms_obfuscation_toolkit.des3GetKey( which => dbms_obfuscation_toolkit.ThreeKeyMode,
 34                                                                                   seed_string => l_seed, key => l_key );
 35          dbms_output.put_line( '6) Key Length = ' || length( l_key ) ||
 36                            ' Key = "' || l_key  || '"' );
 37  end;
 38  /
1) Key Length = 8 Key = "40365655002781FA"
2) Key Length = 8 Key = "xH¨ @,"
3) Key Length = 16 Key = "6F2A0859C8AE128AAFEDEBB558EF3482"
4) Key Length = 16 Key = "~c?
                             Ë&8³b+á"Xµý"
5) Key Length = 24 Key = "C80121C7A7C24ED749919715DD3698EEF7C9E5544E2E54D2"
6) Key Length = 24 Key = "Ù=B"HN/ªã`ÿÙò:ªR©ö,ªO"

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> /
1) Key Length = 8 Key = "96670E8DE2F905CE"
2) Key Length = 8 Key = "L`ÿã£"
3) Key Length = 16 Key = "74653E1DDB12181DC724D55DBDEBEEB9"
4) Key Length = 16 Key = "kM?~1¯o0EKí$Ä"
5) Key Length = 24 Key = "E9D34EB3DCCA6D32271E0B9E8851C2F3C4694012CA738B66"
6) Key Length = 24 Key = "8@ÿÑ*5÷ÓxÝù=û¥¥äkíÑ<"

PL/SQL procedure successfully completed.


Couple things to note:

a) given the same seed, this routine returns different data -- it is non-deterministic (one of the attributes you want in a key generation routine)

b) when you use the STRING "api" (not raw), it returns a string with possibly unprintable characters (eg: the above example "beeped" at me, a CTL-G was in there somewhere).  I strongly suggest using only the RAW api's with dbms_obfuscation_toolkit for all of the interfaces and using utl_raw when necessary.  Get the key in RAW and leave it raw.  Encrypt string data into a RAW, not a string and so on.

4) sure, but again it sounds like you are into password GENERATION, not verification again?

I need a cryptographic key

Pasko, July 01, 2002 - 8:55 am UTC

Hi Tom

Thanks very much for your prompt Response .

Yes,Tom , you are right..

Actually i am trying to use DBMS_OBFUSCATION_TOOLKIT to generate a password , although i know for sure that this package is for Data Encryption/Decryption and may be Password(KEY) verification.

That's why i was specific in asking about the
DES3GetKey and MD5 Functions because i could use this to use the generated key as My password.

I saw your password Generation Routine in this site too which is very similar to my own ealier approach , but i wanted to use the random keys generated from DBMS_OBFUSCATION_TOOLKIT .( after i read from the Oracle Manuals)

What do they (Oracle people in the Docs) actually mean by writing :

"Do not use DBMS_RANDOM as it is unsuitable for cryptographic key generation"

and my aim is to generate this
"cryptographic key" they are talking about...(but how ?? )
or may be i missed out on something.

Best regards

July 01, 2002 - 9:16 am UTC

You are not generating cryptographic keys, you are trying to generate a user password (one that they should presumably change immediately after getting anyway).

Just use a simple algorithm with dbms_random (perhaps dbms_random.string fits your needs)

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1235458156173 <code>

and don't "over solve" the problem.

The reason they say don't use dbms_random for generating keys for cryptology is because if you are encrypting the data -- you must have some pretty darn interesting data -- stuff people would be willing to try and crack. Now, using brute force or sophisticated techniques (even with a reallly superfast HP calculator ;), it would take a very long time and lots of resources to decrypt the data. It would be much much easier to get the key and decrypt it. If you use a psuedo random number generator with a period (such as dbms_random), all you need to see is a sufficiently long sequence of generated numbers to "guess" what the next one would be (eg: as an end user, you could just encrypt a couple of rows and look at the keys provided to you to decrypt -- based on that, you would be able to guess what keys come next). Hence, you can get the keys to the data without having to "crack" the encryption.

Using the des3getkey -- you don't have such a situation. It doesn't have a period, you cannot guess the next key based on some sequence of generated keys.

But, in your case, you have a somewhat simple problem to solve. I would just use dbms_random.

But dbms_random.string generates unprintable characters

Pasko, July 01, 2002 - 10:32 am UTC

HI Tom

Thanks again for your prompt response.

Tomorrow i will play with dbms_random.string

But i am not sure if it will suit me because i would like to get only charasters a-zA-Z0-9 in my string/key and also
skip all strings whenever '01IO' appears..

I am not sure how often will these characters appear in my string....plus i need to limit it to less than 8 bytes...

Is there an option from dbms_random.string which generates only Printable characters?

Thanks

July 01, 2002 - 10:44 am UTC

Yes (if you follow the link for dbms_random i have above, i show that) but printable characters is much much larger then a-zA-Z0-9 -- it includes {}[]!@$$#@ and so on.

Perhaps you'll be best suited by making a string of the characters you WANT to use:

str varchar2(500) :=
'ABCDEFGHIJKLMNPQRSTUVWXYZabcdefghjkmnpqrstuvwxyz23456789';
strlen number := length(str)

for example and then doing something like:

l_pw := null;
for i in 1 .. 8
loop
l_pw := l_pw || substr( str, dbms_random.value( 1, strlen ), 1 );
end loop;

Thanks very much Tom...-_-

Pasko, July 02, 2002 - 2:45 am UTC

Hi TOM

Thanks very much for your excellent follow-up to my query..

I will play around with all these examples you have given me , but i think i might generate a passowrd and use MD5 routine for this password Verification when it is queried later by a user..

Actually in my case it's not really a passoword but rather a secret key which is given to user and the user is not allowed to change it...i will make it expire after some days , so that i may be able to re-use it for other users.

Best regards

To add to all of that 3DES CRACKING disscussion I looked into google.com

Ian Matyssik, July 03, 2002 - 5:38 am UTC

This is a qoute from one mailing list digest. for people who still don'get it. In a human language.
------------------------------

From: rasumner@bach.wisdom.weizmann.ac.il (Reuben Sumner)
Subject: Re: 3DES cracked in 22 hours ??? (Was: Re: (fwd) DES Challenge III Broken in
Record 22 Hours !)
Date: 22 Jan 1999 12:49:32 GMT
Reply-To: rasumner@iname.com

On Thu, 21 Jan 1999 10:14:46 -0000, Sam Simpson <ssimpson@hertreg.ac.uk> wrote:
>Assuming 3DES has an effective keylength of 112-bits then it would take
>around 329293306 years on average to break a single key.
>
>This figure is based on the peak keys per second figure (250 Billion) quoted
>in the EFF/RSA press release.

I'm afraid I have to dissagree with your calculation.

>> kps:=250*10^9; // keys per second
250000000000
>> spy:=60*60*24*365.24; // seconds per year (average year)
31556736.0
>> kpy:=kps*spy; // keys per year
7889184000114802736.0
>> 2^111/kpy;
329076927259548.0
2^111 is the average number of keys you need to search. Thus the total number
of years is 329,076,927,259,548. That is 329 trillion not 329 million.
Even just searching 64 bits (ie the RC5-64 search) would take 1.17 years.
The real threaght is not distributed.net. It is amusing and a demonstration
that there are lots of wasted CPU cycles out there, but not a real threaght.
In the paper by Matt Blaze et al it is estimated that a $10 custom ASIC
can test about 200M keys/second. At that rate a $10M computer could break
64 bits in an average of 12.8 hours. Compare that to 1.17 years.
Even only 80 bits of something like skipjack (assuming brute force is your
best attack) would take a $1B attack 1 year (on average) to crack.

Reuben

------------------------------

------------------------------

From: bt@templetons.com (Brad Templeton)
Subject: Re: 3DES cracked in 22 hours ??? (Was: Re: (fwd) DES Challenge III Broken in
Record 22 Hours !)
Date: 23 Jan 1999 19:10:32 PST

In article <slrn7agt61.v23.rasumner@bach.wisdom.weizmann.ac.il>,
Reuben Sumner <rasumner@iname.com> wrote:
>2^111 is the average number of keys you need to search. Thus the total number
>of years is 329,076,927,259,548. That is 329 trillion not 329 million.
>Even just searching 64 bits (ie the RC5-64 search) would take 1.17 years.
>The real threaght is not distributed.net. It is amusing and a demonstration
>that there are lots of wasted CPU cycles out there, but not a real threaght.
>In the paper by Matt Blaze et al it is estimated that a $10 custom ASIC
>can test about 200M keys/second. At that rate a $10M computer could break
>64 bits in an average of 12.8 hours. Compare that to 1.17 years.
>Even only 80 bits of something like skipjack (assuming brute force is your
>best attack) would take a $1B attack 1 year (on average) to crack.

The difference between 128 bits and 64 bits is indeed this vast. That
we built a machine cheaply that can crack 56 bits does mean that with more
money you can break 64 bits quickly, but 128 bits or more is another story.

Add enough bits and you reach the point where if every atom on earth were
a processor that could check a key every microsecond you still couldn't break
by brute force. Add a few more bits and it's every atom in the universe.

Suffice to say that this means that sufficiently long keys are unbreakable
by brute force based on current understanding, even with things like DNA
computers. Only a breakthrough in physics, like quantum computing can
break a long key with brute force. Or a breakthrough in discrete
math -- which is no longer brute force.
--
Brad Templeton </code> http://www.templetons.com/brad/ <code>

------------------------------

Hope that will explain it to some people who likes HP Calculators. Also subject of this message is from some HP Calculators FAN.

Divisible By 8

Jerry, May 20, 2003 - 12:15 pm UTC

Your book states that the data being encrypted must have a length div. by 8.

In the MD5 digest function you provide in this thread, is the cast_to_raw() satisfying that requirement?

When I hash all the user/passwds in our database, I get an even 32 bytes according to length(), but I just want to confirm this.

May 20, 2003 - 1:39 pm UTC

md5 is a checksum, not encryption

it does not have the same 8byte multiple restriction.

It does return a 16byte key, which in hex will be 32 characters:

CREATE OR REPLACE PACKAGE dbms_obfuscation_toolkit AS
------------------------------- TYPES ------------------------------------
-- Types used to make it easier for the user to reserve the correct
-- amount of memory for a checksum.

SUBTYPE varchar2_checksum IS VARCHAR2(16);
SUBTYPE raw_checksum IS RAW(16);

Interesting read

Scott, July 17, 2003 - 9:33 pm UTC

G'day

Excellent discussion.

Do you recommend any sites that dicuss key management?

And regarding the dbms_obfuscation_toolkit, if we have the choice between DES and DES3, why choose DES? Why not have the best?

July 18, 2003 - 1:34 am UTC

I'm not personally aware of any -- but I'm sure they exist, suggest you try googleing

"key management" encryption

you'll find des3, since it munges more bits, to be more "computationally expensive"

Thanks

Scott, July 18, 2003 - 2:36 am UTC

G'day

Thanks for that Tom. I've also grabbed a few "key" ideas from your book.
CPU cycle usage didn't occur to me...

Would this be correct:
DES(string) returns gobble-d-gook.
DES(raw) returns nice to read in sql*plus characters
DES3(raw) returns highly encrypted gobble-d-gook.

Just making sure I'm using these right....
(I expected highly encrypted nice to read characters from DES3(raw))

Enjoy your holiday

Thanks.

July 18, 2003 - 8:37 am UTC

none of them read "nice to read" characters? they all return gobble-d-gook

Curious....

Scott, July 20, 2003 - 8:00 pm UTC

v_des := dbms_obfuscation_toolkit.desencrypt(input_string => v_locn_name,key_string => v_key_string);
v_raw_des := dbms_obfuscation_toolkit.desencrypt(input=> v_raw_name,key=> v_raw_key);

LOCN_NAME_ENC
rcLGNr)1 Gz sBJP|e_GK5cPx~S`

LOCN_NAME_RAW
72634C47CE722931891247907A0973C24A9D5001FCE5DF47CB356350787E53E0

July 20, 2003 - 9:16 pm UTC

curious how?

"Nice to Read" do you mean...

Peter, July 21, 2003 - 8:55 am UTC

Scott

By "Nice to read" I suspect you mean in that the text is entirely composed of 'printable' characters for what ever display device you are using.

In your "curious" post the raw version is almost certainly the hexadecimal dump of the encrypted string - the hex codes line up with the printable characters in the other string!

If you use 'raw ' you are mapping the text on to a 16 character set - this can be displayed on your PC or whatever and can be keyed in by a human with relative ease, but it is twice as long as the the other version.

Personally, I can't see a problem with unprintable characters in an encrypted string. If my app encrypts it and decrypts it I am happy - if nobody else can read it I am even happier!

I agree with Tom - a hex stream is not easy to read either - I have to start writing on my display with a marker pen ;-}

which one is supported, single or triple DES?

June, August 12, 2004 - 9:59 am UTC

Tom,

Quick question for dbms_obfuscation_toolkit:

- Which one is supported through this package, single DES or triple DES (56 or 168 bit encryption)?
- Is it easy to plug in different encryption routines (e.g. through a crypto API)?

Thanks as always.

August 12, 2004 - 10:36 am UTC

both are in 8iR3 and above.

desencrypt is single
des3encrypt is triple

the supplied algorithms are the supplied algorithms. if you want to use some other algorithm, you would be using a C based external routine to call C code or a java stored procedure to call java code.

DEFAULT KEY LOCATION

Billy, November 05, 2004 - 4:41 pm UTC

Hi Tom very good info here. When you gen the key where is the default location and file name for this key? Thanks man

November 05, 2004 - 6:00 pm UTC

the key is returned to you -- no files, no "location"

Date and Numeric data type encryption

PM, March 25, 2005 - 11:52 pm UTC

Hi Tom,

We have a requirement to encrypt columns of all basic data types in our schema. e.g. date of birth / salary /commission. This encryption is for one of the customers data that we store in the database. Another customer may not want to store the data in encrypted format. And same application accesses the data.

How to encrypt number / date datatype columns and store the encrypted value in the same column?

So for example, is it possible that if 'number' is "1234" to always get encrypted data as say "35235" which is 'number' too?
Similarly if there is 'date' variable "03/25/2005" to always get upon encryption a value which is of 'date' type also?

Thanks
P

March 26, 2005 - 9:17 am UTC

you need to use RAW to encrypt safely (before 10r2 when the database will do this transparently....)

You cannot store raw encrypted date data in a DATE type, it doesn't 'fit'. First a date is 7 bytes and encrypted data will be multiples of 8 bytes. Second, the encrypted data is NOT a date.

same with numbers...
same with strings......

you have to take the data, encrypt it and store it in raw.

what's happening here?

Mariano, April 25, 2005 - 3:42 pm UTC

Hi Tom.
I'm getting errors at compile time with this function:

select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

create or replace function hash_it (p_text in varchar2)
return raw
is
begin
return dbms_obfuscation_toolkit.md5(utl_raw.cast_to_raw(p_text));
end hash_it;
/
show errors

This is the error:
PLS-00307: too many declarations of 'MD5' match this call

I'm pretty sure the database is not confusing the md5 procedure with the md5 function so I've got no clue what this is about.

Although 10g has the dbms_crypto package i need to use dbms_obfuscation_toolkit for backwards compability.
Kindly regards as always.

April 25, 2005 - 3:50 pm UTC

the problem is it gets confused with strings and raws -- each are implicitly convertable to the other.

use
....md5( input => utl_raw....
^^^^^

that will be unambigous.

But 3des is undocumented in 8i

Pratap, July 19, 2005 - 5:38 am UTC

</code> http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_ob2.htm <code>

The DES3 is functions/procedures are not documented in the 8i manuals. Does it mean that it is not supported by Oracle in 8i?
(In 9i manual it is documented)

Generic question - There are a few undocumented procedures/functions in documented packages. Are they supposed to be used or not. Please clear the air in this regard.

July 19, 2005 - 7:48 am UTC

it was an oversight, it is "supported" in 8iR3

contact support for details on whether something is a doc bug (oversite) or not. Typically, for the supplied packages you can select the specification out of the database (all_source where name = 'pack' and type = 'PACKAGE' order by line) to get "the documentation" on it.

DES3 Obsolete ?

Des B, July 19, 2005 - 11:17 am UTC

This interesting, if sometimes acrimonious discussion prompted me to have a look what's happening in this area. It seems that even DES3 ( or 3DES ) has now been overtaken by AES ( Advanced Encrytion Standard )

hash

Hans, July 25, 2005 - 1:35 pm UTC

My requirement is to compare the records in two tables( say Table A and Table B) before flagging it for update or insert for history reasons.
I am planning to write a Java Stored procedure to develop a hash for the records in A and B and compare the hash values. if the hash values are not the same the records goes in
history table.

I will appreciate if you have any examples for JSP with hash comparision

Thanks in advance

July 25, 2005 - 2:24 pm UTC

see other place you put this same exact question.

breaking triple DES

amit poddar, November 05, 2005 - 9:04 am UTC

Hi,

Following is a quote from </code> http://www.garykessler.net/library/crypto.html#rsamath <code>

"The DES III challenge, launched in January 1999, was broken is less than a day by the combined efforts of Deep Crack and distributed.net. This is widely considered to have been the final nail in DES's coffin."

DES versus AES

Lakshmi, July 18, 2006 - 7:24 am UTC

Hi Tom

Right now our application is using Oracle 9i and we are planning to shift to Oracle 10gR2 shortly.And we are using dbms_obfuscation_toolkit.DES algorithm in our stored procedures.

My question is:
1. Is dbms_obfuscation_toolkit is becoming obsolete?
2. Is it a good idea to change to AES anyway?

Can u provide ur inputs on this?

July 18, 2006 - 8:43 am UTC

1) it is replaced in 10g by dbms_crypto, so yes, over time it will be deprecated.

2) you tell me, we provide the implementations, you pick the one that is best for you.

"u"??? I want to meet this "U" person someday.

DES Vs AES

Lakshmi, July 20, 2006 - 6:55 am UTC

Thank YOU very much for the fast response :-)

Hardik Pathak, May 24, 2007 - 7:00 am UTC

What if I need to compare a supplied password string with the password that is stored by oracle? e.g.,
let's say, an oracle database user name is 'user_name' and password for this 'user_name' user is 'pAssw0rd', so this will act as, SQL> conn user_name/pAssw0rd@database_name.

Now, the question: Is there any way provided by oracle to compare the 'pAssw0rd' of 'user_name' for a supplied string?

May 26, 2007 - 11:13 am UTC

quote from above:

...
Unix doesn't store the password, Oracle
doesn't store the password, why should you?
.......

we do not store the password.

to compare the password to see if it is valid, just logon with it.

Hardik Pathak, June 04, 2007 - 10:01 am UTC

But I need to execute one trigger/procedure based on that authentication. And that can not be simply compared with user name only, because any one knows every one's user name. Thus, let¿s say a trigger/procedure with username and argument as parameter, and authentication is needed..

I am securing my database with VPD, and can visualize this option only :(

June 06, 2007 - 9:38 am UTC

I am not following your logic at all.

If you have their username and password, log in using that from the application and - well - USER will have their authenticated username.

Hardik Pathak, June 15, 2007 - 1:40 am UTC

Excuse me, if I am asking my question within inappropriate thread.
Can you please educate me for the following..

conn sys/password@mydb as sysdba
desc dba_users;

that returns:

USERNAME
USER_ID
PASSWORD
ACCOUNT_STATUS
LOCK_DATE
EXPIRY_DATE
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
CREATED
PROFILE
INITIAL_RSRC_CONSUMER_GROUP
EXTERNAL_NAME

Now, I am much concerned with the field named "PASSWORD". Is it somthing to store oracle user password? Let me declare, I am not going to use it anyway to match or compare it with anything, unless you suggest!

June 15, 2007 - 7:44 am UTC

it is hashed, it is not encrypted, it is not clear text, it is a one way hash of the password.

see original text above.

Hardik Pathak, June 16, 2007 - 3:23 am UTC

Thanks for direction.

Excellant Discussion

Vishnu Vardhan Reddy, September 21, 2007 - 2:28 pm UTC

Tom,
I don't have words to describe your knowledge..Ever time i come to your web site i learn new things ...

Thanks
Vishnu Singireddy

generate a number from concatenated attributes

Avishek, March 06, 2009 - 6:21 am UTC

Hi Tom,
If I am asking this question in the wrong thread, please direct me to the right one; but I need to ask you this:
We are in the process of creating a data warehouse which has 5/6 slowly changing dimensions per table and an ETL tool is used to compare the existing record, insert the new record and update the old record.
This looks to be a very time-consuming process and hence a way to concatenate the SCD Type-2 columns thus forming a string and create a unique NUMBER is undertaken, so that the record comparison becomes easy and really fast.
Now, DBMS_OBFUSCATION_TOOLKIT.md5 generates a unique STRING, which may not give satisfactory results while comparison as our table will have min of 10m records!

Request you to please let me know any way in which we can generate a unique NUMBER so that we can achieve our requirement.

Thanks in advance.

March 06, 2009 - 10:44 am UTC

did you know a number in Oracle is a 22byte varying length string with length bytes and stored in a packed decimal like format?

Eg: a number is already a string sort of.

dbms_crypto is what you should be using, there is a HASH function that returns a 16 byte raw.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec open :x for select dbms_crypto.hash( utl_raw.cast_to_raw( ename || '/' || empno || '/' || job ), dbms_crypto.hash_md5 ) from scott.emp;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(ENAME||'/'||EMPNO||'/'||JOB),:B1)
-------------------------------------------------------------------------------
F7490E096D46CDA880656F0CBAABE259
4E9A76A5D2D75E4AD6CC97E0AC352E28
58B24AF728A1DB2F88B4786D28DFEBD5
E4896E08B7AD3D68750414392E14CCF0
498716C13F824ABF9B7BC9E792085F88
916B11C088E3D91EE01A690332B3D0FA
376B2677FF4A27679EB23B718811405E
64E509357E9E38C81D195A9F51DF4326
FDA8349C5F0A01213D2F154E13030F0F
C2787D8E1A10B076CEF80535CFE89A30
580C14ECE3E265EC87016771D607974B
929E598DB5ED325420A67439A5D38A9A
AE6A2A6563432D74C53FF88AF6B99681
33108B19A66671BBFCAB908EE988851C

14 rows selected.

sqlplus implicitly converts RAW to hex-strings, but it is a 16byte raw field.

Generate number

Avishek, March 07, 2009 - 8:13 am UTC

Thanks for the prompt response. That was really good to know.
However, the output from the dbms_crypto.hash() is RAW data, which our ETL tool does not support; and one more datatype conversion will not help the performance.
Hence, is there a more prudent way to go about changing STRING to NUMBER?

March 07, 2009 - 12:47 pm UTC

do not change the string to a number, I sort of tried to impress upon you that a number in Oracle is.....

A string already.

Stinks to be constrained by your tools, doesn't it.

Avishek, March 09, 2009 - 9:11 am UTC

Thanks again... yes, it really sucks depending on an ETL tool to load data!
So, do you suggest that we go ahead with the dbms_obfuscation_toolkit.md5 function, which generates a string and do string comparison? But again, as far I know, the string generated may not be unique for millions of records!

March 09, 2009 - 1:05 pm UTC

... it really sucks depending on an ETL tool to load data!
...

no, it stinks when the tool limits what you can do. Not all tools are created equal.

any hashing function returns a raw bit of data. the varchar that dbms_obfuscation_toolkit returns - it is really raw, you have to (if you want to safely use it) apply:

rawtohex( utl_raw.cast_to_raw( dbms_obfuscation_toolkit.md5( string ) ) )

that'll be 32 bytes of hex data (NOT SAFE TO USE THE MD5 OUTPUT AS A STRING EVER - characterset conversions will corrupt the data)

so, just use dbms_crypto as I demonstrated and use a 32byte string, which is the 16byte raw stuff turned into hex.

I hate dbms_obfuscation_toolkit for tricking us like it does, it is so unsafe to put raw into varchar2, it is easily corrupted. Do not use that package.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Triple DES

Triple DES

Mr Andre Please do not spoil the decorum of this Forum

So how do we verify/Authenticate User

to andre

How to use MD5 instead of dbms_utility.get_hash_value

I need a cryptographic key

But dbms_random.string generates unprintable characters

Thanks very much Tom...-_-

To add to all of that 3DES CRACKING disscussion I looked into google.com

Divisible By 8

Interesting read

Thanks

Curious....

"Nice to Read" do you mean...

which one is supported, single or triple DES?

DEFAULT KEY LOCATION

Date and Numeric data type encryption

what's happening here?

But 3des is undocumented in 8i

DES3 Obsolete ?

hash

breaking triple DES

DES versus AES

DES Vs AES

Excellant Discussion

generate a number from concatenated attributes

Generate number

More to Explore

PL/SQL demos

PL/SQL docs

DBMS_OBFUSCATION_TOOLKIT.DES3GetKey , MD5 Functions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Triple DES

Triple DES

Mr Andre Please do not spoil the decorum of this Forum

So how do we verify/Authenticate User

to andre

How to use MD5 instead of dbms_utility.get_hash_value

I need a cryptographic key

But dbms_random.string generates unprintable characters

Thanks very much Tom...-_-

To add to all of that 3DES CRACKING disscussion I looked into google.com

Divisible By 8

Interesting read

Thanks

Curious....

"Nice to Read" do you mean...

which one is supported, single or triple DES?

DEFAULT KEY LOCATION

Date and Numeric data type encryption

what's happening here?

But 3des is undocumented in 8i

DES3 Obsolete ?

hash

breaking triple DES

DES versus AES

DES Vs AES

Excellant Discussion

generate a number from concatenated attributes

Generate number

More to Explore

PL/SQL demos

PL/SQL docs