Use DDM support in Amazon Redshift for E-mail & SSN Masking

Create the table and populate contents.
Create Users

CREATE TABLE customer (
customer_id INT,
e_mail TEXT,
SSN TEXT
);
-- 2- Populate the table with sample values with last entry with invalid e-mail
INSERT INTO customer
VALUES
(100,'customer1@abc.com', '111-11-1111'),
(101,'customer2@xyz.com', '222-22-2222'),
(102,'customer3@abcxyz.com', '333-33-3333'),
(103,'customer4@abc123.com', '444-44-4444'),
(104,'customer5@axyz.com', '555-55-5555'),
(105,'customer6@abc.com', '666-66-6666'),
(106,'customer7abc.com', '666-66-6666')
;

GRANT SELECT ON customer TO PUBLIC;

-- 3- create four users
CREATE USER cust_srvc_user WITH PASSWORD '1234Test!';
CREATE USER auditor_user   WITH PASSWORD '1234Test!';
CREATE USER regular_user   WITH PASSWORD '1234Test!';

Creating the solution

In order to satisfy the security requirements we will need to make sure that each use will see the same data in different ways based on their granted privileges, to do that we will use user roles combined with masking policies as follows

Create User Roles and grant different users to different Roles.
Create the UDF to be used with DDM

-- Create User Roles
CREATE ROLE cust_srvc_role;
CREATE ROLE auditor_role;
-- note that public role exist by default

-- 5- Grant Roles to Users
GRANT ROLE cust_srvc_role to cust_srvc_user;
GRANT ROLE auditor_role   to auditor_user;
-- note that regualr_user is attached to public role by default.

-- Create UDF for masking e-mail
CREATE OR REPLACE FUNCTION REDACT_EMAIL (email_address TEXT)
RETURNS TEXT IMMUTABLE
AS $$
    md=email_address.find('@') 
    mdot=email_address.rfind('.') 
    ln=len(email_address) 
    if md>0 and mdot> 0 and ln>0: 
            rtn_val = email_address[0]+'#####'+email_address[md-1:md+1]+'####' +email_address[mdot:ln]  
    else:
        rtn_val='invalid email'

    return rtn_val
$$ LANGUAGE plpythonu;


-- Create UDF for masking ssn

CREATE OR REPLACE FUNCTION REDACT_SSN (ssn TEXT)
RETURNS TEXT IMMUTABLE
AS $$
    import re
    vSSN = ''.join(re.findall(r'\d+',ssn )) 
    if len(vSSN)==9: 
        rtn_val = vSSN[0:3]+'-xx-xxxx'
    else:
        rtn_val='invalid ssn'

    return rtn_val
$$ LANGUAGE plpythonu;

Create e-mail Masking Policies & Attaching

CREATE MASKING POLICY mask_e_mail_full
WITH (e_mail VARCHAR(256))
USING ('XXXXXXXX@XXXXXXXX.XXX');

ATTACH MASKING POLICY mask_e_mail_full
ON customer(e_mail)
TO PUBLIC;

CREATE MASKING POLICY e_mail_partial_mask
WITH (e_mail VARCHAR(256))
USING (REDACT_EMAIL(e_mail));

ATTACH MASKING POLICY e_mail_partial_mask
ON customer(e_mail)
USING (e_mail)
TO ROLE cust_srvc_role
PRIORITY 10;

CREATE MASKING POLICY raw_e_mail
WITH (e_mail VARCHAR(256))
USING (e_mail);

ATTACH MASKING POLICY raw_e_mail
ON customer(e_mail)
TO ROLE auditor_role
PRIORITY 20;

Create SSN Masking Policies & Attaching

CREATE MASKING POLICY mask_ssn_full
WITH (SSN VARCHAR(256))
USING ('XXX-XX-XXXX');

ATTACH MASKING POLICY mask_ssn_full
ON customer(SSN)
TO PUBLIC;

CREATE MASKING POLICY ssn_partial_mask
WITH (SSN VARCHAR(256))
USING (REDACT_SSN(SSN));

ATTACH MASKING POLICY ssn_partial_mask
ON customer(SSN)
USING (SSN)
TO ROLE cust_srvc_role
PRIORITY 10;

CREATE MASKING POLICY raw_ssn
WITH (SSN VARCHAR(256))
USING (SSN);

ATTACH MASKING POLICY raw_ssn
ON customer(SSN)
TO ROLE auditor_role
PRIORITY 20;

Testing by using different users with different privileges

Select * from customer

SET SESSION AUTHORIZATION cust_srvc_user;
SELECT * FROM customer;

SET SESSION AUTHORIZATION auditor_user;
SELECT * FROM customer;

SET SESSION AUTHORIZATION default;

Clean-up DDM Objects

DETACH MASKING POLICY mask_e_mail_full
ON customer(e_mail)
FROM PUBLIC;

DROP MASKING POLICY mask_e_mail_full;

DETACH MASKING POLICY e_mail_partial_mask
ON customer(e_mail)
FROM ROLE cust_srvc_role;

DROP  MASKING POLICY e_mail_partial_mask;

DETACH MASKING POLICY raw_e_mail
ON customer(e_mail)
FROM ROLE auditor_role;

DROP MASKING POLICY raw_e_mail;

DETACH MASKING POLICY mask_ssn_full
ON customer(SSN)
FROM PUBLIC;

DROP MASKING POLICY mask_ssn_full;

DETACH MASKING POLICY ssn_partial_mask
ON customer(SSN)
FROM ROLE cust_srvc_role;

DROP MASKING POLICY ssn_partial_mask;
DETACH MASKING POLICY raw_ssn
ON customer(SSN)
FROM ROLE auditor_role;

DROP MASKING POLICY raw_ssn;

Clean-up Role, user, and Table Objects

REVOKE ROLE cust_srvc_role from cust_srvc_user;
REVOKE ROLE auditor_role   from auditor_user;

DROP FUNCTION REDACT_EMAIL (email_address TEXT);
DROP FUNCTION REDACT_SSN (ssn TEXT);

DROP ROLE cust_srvc_role;
DROP ROLE auditor_role;

DROP USER cust_srvc_user ;
DROP USER auditor_user;

DROP TABLE customer;