Exclude identifiable customer numbers from masking

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
We have a customer ID field with customer numbers that start with M, but older customers have a social security number instead. We want to mask the ones that are social security numbers but leave the ones starting with an M alone. Is there a way to do that?

Renee
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,314 Points 1k badge 2x thumb
Hi Renee,

sure, you can! 
In the Ruleset page, enter in edit page and then click on edit table icon. In the popup, select "filter" tab and add your where clause in order to select only record with ID value starts with 'M%' (without WHERE word).
Regards.
Gianpiero
Photo of Mike Donnelly

Mike Donnelly

  • 166 Points 100 badge 2x thumb
Yes! Just use a filter in the Ruleset for that table: something like 'CUSTOMERID NOT LIKE 'M%'
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,314 Points 1k badge 2x thumb
without WHERE word: "CUSTOMERID  NOT LIKE 'M%'"
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Worked like a charm! Thank you both so much!

Renee
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
How would you write the filter if you had 2 fields in the same table that need the filter? For example, the table has a primary ID field and also a spouse ID field, and both need to be left alone if they start with an 'M%'. I have the filter statement saying: Primary_ID not like 'M%' but not sure how to add the second field to that statement. Not all of the records have this second 'spouse' field, and some records have a SSN in primary Id and an 'M%' in spouse ID (or vice versa)., so using an 'AND spouse_ID not like 'M%' won't work. Any suggestions?

Thank you,
Renee
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,682 Points 4k badge 2x thumb
Hi,

Just change the « AND » in your where condition with « OR ».

This because you want records that sweet on or the other condition not both at the same time (if I got it right).

Regards

Mouhssine
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Thank you Mouhssine, but it seems like using the OR would allow the masking to change people's M#s if their spouse had an SSN, wouldn't it? For example:

Primary_ID = M1234567
spouse_ID =  333-22-4444

If my filter says <<Primary_ID not like 'M%'  OR Spouse_ID not like 'M%' >> wouldn't the masking logic decide it was okay to mask both of these fields in the record since the filter was true (for the spouse)? Or does the masking know which field was true and which was not true in the filter? Seems like I need a filter that I can enter by field instead of by table. 

Any thoughts? I may not be understanding the filters correctly.
Renee
Photo of Mike Donnelly

Mike Donnelly

  • 166 Points 100 badge 2x thumb

Hi Renee, I this situation you could just mask the table using two separate masking jobs - one job to mask each field.

Mike

Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,682 Points 4k badge 2x thumb

Hi Reene,

Can you please explain with a sample example covering all cases your need, I'm afraid didn't get your need correctly.

Regards,

Mouhssine

Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Mike, I think that would work, although pretty messy when I have many instances of this happening, but thank you for the suggestion. I may have to resort to that.

Renee
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Mouhssine,

These two fields are located in the same Customer_Info record. Here are the possible scenarios:

Do not mask either field:
Primary_ID = M1234567 
Spouse_ID = M9876543

Mask the Spouse_ID but not the Primary_ID:
Primary_ID = M1234567
Spouse_ID = 333-22-4444

Mask the Primary_ID but not the Spouse_ID:
Primary_ID = 999-88-7777
Spouse_ID = M5678901

Mask both fields:
Primary_ID = 555-44-3333
Spouse_ID = 111-22-8888

How would you write a filter to get these two fields masked so that the M% numbers are preserved and the SSN numbers are masked?

Thanks for your help,
Renee
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,314 Points 1k badge 2x thumb
Hi Renee,

it seems that you're falling into the case of the two separated rulesets:
You have to create two separated rulesets:
1. Ruleset_name: "Customer_Info_Primary_ID", Filter:  "Primary_ID  NOT LIKE 'M%'", Inventory: "Mask Primary_ID"
2. Ruleset_name: "Customer_Info_Spouse_ID", Filter:  "Spouse_ID  NOT LIKE 'M%'", Inventory: "Mask Spouse_ID"

Then create two separated masking jobs, one for each ruleset.
Avoid to execute the two jobs in parallel, you could get dead lock error at the rdbms level.

Hope this help you.

Regards.
Gianpiero
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Gianpiero,

I was beginning to suspect that. Thank you for confirming it.

Renee
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,314 Points 1k badge 2x thumb
Should be an idea to ask for new smart masking algorithms that mask with a condition...
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Yes! That would be very useful and make the tool much more powerful. I have setup the redundant rule sets and jobs. Now let's hope I can schedule these to not create deadlocks. I really do appreciate your help and insights very much.

Renee
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,682 Points 4k badge 2x thumb
Hi Renee,


As per definition Delphix masking is a operating per column and have no builting relational capability.


The only way you can achieve your need is to follow gainpiero’s recommandations hi own wide experience with masking ;).


Regards,


Mouhssine
(Edited)
Photo of Mayank Ahluwalia

Mayank Ahluwalia

  • 678 Points 500 badge 2x thumb
Another option is to create 2 algorithms for the 2 columns in the same table and since we only need to exclude at the column level, we could customize the created algorithm with an if-else to exclude those values from being masked. This resolves the conflict when >1 column in the same table has to be filtered.

of course, customization is slightly complex and will need Delphix services to help with that, but if there aren't many such customisations, it can be done to avoid creating huge number of rulesets
(Edited)
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Thank you Mayank. That sounds like an interesting option. I will keep that in mind as I progress with this issue.

Thanks,
Renee