28 September 2014

DBMS_REDACT and complete ROW update

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.

2 comments:

  1. 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.
    If this wasn't the case, you might get to the data using this as a workaround.

    ReplyDelete
    Replies
    1. 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.
      This 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.

      Delete