My session on "Oracle 12c for Developers" is done. Afterwards someone asked the question:
What happens when you use DBMS_REDACT with a complete row update?My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:
create table emp
as
select ename
,to_char (abs (dbms_random.random)) credit_card
from scott.emp
;
begin
dbms_redact.add_policy
(object_schema => 'A'
,object_name => 'EMP'
,policy_name => 'Hide Creditcard'
,expression => '1=1'
,column_name => 'CREDIT_CARD'
,function_type => dbms_redact.regexp
,regexp_pattern => dbms_redact.re_pattern_any_digit
,regexp_replace_string => 'X'
);
end;
/
First to create a test table (of course called EMP) in schema "A". Next to place a DBMS_REDACT policy on it which replaces the credit_card information with X for each digit.
Just to verify that it works:
SQL> select * 2 from a.emp 3 / ENAME CREDIT_CARD ---------- -------------------- SMITH XXXXXXXXXX ALLEN XXXXXXXXX WARD XXXXXXXXXX JONES XXXXXXXXX MARTIN XXXXXXXXX BLAKE XXXXXXXXXX CLARK XXXXXXXXXX SCOTT XXXXXXXXX KING XXXXXXXXXX TURNER XXXXXXXXXX ADAMS XXXXXXXX JAMES XXXXXXXXX FORD XXXXXXXXX MILLER XXXXXXXXX 14 rows selected.
Now for the row update without actually changing any data.
SQL> declare 2 l_emp a.emp%rowtype; 3 begin 4 select ename, credit_card 5 into l_emp.ename, l_emp.credit_card 6 from a.emp 7 where ename ='SMITH'; 8 update a.emp 9 set row = l_emp 10 where ename = 'SMITH'; 11 end; PL/SQL procedure successfully completed.
The code above will update the information for SMITH without altering the data.
And now to unveil the actual data which is in the table, first remove the redaction
SQL> begin 2 dbms_redact.drop_policy (object_schema => 'A' 3 ,object_name => 'EMP' 4 ,policy_name => 'Hide Creditcard'); 5 end; 6 / PL/SQL procedure successfully completed.
and inspect the data:
SQL> select ename 2 ,credit_card 3 from a.emp 4 / ENAME CREDIT_CARD ---------- -------------------- SMITH XXXXXXXXXX ALLEN 146486740 WARD 1079838967 JONES 204239028 MARTIN 784659193 BLAKE 2086063983 CLARK 1949626638 SCOTT 736597519 KING 1541186772 TURNER 1456281762 ADAMS 61726886 JAMES 729938493 FORD 238314859 MILLER 714890479 14 rows selected.And there you have it.. looks like we lost some information along the way.
