What are the challenges you face when working across database platforms? Take the survey

updated table model - same rules?

Not sure how this will translate in text, but here goes.

v1 of a data model has a table [address], which I've masked with various rules.
v2 of the data model has a table [address_version]; this table is not exactly the same as v1 [address]; all of the columns from v1 are present but v2 has an additional set of columns to manage row versioning (not my design, btw).

When I open the v1 ruleset to review for changes (after refreshing the tables, indexes, row counts, etc. in the controller) and I want to repoint the rules for [address] to [address_version] -- I expected to be able to just point the rule to the new table name and make any changes -- or in this case, none -- to the rule's definition, save the rule with the same definition but pointed to a new table name, and move on to the other 500 tables to review.

What's happening is that I click on the new table name ([address_version]) to point the rule to it, the definition is lost.  What I mean by 'lost' is that [column] and [datasets] values are replaced with '<unknown>'; I then have to recreate the rule, nearly from scratch.  But hey - I get to keep the name of the rule, so there's that.  :cake:

I've opened this post to ask two questions; 1) is it possible to point an existing rule to a new table and preserve its definition, and 2) if I can, how?

Unfortunately, this is not a stale data model and I'm told I should expect 2-3 updates, per year.  This means 2-3 updates to the rulesets per year -- and I'm hoping to be more efficient with this work than starting over with only the name of the rule.

Best Answer

  • Options
    PlantBasedSQLPlantBasedSQL Posts: 187 Gold 4
    Hi @don_don

    Thank you for your post! I'm hoping you can clarify something for me before I try to help or feed this back to the development team - there's one thing I'm struggling to understand.

    If you have [address] and [address_version] on the same DB - is there any reason why you cannot simply create a new rule pointed at address_version to mask that at the same time as address? 

    Or are you looking to simply reuse this rule for the other table so that it makes tackling the other 500 tables easier?

    As for the 2-3 updates per year, does that mean specifically to the schema, therefore you'll have to update the masking set, i.e. to capture new tables and columns?

    In any case I will try to answer your questions:

    There is currently no way within the UI to easily clone a rule and re-point it at another table with a similar definition... however if we're clever about this we COULD manipulate the DMSMASKSET file VERY carefully to achieve what you're looking for.

    You would have to create a rule targeting [address], then hit "clone rule" so you had 2 identical rules targeting [address] - save this down and then open the DMSMaskSet file in Notepad++ or VSCode, find the second rule and change the table name.

    I tried this myself with a couple of example tables:

    and it worked just fine:

    I realize that modifying the XML is not _ideal_ but it is a short term workaround at the very least.

    I should also say, that 500 Tables is a lot to be masking, and it seems unrealistic to go through each and every one to generate masking rules for them - have you considered using SQL Data Catalog alongside Data Masker? It has an integration that would allow you to generate masking rules automatically based on your classifications of those tables!

    Let me know if you'd like some more information!



  • Options
    don_dondon_don Posts: 7 Bronze 1
    Hello, PlantBasedSQL!  Thank you for taking the time to respond to my post.  I apologize that I wasn't very clear.

    [address] and [address_version] are not in the same data model - at least not at the same time.  v1 of the data model contains [address] with 12 columns.  v2 of the data model has [address_version] with 16 columns; the first 12 are exactly the same in the same ordinal order as [address] - the last 4 are date columns used to version the rows.  [address] is dropped from the model and replaced because [address_version] is the 'upgrade' to [address].

    Since [address_version] is the 'upgrade' I had hoped I could just point the [address] rule to [address_version] and move on.  The rules for [address_version] will remain the same as [address] since the additional date columns are non-sensitive.

    Regarding the 500 tables - that was a tongue-in-cheek reference to the overall data model.  I am at the mercy of a third-party vendor who is still in the process of maturing their model and offering new features as other clients request them -- all of which translates to a more fluid model that one might expect from such a provider.  I do have a lot of tables to review, but the vast majority of them do not contain sensitive data.  But as the data model is 'upgraded', I need to review (and where necessary) modify the Masker ruleset.

    I am at a sort of borderline; there are enough tables to mask and review on each 'upgrade' that I might be justified in integrating Data Catalog -- but not quite so much as to justify the distraction from my other tasks to integrate Data Catalog.  I appreciate the reminder about Data Catalog, though, thank you for mentioning it.

    If we're talking about putting this in front of the dev team, it would be a very helpful feature to be able to 'map' prior-version tables and rules to new-version tables and rules, in bulk.  So long as I'm dreaming.  :cake:

    Anyway, directly modifying the xml in DMSMASKSET seems to be my short-term way forward.  I am comfortable with json and xml, so this shouldn't pose too big a hurdle - I just didn't know I had that sort of access.  I'll be sure to be careful.  :)

    So now that I've written a novel, I'll just say thank you again for your time to research this and provide a solid suggestion to move my projects forward; I'll give that a whirl.
  • Options
    don_dondon_don Posts: 7 Bronze 1
    Hello, PlantBasedSQL!  It looks like your suggestion works and will fill the gap until an interface-based solution is provided.  Thanks again for the time you spent putting this together and for sharing your expertise.
  • Options
    My pleasure @don_don
    Glad the workaround worked out for you! Sorry it's not as simple as it should be but happy to keep you working forwards with it!
    Let me know if there's anything I can do to help out further!
  • Options
    don_dondon_don Posts: 7 Bronze 1
    Hello, PlantBasedSQL!  It looks like your suggestion works and will fill the gap until an interface-based solution is provided.  Thanks again for the time you spent putting this together and for sharing your expertise.  You may mark this issue resolved.
  • Options
    Fabulous @don_don glad to hear it! Thanks for letting me know :simple smile:

    Have a great week!
Sign In or Register to comment.