oracle - hashing method, excluding DBMS_CRYPTO

Question

BACKGROUND:

DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :(

---

Is there any way to get the same hash for the text field with the same text? All below methods give different results for the same text as they seem depending on rowid, or something similar:

SELECT
 , utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg))                                                  AS "RAW"
 , rawtohex(utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))                                        AS raw_hex
 , rawtohex(standard_hash(sys_op_map_nonnull(log_msg), 'MD5'))                                       AS md5_hex
 , ora_hash(log_msg)
 , dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))           AS md5
 , rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))) AS hex

log_msg is CLOB

Answer 1

• DBMS_OBFUSCATION_TOOLKIT was deprecated and replaced by DBMS_CRYPTO.
• ORA_HASH does not produce a unique checksum, per se, and does not support LOB types.
• STANDARD_HASH uses DBMS_CRYPTO under the hood, but also does not support LOB types.

Having recently faced a similar problem myself, I was able to get execute privileges on DBMS_CRYPTO for the application schema by demonstrating the need. The DBAs didn't have any problem granting the access as long is it was required and documented. It's the only real option for what you're trying to do. My function to produce a SHA256 hash wound up looking like this:

function get_sha256sum (p_clob in clob) return raw
is
begin
    return dbms_crypto.hash (src => utl_raw.cast_to_raw(p_clob), typ => 4);
end get_sha256sum;

Note: don't try to cast the raw checksum to anything else - it's only accurate as a checksum in the original format.

Answer 2

DBMS_OBFUSCATION_TOOLKIT is no longer available on any currently supported versions of Oracle Database.

My recommendation is for you to upgrade your DB so that you can use DBMS_CRYPTO.