Depersonalize data in Oracle table [message #406041] |
Mon, 01 June 2009 22:23  |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi,
We imported production data to development database and want to depersonalize the sensitive data in a table.
For instance, in a customer table, values in columns such as Account number, Name, Address has to be modified to some other values.
Also these tables have dependencies with other table on Account number column which also has to be changed to implement the joins.
Can anyone provide your expertise, the ways how we can implement this?
|
|
|
|
|
|
Re: Depersonalize data in Oracle table [message #406083 is a reply to message #406041] |
Tue, 02 June 2009 02:42  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I've done this a couple of times, and the best approach I've found is this:create global temporary table obfusc_driver
(rnum number
,rid rowid)
on commit delete rows;
create index o_dr_idx on obfusc_driver(rid,rnum);
create global temporary table obfusc_data
(rnum number
,col_name varchar2(30)
,str varchar2(200)
,dte date)
on commit delete rows;
create index od_str_idx on obfusc_data(rnum,col_name);
insert into obfusc_driver (rnum,rid)
select rownum
,rowid
from person;
insert into obfusc_data (rnum,col_name,str,dte)
select row_number() over (order by forename)
,'FORENAME'
,forename
,null
from person
union all
select row_number() over (order by SURNAME desc)
,'SURNAME'
,surname
,null
from person
union all
select row_number() over (order by birthdate)
,'BIRTHDATE'
,null
,birthdate
from person;
update person p
set (forename
,surname
,birthdate)
= (select os1.str
,os2.str
,os3.dte)
from obfusc_driver od
,obfusc_data os1
,obfusc_data os2
,obfusc_data os3
where od.rid = p.rowid
and od.rnum = os1.rnum
and od.rnum = os2.rnum
and od.rnum = os3.rnum
and os1.col_name = 'FORENAME'
and os2.col_name = 'SURNAME'
and os3.col_name = 'BIRTHDATE');
This lets you sort the firstname,surname and dates of birth into (in this case) increasing, decreasing, and date order respectively across all the rows in the table.
@Blackswan - good to see you keeping up the tradition of help free posts.
[Updated on: Tue, 02 June 2009 03:22] Report message to a moderator
|
|
|