Algorithm for masking postal code with specifics exclusions

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
Hello all,

I have a special demand to mask code postal values within an Oracle table. I tried to make a SM algorithm to answer, without success 'til now :(

Here are the specifications :
- The field to mask is a varchar2(5) column with only "number" between "01000" and "99999"

- the rules to mask this field are :
1 - Every numbers after the 2 firsts ones are masked to zero
2 - Values above 99000 must be exclude (never masked)

So for exemple

01234 -> 01000
12345 -> 12000
66666 -> 66000
99123 -> 99123 (not masked)
99666 -> 99666 (not masked)

etc.

Is it possible with the masking engine only ? (without add triggers in the database, for example)

Regards,
Nicolas

PS : I was able to make a SM algo to mask everthing after the 2 firsts char. (thank for community help : https://community.delphix.com/delphix/topics/masking-with-zero), but i can't exclude the 99xxx codes
Photo of Nicolas Ronayette

Nicolas Ronayette

  • 206 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Mayank Ahluwalia

Mayank Ahluwalia

  • 194 Points 100 badge 2x thumb
Hello Nicolas,

I would do the following :
1. In the ruleset, put the filter condition to exclude all rows that have postal code > 99000
2. Create a SM algorithm with 2 segments , length 2 and 3 respectively, keep them as alphanumeric
3. Preserve the 1st 2 characters from getting masked

This should solve the problem at hand

Thoughts ?

Regards,
Mayank
Photo of Gianpiero Piccolo

Gianpiero Piccolo

  • 1,314 Points 1k badge 2x thumb
In the ruleset configuration, you can add a filter to your table: COLNAME not like '99___'
If you have other columns to be masked in the same table, then you'll have to create two rulesets: one for this column and the other one for the remaining columns without filter.
Photo of Mouhssine SAIDI

Mouhssine SAIDI

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

You can use filters with your ruleset definition to subset rows to be masked

https://docs.delphix.com/docs-old/delphix-masking/masking-engine-user-guide/masking-engine-component...

You can user the custom sql filter for example where rows <99000 to get only lower values subjet to masking.

,Regards,

Mouhssine
Photo of Nicolas Ronayette

Nicolas Ronayette

  • 206 Points 100 badge 2x thumb
Thanks a lot both of you
I didn't think about SQL filtering.
I will have to make 2 ruleSets and 2 jobs : One for code postal, one for all the "name" and "address" columns.
And be sure to avoid deadlock that thoses jobs are not running at the same time.

Regards
Photo of Mayank Ahluwalia

Mayank Ahluwalia

  • 194 Points 100 badge 2x thumb
Of course custom mapplet is another way as Gianpiero mentioned. It is my fav method too :)
Photo of Nicolas Ronayette

Nicolas Ronayette

  • 206 Points 100 badge 2x thumb
The tables are really heavy tables (milliions of rows), so adding column etc. is risky.

Do you have an url showing example of custom mapplet implementation ?

Regards,
Photo of Mayank Ahluwalia

Mayank Ahluwalia

  • 194 Points 100 badge 2x thumb
Custom mapplets are done by Delphix Services, so you would need to reach out to your Delphix contact :)
Photo of Nicolas Ronayette

Nicolas Ronayette

  • 206 Points 100 badge 2x thumb
Ok, thanks for your answer, i will try to get more information bout this.
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,672 Points 4k badge 2x thumb

Hi Nicolas,

Just to complete Mayank's answer.

Mapplets are custom dev. you supply from delphix's PS service, so you will have to pay for this service as it isn’t free.

Regards,

Mouhssine