Oracle data masking

问题:

We have one requirement to mask a particular table column using a Oracle function which gives persistent masked output string.

• We tried Oracle Hash Function but it does not give String type return value.
• We tried Oracle Random function (dbms_random.string) but it does not give Persistent output string.

I read on internet that this is called deterministic masking. But we do not want to use Oracle Enterprise Manager; however we require a direct Oracle function.

Please suggest.

回答1:

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 <your_schema>;

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

create public synonym standard_hash for <schema with function>.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.

回答2:

If you looking something like mask the production data to move it into non-prod for integration testing. Below the "user defined" function would be helpful to you. This function will work only 10G and above.

create or replace function scrubbing(word in varchar2)
return varchar2
as
each_var char(2);
final_val varchar2(100);
complete_data varchar2(4000);
each_word varchar2(1000);
cursor val is select substr(replace(word,' ','#'),-level,1)  from dual connect by level<=length(word);
begin
open val;
--final_val:= '';
loop
    fetch val into each_var;
    exit when val%NOTFOUND;
    --dbms_output.put_line(each_var);
    final_val := trim(final_val)||trim(each_var);
    --dbms_output.put_line(final_val);
    select regexp_substr(final_val,'[A-Za-z]+') into each_word from dual;
    select replace(translate(final_val,each_word,dbms_random.string('L',length(word))),'#',' ') into complete_data from dual;
end loop;
return complete_data;
end;    

回答3:

In Oracle 12C dbms_redact.add_policy is available. It can be used to get the masked value in the select query itself.

回答4:

You can use dbms_crpyto package of oracle , first you need to convert varchar2 type to raw then mask the data according to the hash value.