?
Solved

How to encrypt my password field in oracle ?

Posted on 2014-11-11
16
Medium Priority
?
1,562 Views
Last Modified: 2014-11-18
Hi,
Currently we have a table that store userid and password. The password was encrypted using md5 method.
But now I heard that md5 is no longer secured or less secured. So what is the best encryption method should I use ? We are using Oracle database 11g.  Can you give me sample code using "update table set pwd = something_encrypt(pwd)".
Can we decrypt the encrypted password. ?

thanks.
0
Comment
Question by:VW 63654
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40436744
This is very unspecific to whatever database you use.

MD5 is not an encryption, but a hashing algorithm. That in itself is alright, storing password hashes instead of both unencrypted or encrypted passwords is a good practice.

These password hashes can't be decrypted and that's fine. I know you intend to decrypt the passwords to encrypt them with a new algorithm, but that plan will not work.

The weakness of md5 is, hashes of frequently used passwords can be looked up in so called rainbow tables. If you go the route of getting a hacker and get such rainbow tables you might find some frequent used passwords of your users, but that's no solution to move passwords to a more secure hash.

Best practice to make the rainbow table lookup attack impossible is to combine the user entered password with "salt", that is random data. When checking the validity of a login you then add the same salt to the user entered password and hash this to compare with the stored hash. To be able to do so you of course have to store that "salt" random data into a users record and have a field for that.

To move to another algorithm you need to wait for users to relogin and at that time after checking the validity in the old way store random salt plus the entered password newly hashed, or you assign new random passwords along with random salt for each user, send the new password to them and store newly hashed(salt+password).

Inform users to change their new password with their next login, because even a strong password sent via mail to the account owners still is a password sent out as clear text message via mail relay servers and should therefore be changed.

For best practice of password storage see https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet

OWASP is your no.1 resource for security best practices of any kind.

Related to Oracle:
https://docs.oracle.com/cd/E22289_01/html/821-1278/password-storage-scheme.html
http://oracle-base.com/articles/9i/storing-passwords-in-the-database-9i.php
http://oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php

Bye, Olaf.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40436796
Yes, Encryption can be done using two ways..

1. Using DBMS_OBFUSCATION toolkit
2. Using Oracle Wallet(Transparent Data Encryption )

Obfuscation toolkit you will receive it as an older version and there are some security loopholes in using it and is outdated, if the application is not internet facing and for low risk applications over intranet behind some firewall, you can still use it.

An illustration of the same can be found in tim hall site

http://oracle-base.com/articles/8i/data-encryption.php

An enhancement to the obfuscation tool kit is oracle wallet, to make this work in your database this you have to do some tasks.

See the tim hall site for illustration

http://oracle-base.com/articles/10g/transparent-data-encryption-10gr2.php
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40437366
I recommend NOT ecnrypting your passwords.  Hash them instead.  This is exactly what Oracle does with it's passwords.

http://www.experts-exchange.com/Database/Oracle/A_855-How-Oracle-Stores-Passwords.html

It's much, much more secure.  Use SHA hashing with at least 256 bits
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40437965
Actually he is hashing, he only thinks he's encrypting them. MD5 is a message digest, which is a hash.

It's not a secure hash, though, MD5 rainbow tables are all over the place anywhere, and even not having one get a bunch of millions of easy passwords, hash them and you have a table to lookup passwords for a certain MD5 value.

SHA-256 is much better. OWASP talks about even more costly hashing: pbkdf2 or HMAC-SHA-256 and they even recommend iterating the hashing hundreds of times, so you get a macroscopic time needed to get the hash stored in the database. Simply read the whole article, it's worth the time.

All this doesn't of course unbind you from having a secure password policy to prevent success of brute force attacks with passwords as '123456' or 'password' or 'hardtoguess' (this happens, if you tell users "all passwords must be hard to guess" and you have not only dim, but also humorous users).

Bye, Olaf.
0
 

Author Comment

by:VW 63654
ID: 40439017
Hi,
You guys gave me many links to read. I am not that lazy but I just hope that someone could direct me to somewhere that I can easily understand. I am still reading but sometime it raise more question than reaching the solution.

>>1. Using DBMS_OBFUSCATION toolkit
>>2. Using Oracle Wallet(Transparent Data Encryption )

Can somebody correct me, these tools is about data encryption in general, not just password encryption. Is that right?

>>Actually he is hashing, he only thinks he's encrypting them. MD5 is a message digest, which is a hash.

You are right. I really thought it was an encryption. (any string that I see as funny character I consider it as encryption). See how dummies I am. In this case, it is how to convert a value (password) to SHA-256 value.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40439069
DBMS_CRYPTO in Oracle 11.2 only supports SHA1, but see http://jakub.wartak.pl/blog/?p=124
If in doubt you can always compute hashes outside of the database, too, eg in Java.

Bye, Olaf.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40439095
>>Can somebody correct me, these tools is about data encryption in general, not just password encryption. Is that right?

Yes. Its data encryption, but isn't password data? Those tools which are there in oracle have been built upon those encryption methods and then links provided would give you illustration of how to use them. By using tool like mentioned here it simplifies the task of encryption, you just have to call an api to encrypt and decrypt
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40439244
Wasim:
http://www.darkreading.com/safely-storing-user-passwords-hashing-vs-encrypting/a/d-id/1269374

