Scrub sensitive data so data is not available for test/dev
Need to scrub password hash value in a table with single hash value for all 40 million rows so that test/dev databases doesn't have
this sensitive data. This table has around 125 columns and about 40 indexes. The password column has only 1 index on it. The scrub script has to run once a day on staging environment from where all dev/test databases will be refreshed from. Below are some ideas i have to implement but i would like to hear from you if you have any idea which is efficient to scrub data every day in efficient way.
1) Drop the index on the column and Run a pl/sql script with bulk collect and it takes around 2 hours to finish and create a index once data in updated, below is the code. I have tested this code and it works i modified the code below so that i cannot list the table/column names in the code.
cursor c1 is
select c.id from table_name c
where c.password_column is not null;
TYPE t_id IS TABLE OF table_name.password_column%TYPE;
v_id t_id;
begin
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_id LIMIT 50000;
EXIT WHEN v_id.count = 0;
FORALL i in 1..v_id.count
update table_name set password_column = '5f4dcc3b5a765d2cf99'
where id = v_id(i);
commit;
end loop;
commit;
close c1;
end;
2) Tried to set up VPD policy but i can only get the VPD to work so that the column data is null if users query the data. I'm not sure if there is a way to create a VPD for all rows
in the column with single value in it.
Finally any recommendations to make this process more efficient would be helpful.