1. GDPR process

This module enables the data storage to meet the GDPR compliance requirements by anonymizing historical data in the data warehouse. It consists of multiple components working together to anonymize data. It is designed to anonymize individual data that has been deleted or obscured in source systems. To function correctly, it requires a domain expert familiar with the tables, their meanings and purposes. All configurations are historically recorded to pass potential audit inspections…

1.1. Build GDPR views

Before registering the GDPR objects in the metadata, the application manager has to build the “Trigger” view and the “GDPR” view. The “Trigger” views show all BSK’s of an object, e.g. KUNDE, which need to be anonymized. The view contains the logic how to find these BSK’s and must return at least the distinct RED_BSK column. The “GDPR” views list all rows of the PSA object with all columns to be anonymized. The view may only contain the primary key RED_PK and the anonymizing PSA columns with the logic how the column is anonmyized, e.g. date columns are set to the first day of the year’s decade. It is possible that you can use one “Trigger” view for more than one object, but each object has a related “GDPR” view.

The advantage of building these views is that you can see the probaly results immediate without changing any productive data.

Example for a “Trigger” view

OPEN SCHEMA REDGPR_CUS;

CREATE OR REPLACE VIEW TRG_EMPLOYEES_GDPR
AS
WITH lastgpr AS
    (   -- query for last GDPR run for the selected object
        SELECT
            NVL(MAX(gpr_finish), TO_TIMESTAMP('1800-01-01')) AS last_gpr_run
        FROM
            redgpr.gpr_control
        WHERE
            psa_schema = 'PSA_TEST'
        AND psa_table = 'EMPLOYEES'
        AND trg_schema = 'REDGPR_CUS'
        AND trg_view = 'TRG_EMPLOYEES_GDPR'
        AND gpr_error_code IS NULL
    )
SELECT DISTINCT
    -- get all relevant distinct BSKs, which meet the relevant GDPR criteria
    -- e.g. employee relation ended 7 years ago
    e.red_bsk, 
    l.last_gpr_run, 
    e.enddatum
FROM 
    psa_test.employees e
JOIN
    lastgpr l
ON
    e.red_tdate_from >= l.last_gpr_run
WHERE
    e.enddatum <= ADD_YEARS(SYSDATE, -7)
    AND e.red_gpr_run_id IS NULL          -- already anonymized data have the value of the GDPR run when anonymizing was done
;

Example for a “GDPR” view

OPEN SCHEMA REDGPR_CUS;

CREATE OR REPLACE VIEW TEST_EMPLOYEES_GDPR
AS 
SELECT
    -- query all rows, by select the RED_PK column,
    -- and all relevant columns to be anonymized and the logic for the anonymization
    e.red_pk,
    CAST(TO_NUMBER(NULL) AS DECIMAL(8,2))        AS gehalt,
    CAST(TO_NUMBER(NULL) AS DECIMAL(2,2))        AS provision,
    '*****'                                      AS vorname,
    '*****'                                      AS nachname,
    '*****'                                      AS email,
    '*****'                                      AS telefon,
    DATE_TRUNC ('year', e.einstelldatum)         AS einstelldatum
FROM 
    redgpr_cus.trg_employees_gdpr te
    JOIN 
        psa_test.employees e 
        ON 
            e.red_bsk = te.red_bsk 
        AND e.red_tdate_from >= te.last_gpr_run   -- query all data since last GDPR run
;

When the building of the views are finished and found correctly implemented, the application manager has to add this information to the GDPR metadata using the following API’s

1.2. Adding a “Trigger” view

To add a “Trigger” view to the metadata use the script API_REDGPR.ADD_TRG_OBJECT. Two parameters habe to be provided, the view name of the trigger and if the trigger view is active or not.

Example

EXECUTE SCRIPT API_REDGPR.ADD_TRG_OBJECT(
     'TRG_KUNDE_DSGVO'               -- p_trg_view
   , FALSE                           -- p_active
);

After having issued the command above an entry appears in the table GPR_TRG_OBJECTS.

TRG_SCHEMA	TRG_VIEW	ACTIVE
REDGPR_CUS	TRG_KUNDE_DSGVO	false

In the view REDGPR.GPR_CHECK_OBJECTS you find the “Trigger” view and its status and/or possible errors if the view is not built correctly.

1.3. Adding a PSA object

The second step is to add the PSA object, to be anonymized, and the relation with which “Trigger” view it is checked for GDPR compliant anonymization and which “GDPR” view is used. To add this information use the script API_REDGPR.ADD_PSA_OBJECT.

Example

EXECUTE SCRIPT API_REDGPR.ADD_PSA_OBJECT(
     'PSA_TEST'                      -- p_psa_schema
   , 'KUNDE'                         -- p_psa_table
   , 'TRG_KUNDE_DSGVO'               -- p_trg_view
   , 'KUNDE_DSGVO'                   -- p_gpr_view
   , FALSE                           -- p_active
);

After having issued the command above an entry for your “PSA/GDPR” defintion appears in the table GPR_PSA_OBJECTS.

PSA_SCHEMA	PSA_TABLE	TRG_SCHEMA	TRG_VIEW	GPR_SCHEMA	GPR_VIEW	ACTIVE
PSA_TEST	KUNDE	REDGPR_CUS	TRG_KUNDE_DSGVO	REDGPR_CUS	KUNDE_DSGVO	false

In the view REDGPR.GPR_CHECK_OBJECTS you find the “GDPR” view and its status and/or possible errors if the view is not built correctly.

1.4. Checking the GDPR

The execution of the GDPR process is controlled by a job, which is maintained in the ADM_JOBS table, and is executed only for active metadata definitions. As this example creates only inactive PSA/GDPR metadata, you can check the results just by querying the “Trigger” and/or “GDPR” view. This is also very helpful if you create this GDPR process in a productive environment, where you can create them as inactive, check your views, and if all if found correct, just ust the MODIFY APis to activate the “Trigger” and “PSA/GDPR” definition.