Masking an IDENTITY Column (KBA7266)

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Date	Release
Oct 18, 2023	16.0.0.0
Sep 21, 2023	15.0.0.0
Aug 24, 2023	14.0.0.0
Jul 24, 2023	13.0.0.0
Jun 21, 2023	12.0.0.0
May 25, 2023	11.0.0.0
Apr 13, 2023	10.0.0.0 \| 10.0.0.1
Mar 14, 2023	9.0.0.0, 9.0.0.1
Feb 13, 2023	8.0.0.0
Jan 12, 2023	7.0.0.0
Releases Prior to 2023
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, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
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	The values in an IDENTITY column are auto-generated by the database. This KBA describes issues related to masking an IDENTITY column and how to work around the fact that an IDENTITY column can not be masked.
Applies to	These procedures apply to all databases and have been verified on the following databases: Oracle MS SQL Server SAP ASE (Sybase) MySQL PostgreSQL DB2 Maria DB
Algorithm	Referential Integrity is a hard requirement when masking the values in an IDENTITY column. The Algorithm needs to be one of the following: OOTB: Character Mapping Algorithm OOTB: Segment Mapping (v1 and v2) OOTB: Mapping Algorithm (v1 and v2) Custom Algorithm developed specifically for a Key (Primary or Foreign) column
Steps	Below are database-agnostic steps on how to mask the values in an IDENTITY column using In-Place masking (On The Fly masking will require similar steps): 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 on an existing column. Since this depends on the database 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.
More info	For troubleshooting help: KBA: How to Troubleshoot Masking Jobs and Access Masking Logs (KBA7988) For information about other column types and Rule Sets: KBA: Masking a Column With a Primary Key (PK) (KBA1776) KBA: Masking a Column with a Foreign Key (KBA7286) KBA: Masking a Column with an Index (KBA7295) KBA: Algorithm: Summary (KBA4066) Docs: Managing Rule Sets

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 (OTF) are similar - the IDENTITY column needs to be dropped and replaced with a new column.

Example - MS SQL Server

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

In this example, note the following usage:

'table_name' - the name of the table.
'IDENTITYColumn' - the name of the IDENTITY column.
'tempColumn' - the name of the temporary column (to be masked)
'newIDColumn' - the new IDENTITY column, likely the same name as the old IDENTITY column.

Steps

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;