oracle 12c masking,Oracle data masking

This problem is easily solved in 12c with the function STANDARD_HASH.

The solution in previous versions is only slightly more complicated. Build a simple wrapper around DBMS_CRYPTO that acts just like STANDARD_HASH:

--Imitation of the 12c function with the same name.

--Remember to drop this function when you upgrade!

create or replace function standard_hash(

p_string varchar2,

p_method varchar2 default 'SHA1'

) return varchar2 is

v_method number;

v_invalid_identifier exception;

pragma exception_init(v_invalid_identifier, -904);

begin

--Intentionally case-sensitive, just like the 12c version.

if p_method = 'SHA1' then

v_method := dbms_crypto.hash_sh1;

--These algorithms are only available in 12c and above.

$IF NOT DBMS_DB_VERSION.VER_LE_11 $THEN

elsif p_method = 'SHA256' then

v_method := dbms_crypto.hash_sh256;

elsif p_method = 'SHA384' then

v_method := dbms_crypto.hash_sh384;

elsif p_method = 'SHA512' then

v_method := dbms_crypto.hash_sh512;

$END

elsif p_method = 'MD5' then

v_method := dbms_crypto.hash_md5;

else

raise v_invalid_identifier;

end if;

return rawToHex(dbms_crypto.hash(utl_raw.cast_to_raw(p_string), v_method));

end;

/

You may need to logon with SYS and grant your user access to DBMS_CRYPTO to make the function work:

grant execute on sys.dbms_crypto to ;

Create a public synonym, grant it to everyone, and it works exactly the same way.

create public synonym standard_hash for .standard_hash;

grant execute on standard_hash to public;

select standard_hash('Some text', 'MD5') from dual;

9DB5682A4D778CA2CB79580BDB67083F

select standard_hash('Some text', 'md5') from dual;

ORA-00904: : invalid identifier

Here is a simple example of using the function:

update some_table

set column1 = standard_hash(column1),

column2 = standard_hash(column2);

But updating large amounts of data can be slow. It may be faster to create a new table, drop the old one, rename the new one, etc. And the hash value may be larger than the column size, it may be necessary to alter table some_table modify column1 varchar2(40 byte);

It amazes me how many products and tools there are to do such a simple thing.