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.
I think it makes perfect sense. You didn't loose any data. You retrieved redacted data from the table and then used that data to perform an update. Maybe you didn't alter the data yourself, but your data was altered by the database while retrieving.
ReplyDeleteIf this wasn't the case, you might get to the data using this as a workaround.
It makes sense from a technical standpoint: of course the "xx" are written back to the database because that what you tell it to do.. It doesn't make sense from a logical standpoint: data that you are not allowed to see (hence redacted) you would also not be allowed to modify.
DeleteThis post was just meant as a warning: some applications out there do an update regardless of whether the data was changed by the user or not. If you do, you might end up with a situation that you didn't call for.