数据屏蔽在Oracle SQL select语句中。

Without using PL/SQL, is it possible to do data masking in SELECT statement?

在不使用PL/SQL的情况下，是否可以在SELECT语句中执行数据屏蔽?

For example:

例如:

(AS-IS) SELECT 'this is a string' from DUAL;

(AS-IS)选择“this is a string”from DUAL;

this is a string

这是一个字符串

(TO-BE) SELECT 'this is a string' from DUAL;

(TO-BE)选择'this is a string' from DUAL;

xxxx xx x xxxxxx

2 个解决方案

#1

REGEXP_REPLACE can do this:

REGEXP_REPLACE可以这样做:

SELECT REGEXP_REPLACE('this is a string', '\w', 'x') FROM DUAL;

This replaces all non-whitespace characters with an x. To replace letters only, try this:

这将用一个x替换所有非空白字符。

SELECT REGEXP_REPLACE('this is a string', '[A-Za-z]', 'x') FROM DUAL;

#2

You can create user defined function as below and call that function in your query to mask the data.

您可以创建用户定义的函数，并在查询中调用该函数来屏蔽数据。

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;

#1