>You guys gave me many links to read. I am not that lazy but I just hope that someone could direct me to somewhere that I can easily understand.
I don't know your background, so süeaking for myself, I rather chose good resources over 101 tutorials. Anyway, as I said "read the whole article, it's worth the time." that was in answer to sdstuber "only" suggesting SHA256.

The article sdstuber refers to is specific about Oracle again and suggests there is a built in hashing done for SYS.USER$ stored password hashes and is talking about authentication towards oracle databases, not about logins of a portal website or any application having its own user management. This article also covers a paragraph on the question "Why does Oracle use Hashing intead of Encrypting?" and one more about that topic is http://www.darkreading.com/safely-storing-user-passwords-hashing-vs-encrypting/a/d-id/1269374 But I'm sure you are already convinced in using hashing instead of encryption.

VW 63654, your main problem is not about finding any code to hash values inside or outside of oracle, there are plenty around, your problem is you can't transpose the MD5 hashes you have to a new HASH and even if you would decide to instead use encryption of passwords, you don't have them. You can only take the passwords users enter at their next login, verify them as usual with the MD5 hash and if valid then take this newly entered password and hash it. Well, I mentioned this and another strategy to simply set new passwords in my first answer already.

About the availability of hash functions in oracle: You can use loadjava to execute java, so you have plenty of implementations available. Aside of that the login can be implemented in whatever frontend language and only store and read hashes in your data, compute them outside of the database. This is not less secure, so you're not limited to the hash functions available in oracle itself.

Bye, Olaf.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40439800
Here's an example of implementing sha2 as a java stored procedure within Oracle for various bit lengths

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sha2 AS
import java.security.MessageDigest;
import oracle.sql.*;

public class sha2
{
public static oracle.sql.RAW get_digest( String p_string, int p_bits ) throws Exception
{
MessageDigest v_md = MessageDigest.getInstance( “SHA-” + p_bits );
byte[] v_digest;
v_digest = v_md.digest( p_string.getBytes( “UTF-8″ ) );
return RAW.newRAW(v_digest);
}
}
/

Open in new window


And the corresponding pl/sql wrapper to expose it

CREATE OR REPLACE FUNCTION sha2(p_string in VARCHAR2, p_bits in number)
RETURN RAW
AS
LANGUAGE JAVA
NAME ‘sha2.get_digest( java.lang.String, int ) return oracle.sql.RAW';
/

Open in new window


Using the examples in http://en.wikipedia.org/wiki/SHA-2 we can verify the algorithms return the expected results.
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,256) FROM DUAL
union all
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,384) FROM DUAL
union all
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,512) FROM DUAL
0
 

Author Comment

by:VW 63654
ID: 40443976
sdstuber,
Thanks.
I like to try your scripts (creating java as stored procedure)  but before that, can you tell me what first script looking for ?
>>import oracle.sql.*;
Does this require additional sql scripts or file to be downloaded first OR is there any dependency ?
0
 

Author Comment

by:VW 63654
ID: 40446428
I have this error when try to create java stored procedure:

ORA-29536: badly formed source: oracle.aurora.sqljdecl.TokenMgrError: Lexical error at line 8, column 49.  Encountered: "\\u201c" (8220), after : ""
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 400 total points
ID: 40446691
If you copy and pasted above source code, notice it has left and right double quotation mark in it (The carachter " tilted to left and right). Replace that with normal double quotation mark ".

Bye, Olaf.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 40447240
hmm, thanks I didn't notice the quotes got messed up in the cut-n-paste.  I'll try again.


CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SDS.SHA2 as 
import java.security.MessageDigest;
import oracle.sql.*;

public class sha2
{
    public static oracle.sql.RAW get_digest( String p_string, int p_bits ) throws Exception
    {
        return RAW.newRAW(MessageDigest.getInstance( "SHA-" + p_bits ).digest((p_string == null ? "" : p_string).getBytes( "UTF-8")));
    }
}
/

Open in new window



CREATE OR REPLACE FUNCTION SDS.sha2(p_string IN VARCHAR2, p_bits IN NUMBER)
    RETURN RAW
AS
    LANGUAGE JAVA
    NAME 'sha2.get_digest( java.lang.String, int ) return oracle.sql.RAW';
/

Open in new window


>> Does this require additional sql scripts or file to be downloaded first OR is there any dependency ?

yes there are dependencies, this class is built on other classes, but those should already be included in the database.
0
 

Author Comment

by:VW 63654
ID: 40448823
it works great,

SELECT sha2('hello world',256) FROM DUAL
union all
SELECT sha2('hello world',384) FROM DUAL
union all
SELECT sha2('hello world',512) FROM DUAL

256:
B94D27B9934D3E08A52E52D7DA7DABFAC484EFE37A5380EE9088F7ACE2EFCDE9
384:
FDBD8E75A67F29F701A4E040385E2E23986303EA10239211AF907FCBB83578B3E417CB71CE646EFD0819DD8C088DE1BD
512: 309ECC489C12D6EB4CC40F50C902F2B4D0ED77EE511A7C7A9BCD3CA86D4CD86F989DD35BC5FF499670DA34255B45B0CFD830E81F605DCF7DC5542E93AE9CD76F

How can I let other user use this function ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40449693
grant execute on sha2 to OTHER_USER;

or

grant execute on sha2 to SOME_ROLE;

and then grant that role to the other user


you might want to create a synonym for the function, but it's not necessary
0
 

Author Closing Comment

by:VW 63654
ID: 40451732
Thanks for your help. I am really appreciate your time to give the solution.
0

Featured Post

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question