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:
Build a trigger view and a GDPR view.
Register the trigger view.
Register the PSA table with its trigger and GDPR view.
Check the results.
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 columnRED_BSKis 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. BesidesRED_PKthe view may only contain the anonymised columns, which must exist in the PSA table; no otherRED_*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_OBJECTSshows configuration errors of the views, e.g. a missingRED_BSKin the trigger view, a missingRED_PKin the GDPR view, otherRED_*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.