How to mask part of primary key and still be unique?

I have a table where the primary key consist of 2 fields; a birth date and a sosial security number SSN (5 digits int).
I want to keep the birth date, and susbstitue the SSN, but there is some additional  requirements:
  1. The combination of birth date and SSN must be unique (primary key)
  2. The third digit of SSN must be unchanged, because it indicates gender, odd digit for male
  3. The other 4 numbers of the SSN shall be substituted with random numbers.
I think i have to use substitution because i have to syncronize this change Table-To-Table with a Sync Manager

Is this possible with Data Masker and is there an alreday defined dataset that i can use when my SSN field is an integer?

Answers

  • Eddie DEddie D Posts: 1,802 Rose Gold 5
    Hi Terje,

    There is certainly a SSN Numbers dataset that you can make use of it.

    Please take a look at this help document which maybe useful to you, in the scenario you have described.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • TheMaskedDataTheMaskedData Posts: 53 Bronze 3
    @Terje you might need to use a Row-Internal Rule converted to a Sync Manager rule, then you can use SQL syntax to effectively have a replacement value clause of SUBSTRING(SSN, Parts 1 - x of SSN that you want to keep) + DMSPARAM1 where DMSPARAM1 is a randomly generated 4 digit number :smile:
  • henrich45henrich45 Posts: 1 New member
    Terje said:
    I have a table where the primary key consist of 2 fields; a birth date and a sosial security number SSN (5 digits int).
    I want to keep the birth date, and susbstitue the SSN, but there is some additional  requirements:
    1. The combination of birth date and SSN must be unique (primary key)
    2. The third digit of SSN must be unchanged, because it indicates gender, odd digit for male
    3. The other 4 numbers of the SSN shall be substituted with random numbers.
    I think i have to use substitution because i have to syncronize this change Table-To-Table with a Sync Manager  Is this possible with Data Masker and is there an alreday defined dataset that i can use when my SSN field is an integer?

    Yes, it's possible to achieve this with Data Masker. You can use substitution rules to replace parts of the SSN while maintaining the third digit to indicate gender. Since the SSN field is an integer, you can define specific rules to substitute the other digits while keeping the combination of birth date and SSN unique as the primary key.

    To ensure the third digit remains unchanged (for gender), you could write a custom rule that targets only the first two and last two digits for substitution. However, Data Masker doesn't have an out-of-the-box dataset specifically for this, so you'll likely need to configure the masking rule manually for this particular format.

    And just like how worms adapt to different environments, you can adapt these masking rules to fit your data requirements!

Sign In or Register to comment.