SSN field changer/scrambler needed

We  have 2 oracle data bases one is for production and one is a test with the same user info in both.  we would to make some sort of script or function to scramble the ssn field on our test database.  thanks for your help let me know if you have any questions.
LVL 1
nelslarsonAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
depends on your seed, all you have to do is try it

SELECT   obfuscate_ssn(123456789, 0),
         obfuscate_ssn(123456789, 1),
         obfuscate_ssn(123456789, 20),
         obfuscate_ssn(123456789, 198746385)
  FROM   DUAL;


with the original function
 
 you'll get 
123456789   123456790  123456809   322203174
 
 
with the second version...
 
 
you'll get
 
987654321  097654321  908654321  471302223

Open in new window

0
 
sdstuberCommented:
does the end result have to be a different ssn?  or can it be any number at all?  does it have to be a number?

are the ssn's  stored as numbers?  or as strings in 123-45-6789  format?  
0
 
nelslarsonAuthor Commented:
Its a text field in the format of 123456789 it needs to be nine digits
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
sdstuberCommented:
A simple one is to merely do a rotation.


CREATE OR REPLACE FUNCTION obfuscate_ssn(p_ssn IN INTEGER, p_seed IN VARCHAR2)
    RETURN VARCHAR2
IS
BEGIN
    RETURN TO_CHAR(MOD(p_ssn + p_seed, 1000000000), 'fm000000000');
END obfuscate_ssn;

if you want something more random,  I recommend creating a translation table.
then do a lookup.  generating a random cycle on the fly and iterating through it for each number will be very slow.
0
 
nelslarsonAuthor Commented:
so wouldit be like this 123456789 converted into 987654321
0
 
sdstuberCommented:
you could do a reversal too, yes.

that would work for all ssn's that aren't palindromes.  222454222

the obfuscate_ssn would generate different values depending on your seed.

you would pick a single seed (in which case you could remove the parameter and put it in as a constant)

and use it for all the ssn's.


you could include a reversal too if you wanted


CREATE OR REPLACE FUNCTION obfuscate_ssn(p_ssn IN INTEGER, p_seed IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_temp     VARCHAR2(9) := TO_CHAR(MOD(p_ssn + p_seed, 1000000000), 'fm000000000');
    v_result   VARCHAR2(9) := NULL;
BEGIN
    FOR i IN 1 .. 9
    LOOP
        v_result   := v_result || SUBSTR(v_temp, 10 - i, 1);
    END LOOP;

    RETURN v_result;
END obfuscate_ssn;

0
 
nelslarsonAuthor Commented:
so the rotation solution if a ssn was 123456789 what would the it change the field to ?
0
 
sdstuberCommented:
if you want to make it harder to decipher what the rotation seed is, you can call it multiple times.
note, the p_seed should have been declared as a number in all examples above,  I've corrected it here...

also note, if anyone knows the obfuscation algorithm, simple trial and error will decipher the correct seed.  even with repeated calls.  The recursive method below doesn't really make it more secure, it just makes it less "human-eye" decipherable.


CREATE OR REPLACE FUNCTION obfuscate_ssn(p_ssn IN INTEGER, p_seed IN NUMBER)
    RETURN VARCHAR2
IS
    v_temp     VARCHAR2(9) := TO_CHAR(MOD(p_ssn + p_seed, 1000000000), 'fm000000000');
    v_result   VARCHAR2(9) := NULL;
BEGIN
    FOR i IN 1 .. 9
    LOOP
        v_result   := v_result || SUBSTR(v_temp, 10 - i, 1);
    END LOOP;

    IF p_seed > 2
    THEN
        v_result   := obfuscate_ssn(v_result, FLOOR(p_seed / 2));
    END IF;

    RETURN v_result;
END obfuscate_ssn;
SELECT   obfuscate_ssn(123456789, 0),
         obfuscate_ssn(123456789, 1),
         obfuscate_ssn(123456789, 20),
         obfuscate_ssn(123456789, 198746385)
  FROM   DUAL;
 
 
987654321  097654321  333456814  503620388

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.