SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO

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…

benchmark

* = 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.

22 Responses to “SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO”

  1. chris gould says:

    This looks great, but can you publish your “main.java” file and any others (CommonHash.java ?) which aren’t in the GNU downloads?

    Thanks

  2. admin says:

    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 ;)

  3. chris gould says:

    Thanks, that’s great.

  4. DinhThuc says:

    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 ?

  5. databaseguy says:

    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?

  6. admin says:

    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.

  7. [...] vnull – SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO [...]

  8. Mike says:

    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
    :(

  9. admin says:

    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 ;)

  10. Mike says:

    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.

  11. Mei says:

    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

  12. admin says:

    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.

  13. [...] 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 [...]

  14. [...] 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 [...]

  15. José Fernandes says:

    Can anyone post a link that works for the file javacryptotest1.tgz

  16. bob says:

    Yes, can you please post javacryptotest1.tgz. Thanks

  17. GastónDBA says:

    Hi, thanks for this post. It helped me a lot!. Greetings from Argentina.

  18. 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.

  19. Thanks in support of sharing such a good thinking,
    post is fastidious, thats why i have read it entirely

  20. ismail says:

    download link not working for http://jakub.wartak.pl/dl/oracle/security/javacryptotest1.tgz. Firewall DLP prevents the download.

  21. admin says:

    Well, then fix your firewall. It works for everybody else…