Home » SQL & PL/SQL » SQL & PL/SQL » Depersonalize data in Oracle table
Depersonalize data in Oracle table [message #406041] Mon, 01 June 2009 22:23 Go to next message
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 #406044 is a reply to message #406041] Mon, 01 June 2009 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://forums.oracle.com/forums/thread.jspa?threadID=907969&tstart=0
Re: Depersonalize data in Oracle table [message #406046 is a reply to message #406044] Mon, 01 June 2009 22:39 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Swan ,

I have posted the same...

Thank you,
Re: Depersonalize data in Oracle table [message #406066 is a reply to message #406041] Tue, 02 June 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_random.

Regards
Michel
Re: Depersonalize data in Oracle table [message #406083 is a reply to message #406041] Tue, 02 June 2009 02:42 Go to previous message
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

Previous Topic: Installing DBMS_XMLSCHEMA
Next Topic: How can I fiddle with date in procedure
Goto Forum:
  


Current Time: Sun Dec 04 02:26:50 CST 2016

Total time taken to generate the page: 0.08952 seconds