Conditional dynamic data masking - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Conditional dynamic data masking

You can mask data at the cell level by creating masking policies with conditional expressions in the masking expression. For example, you can create a masking policy that applies different masks to a value, depending on another column's value in that row.

The following is an example of using conditional data masking to create and attach a masking policy that partially redacts credit card numbers involved in fraud, while completely hiding all other credit card numbers. You must be a superuser or have the sys:secadmin role to run this example.

--Create an analyst role. CREATE ROLE analyst; --Create a credit card table. The table contains an is_fraud boolean column, --which is TRUE if the credit card number in that row was involved in a fraudulent transaction. CREATE TABLE credit_cards (id INT, is_fraud BOOLEAN, credit_card_number VARCHAR(16)); --Create a function that partially redacts credit card numbers. CREATE FUNCTION REDACT_CREDIT_CARD (credit_card VARCHAR(16)) RETURNS VARCHAR(16) IMMUTABLE AS $$ import re regexp = re.compile("^([0-9]{6})[0-9]{5,6}([0-9]{4})") match = if match != None: first = last = else: first = "000000" last = "0000" return "{}XXXXX{}".format(first, last) $$ LANGUAGE plpythonu; --Create a masking policy that partially redacts credit card numbers if the is_fraud value for that row is TRUE, --and otherwise blanks out the credit card number completely. CREATE MASKING POLICY card_number_conditional_mask WITH (fraudulent BOOLEAN, pan varchar(16)) USING (CASE WHEN fraudulent THEN REDACT_CREDIT_CARD(pan) ELSE Null END); --Attach the masking policy to the credit_cards/analyst table/role pair. ATTACH MASKING POLICY card_number_conditional_mask ON credit_cards (credit_card_number) USING (is_fraud, credit_card_number) TO ROLE analyst PRIORITY 100;