Hello everybody,
Recently I had the challenge to implement SHA3-Hashing in an Oracle database. Unfortunately, Oracle does not natively support SHA3 (only SHA1 and SHA2 from Oracle 12c onwards). Therefore, I used some java libraries to be able to calculate a SHA3-512-hash directly in SQL or PL/SQL. So, how’s it done?
- Download the jars from https://www.bouncycastle.org/ (I used bcprov-ext-jdk15on-164.jar and bcprov-jdk15on-164.jar, but more recent versions, if there are any, should be fine as well).
- Load the files into the database using loadjava:
loadjava -u username/password@database -o -r -v bcprov-jdk15on-164.jar loadjava -u username/password@database -o -r -v bcprov-ext-jdk15on-164.jar3. Create the Java Source:
create or replace and compile java source named at_mi_crypto as
package at.mi.crypto;
import org.bouncycastle.jcajce.provider.digest.SHA3;
import org.bouncycastle.util.encoders.Hex;
public class Crypto {
public static String getHashSHA3_512(String iHashText) {
SHA3.DigestSHA3 sha3512Digest = new SHA3.DigestSHA3(512);
sha3512Digest.update(iHashText.getBytes());
return Hex.toHexString(sha3512Digest.digest());
}
}
/4. Create the PL/SQL-Function calling the Java Source:
CREATE OR REPLACE FUNCTION js_getHashSHA3_512
(i_text IN VARCHAR2
)
RETURN VARCHAR2
IS LANGUAGE JAVA NAME 'at.mi.crypto.Crypto.getHashSHA3_512(java.lang.String) return java.lang.String'
;
/5. You should be good to go:
select js_getHashSHA3_512('This is a 1. Test Text') from dual;
JS_GETHASHSHA3_512('THISISA1.TESTTEXT')
----------------------------------------------------------------------------------------------------
6bbf42d384579dc1e417ed8b2f3eaa65e373b3935b99ce8fd75eec58aa7d1f20434132ff04e218c0cd6ce575a757fae50fe3
0bf4024531768746d3bba4979386Well, of course BouncyCastle supports also other algorithms that are not natively supported by Oracle, so this can come in very handy.
Hope, this might help some people facing the same challenge!
Best wishes
Markus
Find information in English on the 2-click solution here: http://yro.slashdot.org/story/11/09/03/0115241/heises-two-clicks-for-more-privacy-vs-facebook
Hello
It’s very good document.
I need one clarification.
In which user I need to load (loadjava) the java files.
Is it sys or any other user ?
S Sukumar
Hi, thanks. Basically, every user with the grants to create procedures and tables should be able to load the java files (+ the java grant for “oracle.aurora.security.JServerPermission.loadLibraryInClass”, please also refer to https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdev/loadjava-tool.html#GUID-033299E6-07E2-4C2C-9E26-A2FE25118F1C, especially the -grant option). So, sys should in my opinion be ok, but it could be any user with these grants. Best wishes, Markus
Hello Marcus,
When I tried to loadjava file, I am getting below error
resolving: class org/bouncycastle/x509/extension/AuthorityKeyIdentifierStructure
skipping : class org/bouncycastle/x509/extension/X509ExtensionUtil
skipping : class org/bouncycastle/x509/util/LDAPStoreHelper
skipping : class org/bouncycastle/x509/util/StreamParser
skipping : class org/bouncycastle/x509/util/StreamParsingException
The following operations failed
class META-INF/versions/9/org/bouncycastle/crypto/digests/SHA512tDigest: resolution
class META-INF/versions/9/org/bouncycastle/crypto/digests/CSHAKEDigest: resolution
class META-INF/versions/9/org/bouncycastle/crypto/digests/SHAKEDigest: resolution
class META-INF/versions/9/org/bouncycastle/crypto/digests/KeccakDigest: resolution
class META-INF/versions/9/org/bouncycastle/crypto/digests/SHA3Digest: resolution
class org/bouncycastle/jcajce/provider/asymmetric/edec/BC11XDHPublicKey: resolution
class org/bouncycastle/jcajce/provider/asymmetric/edec/BC11XDHPrivateKey: resolution
class org/bouncycastle/jcajce/provider/asymmetric/edec/BC15EdDSAPublicKey: resolution
class org/bouncycastle/jcajce/provider/asymmetric/edec/BC15EdDSAPrivateKey: resolution
class META-INF/versions/9/module-info: creation (createFailed)
exiting : Failures occurred during processing
Can you please let me know how to proceed further on this? Appreciate your help on this.
Hi Vimal,
which version of the bouncy castle jar are you using? It might make sense to use an older version, as usually the JVM in the oracle database is not the most recent, so a recent version of the bouncy castle jars might make use of Java language constructions not yet supported by the JVM in the database.
I hope, this helps!