data scrambling on last names

Hi,

I have table called 'Elector' which has the following structure

(Elms_elector_id integer  (pk)
First_name varchar2(20)
Last_name varchar2(20)

I need to send that database to vendor but due to the security purposes, i want to scramble the last_names. e.g
Table A
First_name    last_name
DOROTHY     HODGSON
ELIZABETH    HOLMES
TONY             ZAK

to somthing like
First_name  Last_name
DOROTHY    ZAK
ELIZABETH   HODGSON
DOROTHY     HOLMES

the database is SQL server 2005. Any help would really be appriciated

LVL 1
nocinfospanAsked:
Who is Participating?
 
ava23Connect With a Mentor Commented:
Just do this to randomize FirstNames too:
"Select First_name, Last_name, A.Elms_elector_id, B.Elms_elector_id  from
(select ARow = row_number() over (order by rand()) , First_name, Elms_elector_id from Elector)A
left join
(select BRow = row_number() over (order by rand()), Last_Name, Elms_elector_id from Elector)B
on ARow = BRow"

Add similar joins for other columns you want to randomize:
left join
(select CRow = row_number() over (order by rand()), dateofbirth, Elms_elector_id from Elector)C
on BRow = CRow"

The key here is to include the Elms_elector_id in each join so that you can unscramble later
0
 
tickettCommented:
Do you need to unscramble them at a later date? If not, why not just make them all "Jones" or blank or something?
0
 
BrandonGalderisiCommented:
Or... if you have an identity column, and it's OK for them to all be unique.

update YOURTABLE
set lastname = 'LASTNAME_' + ltrim(str(YOURIDENTITYCOLUMN))

If you want them to be more distributed, use the first character of the real last name.

update YOURTABLE
set lastname = left(lastname,1) + '_LASTNAME_' + ltrim(str(YOURIDENTITYCOLUMN))

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BodestoneCommented:
though given last name is to be masked then using it as a base might not be such a good idea.

How about the below.

Okay you would have DOROTHY DOOROTHYSON but hey....

UPDATE MyTable
SET LastName = FirstName + 'SON'

Open in new window

0
 
BrandonGalderisiCommented:
Bodestone...

So you are saying that using the first letter of each last name in order to have index distribution would in some way allow the consumer to infer the original last name.
0
 
BodestoneCommented:
OK, I later saw that the second part had only the first letter. and totally misread the literal as the column name in the first. Many appologies.
0
 
lammy82Commented:
This will scramble them....

update elector set last_name =  (select top 1 last_name from elector order by newid())
0
 
blandyukCommented:
I tried that by using it in a select statement, it ends up changing all the last_names to the same name :) doesn't work properly.
0
 
ava23Commented:
If you dont want to unscramble, you can send this:

Select First_name, Last_name  from
(select ARow = row_number() over (order by First_name) , First_name  from Elector)A
left join
(select BRow = row_number() over (order by rand()), Last_Name from Elector)B
on ARow = BRow

else send this:

Select First_name, Last_name, A.Elms_elector_id, B.Elms_elector_id  from
(select ARow = row_number() over (order by First_name) , First_name, Elms_elector_id from Elector)A
left join
(select BRow = row_number() over (order by rand()), Last_Name, Elms_elector_id from Elector)B
on ARow = BRow

You can simply map A.Elms_elector_id  with B.Elms_elector_id to unscramble
0
 
nocinfospanAuthor Commented:

 
Hi ava23,
 
thanks for the script, i executed the following script that you posted in my testing environment
"Select First_name, Last_name, A.Elms_elector_id, B.Elms_elector_id  from
(select ARow = row_number() over (order by First_name) , First_name, Elms_elector_id from Elector)A
left join
(select BRow = row_number() over (order by rand()), Last_Name, Elms_elector_id from Elector)B
on ARow = BRow"

 
I have one addtional request to make, how can I scramble both the first and last names (as before it was only last_name) randomly without effecting the 'Elms_elector_id' column. what i mean is the Elector Ids remain same only the first and last names are scrambled randomly

also I have Data_of_birth_year (smallint) and date_of_birth_month (smallint) fields. Can I also randomly scramble them ?
 
Thanks a lot in advance.
 
 

 
0
 
BrandonGalderisiCommented:
The problem with rand() is that it is the same within a batch.  So using it as a sort will not provide you with any additional randomness than just last_name, elms_elector_id alone.
0
 
BodestoneCommented:
There is a trick to that and that is creatign a view that has is select SELECT(rand()) AS rand
But also you can use a newID() and ORDER BY in CTEs to get nice randomisation.
0
 
nocinfospanAuthor Commented:
Thanks for all the help.
 
 
0
All Courses

From novice to tech pro — start learning today.