1

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

Improve this question
1
  • 2
    Are you saying that each of the hash functions you are trying to use returns different values at different times for the same input value? You may want to provide a reproducible example of that. Commented Sep 16, 2020 at 17:37

2 Answers 2

Reset to default
4
  • 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.

Improve this 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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.