Skip to main content
Delphix

KBA1789 Masking and Database Schema Limitations

 

 

Issue

Sometimes the databases to be masked use different schemas (or owners) across groups of tables. This knowledge article  explains the limitations and how to work around them.

The term used here will be schema. In Oracle this refers to the owner.

Applicable Delphix Versions

This article applies to all versions of the Masking Engine.

Connector Limitations 

The limitation in accessing different schemas is derived from the Masking Engine's Database Connector. The Database Connector specifies the schema name in the JDBC connection and this is being used to create the Rule Set.

Example: MS SQL

As we can see here there are one-to-one links between the Schema, the Connector, the Rule Set, and the Job. 

Masking - Schema - MS Connector - Rule Set - Job.png

The key objects links here are:

  • One-to-one:
    • db.Schema   <--->   connector.Schema 
    • connector.Connector Name   <---->   ruleset.Connector Name
    • ruleset.Rule Set Name   <---->   job.Rule Set Name
  • Many-to-many:
    • db.Tables   <--->   ruleset.Tables

Example: Oracle 

Oracle is slightly different as Schema here refers to the Owner. From the query that is executed to get the tables in the Rule Set (seen below) we can also see that Synonyms will not work here:

SELECT DISTINCT table_name 
  FROM all_tables 
  WHERE owner = '[SCHEMA_NAME]';

Masking - Schema - Oracle Connector - Rule Set - Job.png

The key objects links here are:

  • One-to-one:
    • db.Owner   <--->   connector.Schema 
    • connector.Connector Name   <---->   ruleset.Connector Name
    • ruleset.Rule Set Name   <---->   job.Rule Set Name
  • Many-to-many:
    • db.Tables   <--->   ruleset.Tables

Workaround 

There is no easy way to get around this limitation. Below are some suggestions.

The suggestions are:

  • Manually create Connectors, Rule Sets, and Jobs - one for each Schema.
  • Use API to create Connectors, Rule Sets, and Jobs - one for each Schema.
  • Use API to change Schema in one Connector.
Manually create Connectors, Rule Sets, and Jobs - one for each Schema
  • Pros: Straight forward.
  • Cons: Might take too long time. Hard to manage if there are many schemas. 
Use API to create Connectors, Rule Sets, and Jobs - one for each Schema
  • Pros: Can provide automatic creation of Connectors, Rule Set and Jobs - also job Start can be managed with the API.
  • Cons: Will require scripting/development. 
Use API to change Schema in one Connector

This is a special case, if the table definitions and the Rule Set Inventory are the same for all schemas (for example if there is one schema for Dev and one for Test for the same application). 

  • Pros: The change of the Schema in one connector before execution is easier than creating one for all schemas. 
  • Cons: Will require some scripting/development.
     

 


Related Articles

The following articles may provide more information or related information to this article:

 

  • Was this article helpful?