oracle-databaseplsqltemp-tablesanonymize

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


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.


Solution

  • 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