APIPythonOpen-Source Solutions

Expand all | Collapse all

How to filter out the required tables from huge set of tables in rule set screen

  • 1.  How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 12:53:00 PM
    We have 10808 table in in DB, we have got the query to filter out the table which are in scope there are 1642 tables, now we want to run the profile job on these tables only so how we can run profile and Masking job on these table only? As per my understanding we have to select the 1642 tables from rule set manually

    Please let me know with detail steps 
    #Masking


  • 2.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 01:18:00 PM
    Hello 
    Could you please let me know ...!!


  • 3.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 01:37:00 PM
    Could you please help?? or we dont have any functionality in Delphix ? any active member in this community ?


  • 4.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 03:59:00 PM
    Hi Pankaj,
    You can create a user that only has access to the tables you need.  Thereby ensuring that you don't need to select them in the GUI.
    If you are using version 5.3.2 then you might also consider using the API, which might allow you to build the appropriate ruleset programmatically.
    Regards,
    Gary


  • 5.  RE: How to filter out the required tables from huge set of tables in rule set screen



  • 6.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 04:34:00 PM
    So Pankaj,
    Having a quick 10 minute look at the documentation whilst on the train, I think you just need to create a bit of JSON that will update your connector with the right values using the API Operation: /masking/api-client/#!/tableMetadata/updateTableMetadata.
    I appreciate that these solutions aren't perhaps ideal for you but shouldn't take too long to put in place.  How do you know what tables you would like to mask? If you have all the table names listed then you could build something very quickly, before dinner.
    Let me know how you get on.
    Regards,
    Gary


  • 7.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-22-2018 06:28:00 PM
    Note you'll need to login (authenticate) for your API calls:

    https://docs.delphix.com/docs/delphix-masking/masking-api-cookbook/the-masking-api-client

    This is the key page to update metadata for your connector as Gary outlines:
    https://docs.delphix.com/docs/delphix-masking/masking-api-cookbook/api-calls-for-creating-an-invento...

    The only real variable is the table name. If you're a good coder, you can simply imbed that into an array and loop through it.  These snippets will help you:

    https://docs.delphix.com/docs/delphix-masking/masking-api-cookbook/sample-cookbook-scripts/createinv...

    https://docs.delphix.com/docs/delphix-masking/masking-api-cookbook/sample-cookbook-scripts/helpers

    If you're more like me....

    If you have that list of tables in an Excel sheet, you can but that into column B and put your partial API commands in columns A and C.  Then Concatenate them into column D, and this should give you a list of 1642 API calls.  Watch out for fancy quotes (you don't want them, and can either disable them on your PC or fix them with find and replace once you take your script to your shell prompt).

    The API call Gary listed above is actually a link on your machine to a tool which will help you get started with the syntax, but you probably won't use that for the final solution.  

    I am also going to submit an RFE on your behalf.  I'm sorry this isn't in the product yet, it's a great idea.



  • 8.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-23-2018 06:40:00 AM
    Pankaj,
    If you send me the list of tables off-line we can knock up a script that will build your ruleset for you - I think this might be the best way forwards if you have no access into the database to create an appropriate user to filter at the database level.
    Regards,
    Gary


  • 9.  RE: How to filter out the required tables from huge set of tables in rule set screen
    Best Answer

    Posted 03-24-2018 10:37:00 AM
    So, just to wrap this up.
    You can create a Ruleset using the Masking API.  If you are clever then you can build all sorts of capability and build your Rulesets dynamically.  We have developed in-house a way of extracting massive lists of databases and their connection strings to dynamically and automatically profile your database estate for sensitive data.  This will automatically build an inventory on which you can also automatically run a masking job. Obviously you will need to check your inventory for false positives and false negatives using a human or artificial intelligence...
    In this case Pankaj sent me a list of tables and I sent him back a list of curl commands that will update his ruleset, with all the tables that are relevant to profile/mask.
    As I'm not proficient at bash programming, I simply used the http://<your masking engine>:8282/masking/api-client to guide me.  To login to that client and get an authentication code you need to read the documentation or follow this video that I created.

    The curl command syntax to add tables to a ruleset is shown below - download a sample file here.

    # 1. You need to change 47eba9e5-3ee3-4d3d-8299-2c0700a1a619 to your 
    # authorisation code.
    # 2. You need to change the “rulesetId”: 1 to the correct number for the 
    # ruleset you want to change.  You can see that in the UI.
    # 3. You need to change the Engine IP Address and port to your
    # engine ID and port
    #
    # Before running this you should login and get the authorisation code.
    # You could do that on the masking/client-api
    # You just need to run this on a command line like ./Table_names_json.txt
    # You will need curl installed on your machine that you run this from.
    # You could run a script that will take the table_name from an Excel or 
    # CSV file but I don’t have much recent experience with something like 
    # that.
    #


    curl -s -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: 47eba9e5-3ee3-4d3d-8299-2c0700a1a619' -d '{ "tableName": "TABLE_17”, "rulesetId": 1 }' 'http://192.13.12.100:8282/masking/api/table-metadata'

    Regards,
    Gary



  • 10.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-25-2018 09:04:00 AM

    Hi Gary,

    I do get the same requests from some customers, to build masking API scripts that mimics the multitenant function we have using the GUI.

    The idea is defining one ruleset and render it dynamic for all vdbs created based on the same dsource.

    Actually I’m on rush until the end of month and have not a lot time to take a closer look on it, but interested in sharing any ideas/codes on this subject.

    Regards,

    Mouhssine 



  • 11.  RE: How to filter out the required tables from huge set of tables in rule set screen

    Posted 03-25-2018 09:02:00 PM
    Hi Mouhssine,
    I'm not exactly clear on what you need.  I think you might be saying that you want to force a masking ruleset to be applied whenever a VDB is provisioned from a particular dSource?  Can we discuss offline - send an email.  We can pick it up back here when we get some clarity.
    Regards,
    Gary