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

Login to AWS Management console, Navigate to Amazon Redshift, and click Query editor v2 in Explorer.

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
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 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 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;
Select * from customer

SET SESSION AUTHORIZATION cust_srvc_user;
SELECT * FROM customer;

SET SESSION AUTHORIZATION auditor_user;
SELECT * FROM customer;

SET SESSION AUTHORIZATION default;
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;
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;