Skip to main content
Delphix

Masking with Filtering and Subsetting Rule Sets (KBA6828)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA# 6828

At a Glance

Summary: This KBA describes how to assign different Masking Algorithms to the same Field/Column by adding a Filter to the Rule Set. This can be used to mask just specific records or if different records need to be masked with different algorithms based on the value in a key. 
Use cases: Filtering use cases: 
  1. Mask data using different algorithms based on a specific rule.
    • Example: Mask First Name based on Gender.
    • Example: Mask PII data in one country (or define different rules per country). 
       
  2. Reduce the number of records in the masked dataset (OTF only).
    • Reduce 1 billion rows to a table with 1 million rows.
Methods: Database Masking has two methods to achieve this: 
  1. Rule Set - Filter (preferred)
  2. Rule Set - Custom SQL

File Masking has one method to achieve this:

  1. Record Type Filtering

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0

5.3

 5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Database Filtering

Database Filtering works a bit differently between In-Place (IP) and On-The-Fly (OTF) jobs.

Some terms used in this KBA: 

  • Filter Branch - This is a Filter Configuration with its unique Rule Set and Algorithm configuration. 
  • Unmasked Branch - A 'Filter Branch' that is not masked (branch with no algorithms defined).

In-Place Filtering 

Each Filter Branch in the logic needs its own Rule Set and Masking Job.

  1. Create a Rule Set for each Filter Branch.
  2. Edit the Rule Set and add a Filter or Custom SQL with the WHERE predicate.
  3. Define Algorithms in the Inventory.
  4. Create a Masking Job.

Repeat the steps above for each Filter Branch.

Important Notes 
  • Only Masked Branches need to be included.
    • Unmasked branches do not need to be included. 
  • Do not use UNIONS as they can cause deadlocks.
  • If Custom SQL is used, all masked columns and the key need to be in the result set. 
  • The filter columns only need to be in the WHERE predicate. 
  • Filter column(s) can also be masked.

Examples

Rule Set - Filter
[filter_col] like '%xyz%'
Rule Set - Custom SQL
SELECT msk_col1, msk_col2,..., ID_key FROM tbl WHERE [filter_col] like '%xyz%'

Note

Notes:

  • Custom SQL requires all masked columns and the URI (ID) in the SELECT.
  • Custom SQL cannot be used if 'MASKING_GENERATED_IDENTITY_TMP' (formerly called 'DMS_ROW_ID') is used. If needed, the URI needs to be created manually before the job starts. See DMS_ROW_ID KBA link below for details.

 

On-The-Fly Filtering

Every Filter Branch in the logic needs its own Rule Set and Masking Job (including the Unmasked Branch).

  1. Create a Rule Set for each Filter Branch.
  2. Edit the Rule Set and add a Filter or Custom SQL with the WHERE predicate.
  3. Define Algorithms in the Inventory. 
  4. Create a Masking Job.

Repeat the steps above each Filter Branch.

Unless the dataset should be reduced - the Unmasked Branch needs to have a separate Rule Set and Job.

Important Notes
  • The first job should have TRUNCATE.
    • All other branches (jobs) should not truncate.
  • The Unmasked Branch should have a blank Inventory (no algorithms defined) (see note below). 
  • If Custom SQL is used then all columns in the table need to be included ('*' can be used).
  • Filter column(s) can also be masked.

Examples

Rule Set - Filter
[filter_col] like '%xyz%'
Rule Set - Custom SQL
SELECT * FROM tbl WHERE [filter_col] like '%xyz%'
Note

Note:

Since all columns in the table need to be moved to the target, using the '*' is possible.

Unmasked Branch (and NULLs)

If the Filter Column(s) has NULL values, these are likely not included in the SELECT result set. In order to include all Unmasked records use this tip:

  • Use NOT IN and the SELECT from the masked Filter Branches.
Oracle

Below is an example of NOT IN using Oracle.

SELECT * FROM tbl WHERE ROWID NOT IN (SELECT ROWID FROM tbl WHERE [filter_col] like '%xyz%')
SQL Server and Other

Below is a general example for SQL Server and other databases. Note that in SQL Server and most other databases, a Unique Row Identifier (URI) is needed.

In the example below, ID has been used as URI. Please change as needed. 

SELECT * FROM tbl WHERE ID NOT IN (SELECT ID FROM tbl WHERE [filter_col] like '%xyz%')

 

Related Articles

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

 

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp