Delphix Products

Expand all | Collapse all

Algorithm for masking postal code with specifics exclusions

Gianpiero Piccolo

Gianpiero Piccolo04-24-2018 12:53:00 PMBest Answer

  • 1.  Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 12:23:00 PM
    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


    #Masking


  • 2.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 12:28:00 PM
    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


  • 3.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 12:28:00 PM
    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.


  • 4.  RE: Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 12:46:00 PM
    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


  • 5.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 12:49:00 PM
    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


  • 6.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 12:53:00 PM
    I can confirm all you wrote.
    Another way could be the custom mapplet.


  • 7.  RE: Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 12:55:00 PM
    Well there is a way by which you can do all of this in just one masking job. It will involve the use of pre and post scripts though. 

    So, you create a post script that does the below :
    1. Add another column to the table, say postal_code_temp
    2. update postal_code temp = postal_code
    3. update postal_code = null for all rows where postal_code > 98999

    4. run your masking by applying the postal code SM on postal_code column only

    Then have a post script that does below :

    5. update postal_code = postal_code_temp only for rows which have postal_code = null
    6. drop the additional added column

    Should work :-)

    Regards,
    Mayank


  • 8.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 12:56:00 PM
    Of course custom mapplet is another way as Gianpiero mentioned. It is my fav method too :)


  • 9.  RE: Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 01:03:00 PM
    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,



  • 10.  RE: Algorithm for masking postal code with specifics exclusions
    Best Answer

    Posted 04-24-2018 01:07:00 PM
    Custom mapplets are done by Delphix Services, so you would need to reach out to your Delphix contact :)


  • 11.  RE: Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 01:18:00 PM
    Ok, thanks for your answer, i will try to get more information bout this.


  • 12.  RE: Algorithm for masking postal code with specifics exclusions

    Posted 04-24-2018 01:29:00 PM

    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