Oracle: Pure PL/SQL data extraction and anonymization using temporary tables, read-only permissions

Question

I am trying to create a PL/SQL script that extracts a root "object" together with all children and other relevant information from an oracle production database. The purpose is to create a set of test-data to recreate issues that are encountered in production. Due to data protection laws the data needs to be anonymized when extracted - object names, certain types of id's, and monetary amounts need to be replaced.

I was trying to create one or more temporary translation tables, which would contain both the original values and anonymized versions. Then I would join the real data with the translation tables and output the anonymized values wherever required.

DECLARE
  rootId integer := 123456;

  TYPE anonTableRow IS RECORD 
  (
    id NUMBER,
    fieldC NUMBER,
    anonymizedFieldC NUMBER
  );

  TYPE anonTable IS TABLE OF anonTableRow;
  anonObject anonTable;
BEGIN

  FOR cursor_row IN 
  (
    select 
     id,
     fieldC,
     1234 -- Here I would create anonymized values based on rowNum or something similar
    from 
    prodTable
    where id = rootId
  ) 
  LOOP       
    i := i + 1;
    anonObject(i) := cursor_row; 
  END LOOP;

  FOR cursor_row IN 
  (
    select 
    prod_table.id,
    prod_table.fieldB,
    temp_table.anonymizedFieldC fieldC,
    prod_table.fieldD
    from 
    prod_table
    inner join table(temp_table) on prod_table.id = temp_table.id
    where prod_table.id = 123456789
  ) 
  LOOP       
   dbms_output.put_line('INSERT INTO prod_table VALUES (' || cursor_row.id || ', ' || cursor_row.fieldB || ', ' || cursor_row.fieldC || ', , ' || cursor_row.fieldD);
  END LOOP;
END;
/

However I ran into several problems with this approach - it seems to be near impossible to join oracle PL/SQL tables with real database tables. My access to the production database is severely restricted, so I cannot create global temporary tables, declare types outside PL/SQL or anything of that sort.

My attempt to declare my own PL/SQL types failed with the problems mentioned in this question - the solution does not work for me because of the limited permissions.

Is there a pure PL/SQL way that does not require fancy permissions to achieve something like the above?

Please Note: The above code example is simplified a lot and would not really require a separate translation table - in reality I need access to the original and translated values in several different queries, so I would prefer not having to "recalculate" translations everywhere.

score 1 · Accepted Answer · answered Jan 07 '16 at 10:52

If your data is properly normalized, then I guess this should only be necessary for internal IDs (not sure why you need to translate them though).

The following code should work for you, keeping the mappings in Associative Arrays:

DECLARE
  TYPE t_number_mapping IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  mapping_field_c   t_number_mapping;
BEGIN
  -- Prepare mapping
  FOR cur IN (
    SELECT 101 AS field_c FROM dual UNION ALL SELECT 102 FROM dual -- test-data
  ) LOOP
    mapping_field_c(cur.field_c) := mapping_field_c.COUNT;  -- first entry mapped to 1
  END LOOP;

  -- Use mapping
  FOR cur IN (
    SELECT 101 AS field_c FROM dual UNION ALL SELECT 102 FROM dual -- test-data
  ) LOOP
    -- You can use the mapping when generating the `INSERT` statement
    dbms_output.put_line( cur.field_c || ' mapped to ' || mapping_field_c(cur.field_c) );
  END LOOP;
END;

Output:

101 mapped to 1
102 mapped to 2

Thanks, this approach looks like it might be usable for me, I'll have to do some testing for the more advanced mappings though. — cornergraf, Jan 07 '16 at 11:26

score 1 · Answer 2 · answered Jan 07 '16 at 10:52

If this isn't a permanent piece of production code, how about "borrowing" an existing collection type - e.g. one define in SYS that you can access.

Using this script from your schema you can generate a SQL Plus script to describe all SYS-owned types:

select 'desc ' || type_name from all_types
where typecode = 'COLLECTION'
and owner = 'SYS';

Running the resulting script will show you the structure of all the ones you can access. This one looks potentially suitable for example:

SQL> desc KU$_PARAMVALUES1010
 KU$_PARAMVALUES1010 TABLE OF SYS.KU$_PARAMVALUE1010
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PARAM_NAME                                         VARCHAR2(30)
 PARAM_OP                                           VARCHAR2(30)
 PARAM_TYPE                                         VARCHAR2(30)
 PARAM_LENGTH                                       NUMBER
 PARAM_VALUE_N                                      NUMBER
 PARAM_VALUE_T                                      VARCHAR2(4000)

Of course, you can't guarantee that type will still exist or be the same or be accessible to you after a database upgrade, hence my caveat at the start.

score 0 · Answer 3 · answered Jan 07 '16 at 14:35

More generic way to achieve this goal. In my example i'm using xquery flwor expressions and dbms_xmlstore. Knowledge about xquery is mandatory.

create table mask_user_objects as select * from user_objects where rownum <0;

declare 
   v_s_table varchar2(30) := 'USER_OBJECTS';   --uppercase!!!
   v_d_table varchar2(30) := 'MASK_USER_OBJECTS';  --uppercase!!!
   v_mask_columns xmltype := xmltype('<COLS><OBJECT_NAME>XXXX</OBJECT_NAME>
                                            <DATA_OBJECT_ID>-1</DATA_OBJECT_ID>
                                            <OBJECT_TYPE/>                                            
                                      </COLS>');  --uppercase!!!   
   insCtx DBMS_XMLSTORE.ctxType;
   r NUMBER;
   v_source_table xmltype;
   v_cursor sys_refcursor; 
begin 
   open v_cursor  for 'select * from '||v_s_table||' where rownum <100 ';  
   v_source_table := xmltype(v_cursor);
   close v_cursor; 
   -- Load source table into xmltype. 
   insCtx := DBMS_XMLSTORE.newContext(v_d_table); -- Get saved context
  for rec in ( 
   select tt.column_value from  xmltable('
                  let $col := $anomyze/COLS
                  for $i in  $doc/ROWSET/ROW           
                   let $row := $i  
                   return <ROWSET>             
                            <ROW>                                         
                                {                                                                
                                 for $x in $row/* 
                                  return if(
                                       exists($col/*[name() = $x/name()] )                                                                                             
                                  ) then element{$x/name()}{ $col/*[name() = $x/name()]/text() }  
                                    else element{$x/name()}{$x/text()}                   
                                }                   
                            </ROW>             
                         </ROWSET>
                  '
                  passing v_source_table as "doc"
                       ,  v_mask_columns as "anomyze"
                  ) tt) loop
                  null;
             r := DBMS_XMLSTORE.insertXML(insCtx,  rec.column_value);
end loop;
 DBMS_XMLSTORE.closeContext(insCtx);      

end;

Thank you for taking the time to try and help. I'm afraid that your solution looks more complex than what I am willing to try out right now, so I cannot verify whether it merits an upvote for the time being. I hope I'll be able to check it out in the future though. — cornergraf, Jan 08 '16 at 10:05

Oracle: Pure PL/SQL data extraction and anonymization using temporary tables, read-only permissions

3 Answers3