Question

A financial services company stores customer data in an Amazon Redshift cluster. Analysts need to run queries on a customers table that contains a social_security_number column, but compliance requires that only members of the fraud-investigation team see the full SSN value. All other analysts must see the value fully redacted, while a small auditing group should see only the last four digits. The company wants to enforce this at query time without duplicating the table or building separate views for each group. Which approach BEST meets these requirements with the least ongoing maintenance?

Accepted Answer

Create dynamic data masking policies on the social_security_number column and attach them to different database roles, granting each role the appropriate masking behavior.

Answer

Create three materialized views of the customers table with different levels of column redaction and grant each analyst group access to the appropriate view.

Answer

Enable Redshift column-level encryption on the SSN column using KMS, and share the decryption key only with the fraud-investigation team.

Answer

Export the table to Amazon S3, apply Lake Formation column-level permissions, and have all analysts query it through Redshift Spectrum external tables.