Topic
  • 7 replies
  • Latest Post - ‏2015-02-04T12:33:36Z by mariusz.czopinski
prateek0503
prateek0503
31 Posts

Pinned topic Mantaining consistency while masking in different Databases

‏2013-12-12T07:39:01Z |

Hello Everyone,

I faced a question while discussion with one of my friend and would need a help on this" If we have an SSN column in two databases (One is Oracle and sec is SQLServer) and we need to maintain consistency while masking them (is there a way to propagate it from one table on one database to some other table of some other database, suppose we are making it using TRANSSSN)".

Thanks!!

Prateek

  • sundar_chinnagoundan
    11 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2013-12-13T16:47:12Z  

    Prateek, 

    When you are using OPTIM inbuilt function TransSSN, it will yield same masked value across different database technologies even without propagate. You can very well use it separately in two databases and get same masked values.

  • prateek0503
    prateek0503
    31 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2013-12-16T10:51:47Z  

    Prateek, 

    When you are using OPTIM inbuilt function TransSSN, it will yield same masked value across different database technologies even without propagate. You can very well use it separately in two databases and get same masked values.

    Thanks for replying back Sundar…. And you hit the bullseye for TRANSSN

    But I probably gave a wrong example ...sorry for that …  let me try to put it in a different way .

    Suppose we have a field Member_id( just like our Employee Id ) which is unique and is maintained consistently across multiple Databases( HR , Sales, Performance management system etc.). Now while masking it we are generating a random sequence and want that sequence to be maintained across multiple databases.

    How can we achieve this?

    Thanks!!

    Prateek

  • Matthew_Simons
    Matthew_Simons
    27 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2014-01-15T16:40:57Z  

    Thanks for replying back Sundar…. And you hit the bullseye for TRANSSN

    But I probably gave a wrong example ...sorry for that …  let me try to put it in a different way .

    Suppose we have a field Member_id( just like our Employee Id ) which is unique and is maintained consistently across multiple Databases( HR , Sales, Performance management system etc.). Now while masking it we are generating a random sequence and want that sequence to be maintained across multiple databases.

    How can we achieve this?

    Thanks!!

    Prateek

    Prateek,

    For instances like this, where the field to be masked needs to be consistent but doesn't fall into the existing TRANS categories (SSN/NID, Email, CCN) Optim has the Affinity provider otherwise knows as TRANS COL (here is the original note announcing it).  It provides many of the same features and functions as TRANS SSN/TRANS NID but for non-standard formats.  You didn't mention what version of Optim you're using, but hopefully you have support for TRANS COL.

     

    Hope that helps!

    Matt

     

  • Robert Phillips
    Robert Phillips
    5 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2014-01-16T12:39:54Z  

    Thanks for replying back Sundar…. And you hit the bullseye for TRANSSN

    But I probably gave a wrong example ...sorry for that …  let me try to put it in a different way .

    Suppose we have a field Member_id( just like our Employee Id ) which is unique and is maintained consistently across multiple Databases( HR , Sales, Performance management system etc.). Now while masking it we are generating a random sequence and want that sequence to be maintained across multiple databases.

    How can we achieve this?

    Thanks!!

    Prateek

    I hope that I'm not misunderstanding the situation. If I correctly understand the situation, you can create an Optim Access Definition that spans multiple Optim DBAliases.  Your business object can consist of data in multiple tables across multiple databases (across multiple vendors' databases).  You may need to use Optim Relationships to establish the connections between the columns in different tables (especially across databases). You can then extract the data using the Access Definition, run a Convert job to perform a data masking with propagation, which should follow the Optim Relationships, as well as the normal database relationships.  Now, with the masked data in an Optim Extract file, you can run Load or Insert jobs to install the data into the various databases whence it came.

    To be fair, I haven't done this recently, so I may be mis-remembering some details, but I  believe that the general idea is sound.

  • LeonardusGeorge
    LeonardusGeorge
    1 Post

    Re: Mantaining consistency while masking in different Databases

    ‏2014-02-24T14:13:53Z  

    I hope that I'm not misunderstanding the situation. If I correctly understand the situation, you can create an Optim Access Definition that spans multiple Optim DBAliases.  Your business object can consist of data in multiple tables across multiple databases (across multiple vendors' databases).  You may need to use Optim Relationships to establish the connections between the columns in different tables (especially across databases). You can then extract the data using the Access Definition, run a Convert job to perform a data masking with propagation, which should follow the Optim Relationships, as well as the normal database relationships.  Now, with the masked data in an Optim Extract file, you can run Load or Insert jobs to install the data into the various databases whence it came.

    To be fair, I haven't done this recently, so I may be mis-remembering some details, but I  believe that the general idea is sound.

    Hi Robert, please check the propagate function. I believe this will fit your needs. It will keep primary/foreign keys consistend across the federated datamodel.

  • sundar_chinnagoundan
    11 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2014-03-15T23:59:45Z  

    I hope that I'm not misunderstanding the situation. If I correctly understand the situation, you can create an Optim Access Definition that spans multiple Optim DBAliases.  Your business object can consist of data in multiple tables across multiple databases (across multiple vendors' databases).  You may need to use Optim Relationships to establish the connections between the columns in different tables (especially across databases). You can then extract the data using the Access Definition, run a Convert job to perform a data masking with propagation, which should follow the Optim Relationships, as well as the normal database relationships.  Now, with the masked data in an Optim Extract file, you can run Load or Insert jobs to install the data into the various databases whence it came.

    To be fair, I haven't done this recently, so I may be mis-remembering some details, but I  believe that the general idea is sound.

    Prateek,

    Using OPTIM API you can achieve this. User defined or predefined functions can be called from the applications directly or from the columnmap. This feature in OPTIM 9.1 should address your need. I haven't tried it yet though.

    Hope it helps.

  • mariusz.czopinski
    mariusz.czopinski
    3 Posts

    Re: Mantaining consistency while masking in different Databases

    ‏2015-02-04T12:33:36Z  

    I hope that I'm not misunderstanding the situation. If I correctly understand the situation, you can create an Optim Access Definition that spans multiple Optim DBAliases.  Your business object can consist of data in multiple tables across multiple databases (across multiple vendors' databases).  You may need to use Optim Relationships to establish the connections between the columns in different tables (especially across databases). You can then extract the data using the Access Definition, run a Convert job to perform a data masking with propagation, which should follow the Optim Relationships, as well as the normal database relationships.  Now, with the masked data in an Optim Extract file, you can run Load or Insert jobs to install the data into the various databases whence it came.

    To be fair, I haven't done this recently, so I may be mis-remembering some details, but I  believe that the general idea is sound.

    Hi Robert,

    The Opitm Relationships can be used between databases for data masking propagation. I checked it and it works. But for the scenario when you need to propagate masked addresses bind with user_id for example to various databases the best way is to use lookup function. I'm using it in the following way:

    1. Masking addresses in table A in database A for user_id.

    2. Do Optim relations for table A in database A with table B and database B

    3. Extracting data from table A in db A and table B in db B using created relation

    4. Masking table B in db B using lookup function based on user_id to get masked address value from table A from db A

     

    Best regards,

    Mariusz