Algorithm: Secure Lookup (SL) (KBA1243)

Applicable Delphix Versions 

From 6.0.4 the Secure Lookup moved to a new Framework. All upgraded engines will have the old SL upgraded to the new Framework automatically. 

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
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

At a Glance

Creating and Modifying Algorithms using the User Interface

The algorithms are accessed from the Settings tab >  Algorithm. A custom algorithm can be created or modified.

• To create click Add Algorithm.
• To modify click the edit icon the Edit column.

User Interface: 6.0.11 and Newer

The new create Secure Lookup Algorithm dialog looks like this.

Creation 

When creating a Secure Lookup Algorithm, you need to provide details for Algorithm Properties:

• Algorithm Name.
• Description.
• Output (Masked) Case. 
• Hash Method (how the lookup id is calculated).
• Case Sensitive Lookup.
• The algorithm lookup file (loaded from a text file).
   

Modification  

When modifying an algorithm, the following details can be modified:

• Description.
• Output (Masked) Case. 
• Hash Method (how the lookup id is calculated).
• Case Sensitive Lookup.
• The algorithm lookup file (loaded from a text file).

 

 

Note:

To modify the algorithm name, the algorithm needs to be deleted and recreated.

User Interface: Previous to 6.0.11

The following dialog is displayed when creating and modifying the algorithm.

Creation 

When creating a Secure Lookup Algorithm, you need to provide details for Algorithm Properties:

• Algorithm Name.
• Description.
• The algorithm lookup file (loaded from a text file).

Modification

When modifying an algorithm, the following details can be modified:

• Description.
• The algorithm lookup file (loaded from a text file).

Note:

To modify the algorithm name, the algorithm needs to be deleted and recreated.

Considerations

There are a few considerations:

• Pool Size and Load Time 
• Memory Requirements
• Case Sensitivity
• Masked Data Encoded with Non-Standard Code Page

SELECT ID, UPPER(maskMeCol) as maskMeCol from myTable;

Examples   

The example below displays an example of a masked result.

FIRST NAME SL was used in this example on a column defined with varchar(8).

• Note 1: NULL and white space strings. 
• Note 2: case sensitivity. 
• Note 3: spaces are trimmed.
• Note 4: 1:N mapping - the masked values 'collide'.
• Note 5: strings are cut (cropped) to fit the column (database masking only).

+-----+--------+----------+
| Ref | Source | Masked   |
+=====+========+==========+
| 1   | NULL   | NULL     | << Note 1: NULL and white space strings(' used to indicate space)
| 2   | ''     | ''       | <<    "
| 3   | ' '    | ' '      | <<    "
| 4   | '   '  | ' '      | <<    "
| 5   | Peter  | Flo      |
| 6   | PETER  | Yoshiko  | << Note 2: Case insensitivity examples.
| 7   | peter  | Paul     | <<   "
| 8   |  Peter | Flo      | << Note 3: Leading and/or trailing whitespaces are trimmed.
| 9   | Bar    | Yoshiko  | << Note 4: 1:N Mapping - no unique mapping.
| 10  | Foo    | Christop | << Note 5: Masked value ('Christopher') cropped.
+-----+--------+----------+