1. Anonymise Data for GDPR

The GDPR module anonymises personal data in the historised PSA tables, e.g. after the relationship with a customer or employee has ended. The anonymised values overwrite the original values in place; the original data can only be restored from a database backup.

The setup consists of these steps:

  1. Build a trigger view and a GDPR view.

  2. Register the trigger view.

  3. Register the PSA table with its trigger and GDPR view.

  4. Check the results.

  5. Activate the definitions and the jobs.

The examples anonymise the employees of the table PSA_TEST.EMPLOYEES whose employment ended more than 7 years ago.

1.1. Build the Views

First the Application Manager builds two views in the schema REDGPR_CUS (the default schema of the GDPR module):

Trigger view

Delivers the business keys (RED_BSK) of all subjects to be anonymised. The column RED_BSK is mandatory. One trigger view can be used for several PSA tables.

GDPR view

Delivers, for one PSA table, the rows to be anonymised (identified by RED_PK) together with the anonymised values of the affected columns. Besides RED_PK the view may only contain the anonymised columns, which must exist in the PSA table; no other RED_* columns are allowed.

Important

The GDPR view itself must restrict its rows to the business keys of the trigger view, e.g. by a join. The trigger view only decides whether an anonymisation run is executed for the PSA table at all (i.e. whether it returns any rows).

As the views are normal views, their results can be checked before any data is changed.

Example: Trigger View

 1CREATE OR REPLACE VIEW REDGPR_CUS.TRG_EMPLOYEES_GDPR
 2AS
 3WITH last_gpr AS
 4    (   -- time of the last successful GDPR run for this PSA table and trigger
 5        SELECT NVL(MAX(GPR_FINISH), TO_TIMESTAMP('1800-01-01')) AS LAST_GPR_RUN
 6        FROM REDGPR.GPR_CONTROL
 7        WHERE PSA_SCHEMA = 'PSA_TEST'
 8          AND PSA_TABLE = 'EMPLOYEES'
 9          AND TRG_SCHEMA = 'REDGPR_CUS'
10          AND TRG_VIEW = 'TRG_EMPLOYEES_GDPR'
11          AND GPR_ERROR_CODE IS NULL
12    )
13SELECT DISTINCT
14    e.RED_BSK,
15    l.LAST_GPR_RUN
16FROM PSA_TEST.EMPLOYEES e
17JOIN last_gpr l
18  ON e.RED_TDATE_FROM >= l.LAST_GPR_RUN      -- only data loaded since the last run
19WHERE e.END_DATE <= ADD_YEARS(SYSDATE, -7)   -- employment ended more than 7 years ago
20  AND e.RED_GPR_RUN_ID IS NULL;              -- not anonymised yet

Example: GDPR View

 1CREATE OR REPLACE VIEW REDGPR_CUS.GPR_EMPLOYEES_GDPR
 2AS 
 3SELECT
 4    e.RED_PK,
 5    CAST(NULL AS DECIMAL(8,2))           AS SALARY,
 6    CAST(NULL AS DECIMAL(4,2))           AS COMMISSION,
 7    '*****'                              AS FIRST_NAME,
 8    '*****'                              AS LAST_NAME,
 9    '*****'                              AS EMAIL,
10    '*****'                              AS PHONE,
11    DATE_TRUNC('year', e.HIRE_DATE)      AS HIRE_DATE   -- first day of the year
12FROM REDGPR_CUS.TRG_EMPLOYEES_GDPR t
13JOIN PSA_TEST.EMPLOYEES e
14  ON e.RED_BSK = t.RED_BSK                           -- restrict to the triggered keys
15 AND e.RED_TDATE_FROM >= t.LAST_GPR_RUN;

1.2. Register the Trigger View

Register the trigger view with API_REDGPR.ADD_TRG_OBJECT. The view is expected in the schema REDGPR_CUS. Register it as inactive first.

Example: Register the Trigger View

1EXECUTE SCRIPT API_REDGPR.ADD_TRG_OBJECT(
2    'TRG_EMPLOYEES_GDPR'     -- p_trg_view
3   ,false                    -- p_active
4);

The trigger view is stored in REDGPR.GPR_TRG_OBJECTS:

TRG_SCHEMA

TRG_VIEW

ACTIVE

REDGPR_CUS

TRG_EMPLOYEES_GDPR

false

1.3. Register the PSA Table

Register the PSA table with its trigger view and GDPR view with API_REDGPR.ADD_PSA_OBJECT, again as inactive.

Example: Register the PSA Table

1EXECUTE SCRIPT API_REDGPR.ADD_PSA_OBJECT(
2    'PSA_TEST'               -- p_psa_schema
3   ,'EMPLOYEES'              -- p_psa_table
4   ,'TRG_EMPLOYEES_GDPR'     -- p_trg_view
5   ,'GPR_EMPLOYEES_GDPR'     -- p_gpr_view
6   ,false                    -- p_active
7);

The definition is stored in REDGPR.GPR_PSA_OBJECTS:

PSA_SCHEMA

PSA_TABLE

TRG_SCHEMA

TRG_VIEW

GPR_SCHEMA

GPR_VIEW

ACTIVE

PSA_TEST

EMPLOYEES

REDGPR_CUS

TRG_EMPLOYEES_GDPR

REDGPR_CUS

GPR_EMPLOYEES_GDPR

false

1.4. Check the Results

  • REDGPR.GPR_CHECK_OBJECTS shows configuration errors of the views, e.g. a missing RED_BSK in the trigger view, a missing RED_PK in the GDPR view, other RED_* columns, or columns that do not exist in the PSA table.

  • Query the trigger view and the GDPR view to check which rows would be anonymised, and with which values.

1.5. Activate the Anonymisation

When the results are correct, activate the trigger view and the PSA table definition with API_REDGPR.MODIFY_TRG_OBJECT and API_REDGPR.MODIFY_PSA_OBJECT:

Example: Activate the Definitions

 1EXECUTE SCRIPT API_REDGPR.MODIFY_TRG_OBJECT(
 2    'TRG_EMPLOYEES_GDPR'     -- p_trg_view
 3   ,true                     -- p_active
 4);
 5
 6EXECUTE SCRIPT API_REDGPR.MODIFY_PSA_OBJECT(
 7    'PSA_TEST'               -- p_psa_schema
 8   ,'EMPLOYEES'              -- p_psa_table
 9   ,'TRG_EMPLOYEES_GDPR'     -- p_trg_view
10   ,'GPR_EMPLOYEES_GDPR'     -- p_gpr_view
11   ,true                     -- p_active
12);

The anonymisation is executed by the job gpr_run (weekly, Sunday 02:00), and interrupted runs are closed by the job gpr_repair (Sunday 01:00). Both jobs are inactive after the installation. Activate them once on the RED server (see the RED-PSS Guide):

Example: Activate the GDPR Jobs

redctl toggle_db_job -s REDGPR -j gpr_run -a
redctl toggle_db_job -s REDGPR -j gpr_repair -a

Each run is recorded in REDGPR.GPR_CONTROL with the number of updated rows; its status is shown in REDGPR.GPR_OBJECT_STATUS. Anonymised rows carry the ID of the run in the column RED_GPR_RUN_ID.

Definitions are removed with API_REDGPR.REMOVE_PSA_OBJECT and API_REDGPR.REMOVE_TRG_OBJECT.