Skip to main content
Delphix

Masking and Database Schema Limitations (KBA1789)

 

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

Note

Note:

The term used within this article will be schema. In Oracle "schema" refers to the owner.

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 is used to create the Rule Set.

Example: MS SQL

As we can see here, there are one-to-one links between the Schema, Connector, 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" 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. Some 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