SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO! (yay! without Enterprise Edition!*) powered by GNU CRYPTO project
For detailed list of algorithms please consider this link. (much more than DBMS_CRYPTO in 11g, which requires you to buy Enterprise Edition).
[oracle@xeno src]$ ls -l total 764 -rw-rw-r-- 1 vnull vnull 458 Mar 1 05:53 CommonHash.java -rw-r--r-- 1 vnull vnull 598036 Mar 1 04:47 gnu-crypto.jar -rw-r--r-- 1 vnull vnull 96430 Mar 1 04:47 javax-crypto.jar -rw-r--r-- 1 vnull vnull 16969 Mar 1 04:47 javax-security.jar -rw-rw-r-- 1 vnull vnull 214 Mar 1 05:27 Main.java -rw-rw-r-- 1 vnull vnull 145 Mar 1 05:27 SHA1.java -rw-rw-r-- 1 vnull vnull 152 Mar 1 05:18 SHA256.java -rw-rw-r-- 1 vnull vnull 152 Mar 1 05:18 SHA512.java [oracle@xeno src]$ [oracle@xeno src]$ loadjava -u vnull/*** -v -resolve *.java *.jar arguments: '-u' 'vnull/***' '-v' '-resolve' 'CommonHash.java' 'Main.java' 'SHA1.java' 'SHA256.java' 'SHA512.java' 'gnu-crypto.jar' 'javax-crypto.jar' 'javax-security.jar' [..] Classes Loaded: 560 Resources Loaded: 3 Sources Loaded: 0 Published Interfaces: 0 Classes generated: 0 Classes skipped: 1 Synonyms Created: 0 Errors: 0 [oracle@xeno src]$
Now as SYSDBA:
SQL> GRANT CONNECT,RESOURCE,EXECUTE ON DBMS_CRYPTO, CREATE PROCEDURE TO vnull;
SQL> conn vnull/***
Connected.
SQL> CREATE OR REPLACE FUNCTION gnuhash_sha256 (string IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SHA256.calcHash(java.lang.String) return java.lang.String';
2 /
Function created.
SQL> CREATE OR REPLACE FUNCTION gnuhash_sha512 (string IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SHA512.calcHash(java.lang.String) return java.lang.String';
2 /
Function created.
SQL> CREATE OR REPLACE FUNCTION gnuhash_sha1 (string IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'SHA1.calcHash(java.lang.String) return java.lang.String';
2 /
Function created.
SQL> select gnuhash_sha1('1234') from dual;
GNUHASH_SHA1('1234')
--------------------------------------------------------------------------------
7110eda4d09e062aa5e4a390b0a572ac0d2c0220
SQL> select gnuhash_sha256('1234') from dual;
GNUHASH_SHA256('1234')
--------------------------------------------------------------------------------
03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4
SQL> select gnuhash_sha512('1234') from dual;
GNUHASH_SHA512('1234')
--------------------------------------------------------------------------------
d404559f602eab6fd602ac7680dacbfaadd13630335e951f097af3900e9de176b6db28512f2e000b9d04fba5133e8b1c6e8df59db3a8ab9d60be4b97cc9e81db
SQL>
Verify results using OpenSSL :
[vnull@xeno ~]$ echo -n "1234" | openssl dgst -sha1 7110eda4d09e062aa5e4a390b0a572ac0d2c0220 [vnull@xeno ~]$ echo -n "1234" | openssl dgst -sha256 03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4 [vnull@xeno ~]$ echo -n "1234" | openssl dgst -sha512 d404559f602eab6fd602ac7680dacbfaadd13630335e951f097af3900e9de176b6db28512f2e000b9d04fba5133e8b1c6e8df59db3a8ab9d60be4b97cc9e81db [vnull@xeno ~]$
A little bonus, performance verification: DBMS_CRYPTO from 11.1.0.6 versus GNU.CRYPTO.HASH Java library running in JVM in Oracle (oracle_sha1 vs gnuhash_sha1, Oracle does not support SHA-2 standard yet, only SHA1=160 bits).
SQL> CREATE OR REPLACE FUNCTION oracle_sha1 (string IN VARCHAR2) RETURN VARCHAR2 AS BEGIN
2 RETURN sys.dbms_crypto.hash(UTL_I18N.STRING_TO_RAW ('1234','AL32UTF8'),
3 sys.dbms_crypto.hash_sh1);
4 END;
5 /
Function created.
SQL> select oracle_sha1('1234') from dual;
ORACLE_SHA1('1234')
--------------------------------------------------------------------------------
7110EDA4D09E062AA5E4A390B0A572AC0D2C0220
SQL>
From this quick & dirty test you can see there is only 4% performance difference between native DBMS_CRYPTO and GNU_HASH…
* = UPDATE(12/11/2013): please verify with your Oracle Sales representative. Depending on who you ask and how you ask you may get different answer. DBMS_CRYPTO doesn’t seem to be licensed as ASO anymore, same for SSL encryption for RAC databases even for SE, so in those scenarios if you dont want to have encrypted data on the write you may have less complicated alternatives, YMMV.
This looks great, but can you publish your “main.java” file and any others (CommonHash.java ?) which aren’t in the GNU downloads?
Thanks
Sure, here it is: http://vnull.pcnet.com.pl/dl/oracle/security/javacryptotest1.tgz
This is proof-of-concept code and you are on you own.
BTW: you can easily alter it to have AES256 or Serpent256 in 5 minutes ready
Thanks, that’s great.
I need source code sha512 using procedure in oracle (v 9). I have read this tutorial but I dont’t understand. You can give me your detail tutorial ?
What is the earliest version of Oracle which will support this approach? Would it be supported with Oracle 10.2.0.2 and 10.2.0.4?
Kevin,
Oracle won’t support this approach as a whole, as far as i know Oracle supports Java/JRE in the database; so as long JRE/Java is being supported inside Oracle kernel this should work. Please keep in mind that this solution depends on OpenSource components so you are basically on your own with community (or perhaps some 3rd party company can provide some support services if required, but for sure that won’t be Oracle i guess). Hope that helps.
[...] vnull – SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO [...]
arguments: ‘-u’ ‘corp_mobile/7cJMw95wpLi8@orcl’ ‘-v’ ‘-resolve’ ‘CommonHash.java’ ‘Main.java’ ‘SHA1.java’ ‘SHA256.java’ ‘SHA512.java’ ‘gnu-crypto.jar’ ‘javax-crypto.jar’ ‘javax-security.jar’
creating : source CommonHash
loading : source CommonHash
Error while creating source CommonHash
ORA-29532: Java call terminated by uncaught Java exception: java.lang.ExceptionInInitializerError
ORA-06512: at “SYS.DBMS_JAVA”, line 152
ORA-06512: at line 1
creating : source Main
loading : source Main
Error while creating source Main
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
ORA-06512: at “SYS.DBMS_JAVA”, line 152
ORA-06512: at line 1
creating : source SHA1
loading : source SHA1
Error while creating source SHA1
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
ORA-06512: at “SYS.DBMS_JAVA”, line 152
ORA-06512: at line 1
creating : source SHA256
loading : source SHA256
Error while creating source SHA256
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
ORA-06512: at “SYS.DBMS_JAVA”, line 152
ORA-06512: at line 1
creating : source SHA512
loading : source SHA512
Error while creating source SHA512
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
ORA-06512: at “SYS.DBMS_JAVA”, line 152
ORA-06512: at line 1
Hi Mike, generally i don’t have notes with me , but try granting DBA to corp_mobile and then retrying. The first error java.lang.ExceptionInInitializerError might related to lack of permission for this user to load Java libraries. The next ones are can be strictly related to the inability to load the JARs (BTW: try to separate them and load them first) – i’m not sure because it was years ago
In corp_mobile have all possible rights. Loadjava but does not work. Worked directly from the database creation.
create or replace and compile java source named “CommonHash” as import gnu.crypto.hash.HashFactory; import gnu.crypto.hash.IMessageDigest; import gnu.crypto.util.Util; public class CommonHash {public static java.lang.String common (java.lang.String input, java.lang.String hash) {try {IMessageDigest md = HashFactory.getInstance (hash); md.update (input.getBytes (), 0, input.length ()); return Util . toString (md.digest ()). toLowerCase ();} catch (Exception e) {e.printStackTrace (); return null;}}}
Thank you for your prompt response.
Your post is a great news to us since we were struggling to find a way to encrypt a database column data with SHA512 hash.
I tried your code for SHA512. The function was created without problem. However, when I try to use the function in “select gnuhash_sha512(’12′) from dual;”, I got error as: “ERROR at line 1:
ORA-29540: class SHA512 does not exist”. Guess our database does not have the SHA512 java class file loaded. Where should we go get the class file? Should we load it into database? How?
Please advise. Thank you very much.
–Mei
Mei, you probably missed creation of functions or they were created in different schema, you may try to locate them using:
select owner, object_name from dba_objects where object_name like ‘GNUHASH%’;
-J.
[...] I wish I could take even some credit for the content of that post but all I really did was bring the word of another developer (Jakub Wartak) to the attention of this blog’s readers. I wish I could say that my posts on SQL have been as [...]
[...] I wish I could take even some credit for the content of that post but all I really did was bring the word of another developer (Jakub Wartak) to the attention of this blog’s readers. I wish I could say that my posts on SQL have been as [...]
Can anyone post a link that works for the file javacryptotest1.tgz
Yes, can you please post javacryptotest1.tgz. Thanks
Uploaded as http://jakub.wartak.pl/dl/oracle/security/javacryptotest1.tgz
Hi, thanks for this post. It helped me a lot!. Greetings from Argentina.
Valuable info. Fortunate me I discovered your website unintentionally, and I am surprised why this coincidence
didn’t took place in advance! I bookmarked it.
Thanks in support of sharing such a good thinking,
post is fastidious, thats why i have read it entirely
download link not working for http://jakub.wartak.pl/dl/oracle/security/javacryptotest1.tgz. Firewall DLP prevents the download.
Well, then fix your firewall. It works for everybody else…