Encode / Decode sensitive data fields

[Marco]

Any experience to add encode a sensitive data field before save to db and to decode for certain roles. Where should the encode be added to have it wokring in MariaDB ?

SELECT DECODE(ENCODE('abc','abc@123'),'abc@123')

[AppSynergy Support]

You could use a trigger to encode during insert and then use a view to decode during select.

However, what I suspect you want is "data masking" functionality for GDPR or other compliance purposes.

The best way to do this is with Security Roles and VIEWs.

First, create a Role for HR - say they can SELECT all cols on Customer and UPDATE their private info.

Second, create a VIEW called Customer_Masked something like this:

SELECT first_name, last_name, IF('HR' = 

  (

    SELECT parasql_roles.name 

    FROM parasql_users JOIN parasql_roles ON (parasql_roles.role_id = parasql_users.role_id) 

    WHERE user_id = SUBSTRING_INDEX(SESSION_USER(), '@', 1) 

  ), ssn, 'xxx-xx-xxxx') as ssn_masked

From Customers

[AppSynergy Support]

Or using the built in function the VIEW could be more simply written as:

SELECT first_name, last_name, IF(parasql_user_role() = 'HR' , ssn, 'xxx-xx-xxxx') AS ssn_masked

From Customers

On Wednesday, February 3, 2021 at 1:40:52 PM UTC-5 Marco wrote: