Goldengate Data Masking

April 6, 2023
Categories: GoldenGate

Goldengate Data Masking & obfuscation

GoldenGate data masking offers features that enable secure replication of sensitive data from source to target systems. Data masking involves substituting sensitive data with alternative values such as XXXX thus preventing unauthorized access to the original data.

Data obfuscation is typically used to transform data in a way that makes it difficult to understand or interpret, while masking is used to conceal specific data elements that are considered sensitive or confidential. Both data masking and data obfuscation are techniques used to protect sensitive data by hiding or altering it in some way.

In this article we will cover the process of transforming and masking data values, with the purpose of concealing them from downstream applications, such as reporting, analytics and development team. This helps organizations to protect confidential information while still allowing developers, testers and Business team to work with realistic data sets.

There are two methods of sending sensitive data to downstream systems.
Filter the data while extracting from the source. Filter the data while Appling it to target.

ARCHITECTURE

DATA BACKGROUND

Here is the production data containing personally identifiable information (PII) such as identification types including social security number (SSN), driver’s license number (DL), and employer identification number (EIN).

Now we would like to send the data to downstream for reporting by masking & transforming the data in column ID_Number.

LOGIC : Convert or transform the PER_ID number as an actual ID_Number and display only the last 4 digits and masking all other numbers as XXX-XX-XXXX

FILTERING IN EXTRACT

Create a stored procedure in source database to mask & obfuscate the data and call it in extract.

				
					CREATE OR REPLACE PROCEDURE MASK_ID_NUMBER(
    P_ID_TYPE IN VARCHAR2,
    P_ID_NUMBER IN VARCHAR2,
    P_MASKED OUT VARCHAR2
) AS
BEGIN
    CASE
        WHEN TRIM(P_ID_TYPE) = 'SSN' THEN
            P_MASKED := ( SUBSTR('XXX', 1, 3)
                          || '-'
                          || SUBSTR('XX', 1, 2)
                          || '-'
                          || SUBSTR(P_ID_NUMBER, 8, 4) );
        WHEN TRIM(P_ID_TYPE) = 'EIN' THEN
            P_MASKED := ( SUBSTR('XXX', 1, 3)
                          || '-'
                          || SUBSTR('XX', 1, 2)
                          || '-'
                          || SUBSTR(P_ID_NUMBER, 8, 4) );
        WHEN TRIM(P_ID_TYPE) = 'DL' THEN
            P_MASKED := ( SUBSTR('XXX', 1, 3)
                          || '-'
                          || SUBSTR('XX', 1, 2)
                          || '-'
                          || SUBSTR(P_ID_NUMBER, 8, 4) );
    END CASE;
END;
/

EXTRACT PARAMETER

EXTRACT EX_MSK
USERIDALIAS GGSOracle DOMAIN OracleGoldenGate
EXTTRAIL kk
DDL INCLUDE ALL
GETUPDATEBEFORES
GETDELETES
Table GGADMIN.SENDATA,SQLEXEC (ID LOOKUP,SPNAME GGADMIN.MASK_ID_NUMBER,PARAMS (P_ID_TYPE=ID_TYPE,P_ID_NUMBER=ID_NUMBER));

FILTERING IN REPLICAT

				
					REPLICAT RP_MASK
USERIDALIAS ATP DOMAIN OracleGoldenGate
GETUPDATEBEFORES
GETDELETES
MAP GGADMIN.SENDATA, TARGET GGADMIN.SENDATA
SQLEXEC (ID lookup, &
QUERY "select CASE WHEN TRIM(ID_TYPE) = 'SSN' THEN 'XXX-XX'||'-'|| SUBSTR(PER_ID, 6, 4)  WHEN TRIM(ID_TYPE) = 'EIN' THEN 'XXX-XX'||'-'|| SUBSTR(PER_ID, 6, 4)    WHEN TRIM(ID_TYPE) = 'DL' THEN 'XXX-XX'||'-'|| SUBSTR(PER_ID, 6, 4) END AS PER_ID_NBR_NEW from GGADMIN.SENDATA where PER_ID =:v_PER_ID",&
PARAMS (v_PER_ID = PER_ID)),&
COLMAP (USEDEFAULTS, ID_NUMBER=@getval(lookup.PER_ID_NBR_NEW));

In my experience, it is more efficient to perform the data conversion at the replicat rather than at the extract side. However, the choice ultimately depends on individual preferences and circumstances.

It is recommended to thoroughly test both options and implement the most suitable approach for your specific situation.

For further reading on the topic of filtering and transformation of data, you can refer to the MOS Note available.
Primary Note for Oracle GoldenGate for Filtering and Transformation Data (Doc ID 1450495.1)

Oracle provides several data masking tools that seamlessly integrate with other Oracle products, such as Oracle Enterprise Manager, Oracle Data Integrator, and Oracle Database Vault. These tools offer more advanced features and functionality compared to the simple method used in GoldenGate.

Oracle Data Masking can also be used as part of the Data Safe service on Oracle Cloud Infrastructure (OCI). Data Safe automates data masking activities and provides real-time monitoring of data security posture.

While the simple data masking method used in GoldenGate can be useful in certain scenarios, it is not as robust as other Oracle data masking tools. Therefore, it is not recommended to compare this solution with the more advanced data masking products offered by Oracle.

Hope you found this article useful.
Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle..