Solved

How to encrypt my password field in oracle ?

Posted on 2014-11-11
16
896 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
  • 5
  • 5
  • 4
  • +1
16 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 400 total points
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
Thanks for your help. I am really appreciate your time to give the solution.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now