3

I want to encrypt some data with SHA-512 on Oracle 12.

For example, I would like to encrypt this string: 

230049008251111

to this:

DA9AA3ACDE64DB3297FF75FDE407DAF3DB7EFF0CDF987C6C16BAF28242997046997EBF5A2F6C4F7449A4936C6518A6FD24A3C0984E9C09BF19395175F1BE2B5F

This is according to sha512 generator I'm using. What is the best way to do so on Oracle?

Thanks

Maarten Bodewes
  • 90,524
  • 13
  • 150
  • 263
shir
  • 63
  • 1
  • 3
  • 1
    First of all - do not store passwords as SHA2, use [HMAC](https://en.wikipedia.org/wiki/HMAC). You can use Java based stored procedure/trigger to perform such a hashing, our you can found PLSQL encryption at github (not recommended). As well as host application can hash password before storing it into database. – Victor Gubin Dec 04 '18 at 10:13
  • 2
    Thanks Victor! This is not a password. I thought about Java, but I prefer to use Oracle if possible. I need this in order to simulate behavior of a system that uses this sha512. I searched on google and found some hashing methods but I didn't succeed to adjust it to my case. – shir Dec 04 '18 at 10:18
  • 1
    Java VM is a part of Oracle. You can write [Oracle Stored Procedure](https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm) using [PLSQL](https://www.oracle.com/technetwork/database/features/plsql/index.html) or [Java](https://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm) or [C/C++](https://docs.oracle.com/cd/B28359_01/server.111/b28318/data_access.htm#CNCPT1759) programming languages. – Victor Gubin Dec 04 '18 at 11:16
  • @VictorGubin - There's no value in us rolling our own routines when Oracle has hashing functions built in. – APC Dec 04 '18 at 11:38
  • 3
    [hashing ≠ encryption](https://stackoverflow.com/q/4948322/1679849) – r3mainer Dec 04 '18 at 13:44

1 Answers1

11

Oracle has built-in database support for SHA-512.

If you want a solution for pure SQL and your input string is within the SQL string length limit (4000 characters unless you've enabled the 12c extended varchar2 semantics) you can do this:

select standard_hash ( '230049008251111', 'SHA512')
from dual
/

If you have longer strings Oracle provides a package DBMS_CRYPTO (not to be confused with the deprecated DBMS_CRYPTO_TOOLKIT). You could use it like this ...

declare
     str clob;
     hsh raw(64);
begin      
     str := '230049008251111';
     hsh := dbms_crypto.hash (str, dbms_crypto.HASH_SH512);
     dbms_output.put_line(hsh);
end;
/

... or whatever makes sense for your application. There are three variants of dbms_crypto.hash() which take BLOB, CLOB and RAW. There is no variant for plain old varchar2. Not sure why. Find out more.

Both these calls produce the same output (as might be hoped):

DA9AA3ACDE64DB3297FF75FDE407DAF3DB7EFF0CDF987C6C16BAF28242997046997EBF5A2F6C4F7449A4936C6518A6FD24A3C0984E9C09BF19395175F1BE2B5F

As @WernfriedDomscheit observes, execute on DBMS_CRYPTO is not granted by default. However, STANDARD_HASH() is. So, depending on your requirements, you may need to engage with your DBA team to get the necessary privileges.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I tried to do so, but I got this: too many declarations of 'HASH' match this call – shir Dec 04 '18 at 10:26
  • 4
    Just a note, by default the normal user has no execute privileges on `DBMS_CRYPTO` package. You may ask your DBA to grant it. – Wernfried Domscheit Dec 04 '18 at 10:35
  • @shir - if this answer was helpful please consider accepting it. Accepted answers improve the quality of this site for future Seekers. – APC Dec 04 '18 at 11:39