Masking an IDENTITY Column (KBA7266)

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

Summary:	This page describes issues related to masking an IDENTITY column. An IDENTITY Column can not be masked, but the values in the column can be transferred to a new column.
Applies to:	These procedures apply to all database and have been verified on the following databases: Oracle MS SQL Server SAP ASE (Sybase) MySQL PostgreSQL DB2 Maria DB
Algorithm:	Referential Integrity is usually essential when masking an IDENTITY column. The Algorithm will, therefore, need to be one of these: Character Mapping Algorithm (replaces Segment Mapping) Mapping Algorithm Custom Algorithm developed specifically for this IDENTITY column
Steps:	Below are database agnostic steps on how to mask the values in an IDENTITY column using In-Place masking: Create a Temporary column on the table. Transfer the data from the existing IDENTITY column to the newly created Temporary column. Refresh the Rule Set. Assign the Masking Algorithm to the Temporary column. Remove any algorithm on the Identity column. Run the Masking Job. Drop the IDENTITY column. Create an id column with the same name as the Identity column (this can not be an Identity column). Transfer the masked data from the temporary column into the newly created id column. Drop the temporary column. Some databases will allow defining an IDENTITY column on an existing column. Since this depends on type and version, please consult the database documentation for details. In step 6, if there is an Index on the IDENTITY column, this index needs to be dropped and then recreated on the new id column.

Procedure (In-Place)

The SQL commands issued will differ between database vendors and the steps detailed above are, therefore, database agnostic.

The Masking Engine will use the IDENTITY column as a Unique Row Identifier (URI). The steps for On-The-Fly are similar, as that the IDENTITY column needs to be dropped and replaced with a new column.

Example - MS SQL Server

Below is an example related to MS SQL Server (object names and column types need to be changed accordingly):

Create a Temporary column on the table:

ALTER TABLE table_name ADD tempColumn INT;

Transfer the data from the existing IDENTITY column to the newly created Temporary column:

UPDATE table_name set tempColumn = IDENTITYColumn;

Refresh the Rule Set.
Assign the Masking Algorithm to the Temporary column.
Run the Masking Job.
Drop the IDENTITY column:

ALTER TABLE table_name DROP COLUMN IDENTITYColumn;

Create an id column with the same name as the Identity column (this can NOT be an Identity column):

ALTER TABLE table_name ADD newIDColumn INT;

Transfer the masked data from the temporary column into the newly created id column:

UPDATE table_name set newIDColumn = tempColumn;

Drop the temporary column:

ALTER TABLE table_name DROP COLUMN tempColumn;

KBA

Applicable Delphix Versions

At a Glance

Procedure (In-Place)

Example - MS SQL Server

Related Articles