DEA-C01 practice question: Redshift users, roles and data sharing (1 of 2)
Data Security and Governance A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
A pet insurer keeps customer records in an Amazon Redshift table, including full Social Security numbers and dates of birth. Most analysts need to join and count customers, but they must never see a full Social Security number. A small group of claims staff needs the full value.
The team does not want to create and maintain a separate copy of the table for each group.
Which approach meets these requirements?
Choose one answer.
AA. Create a view that masks the Social Security column, grant the analysts the view, and leave the base table readable to everyone.
BB. Create a dynamic data masking policy on the Social Security column, attached to the analyst role and not to the claims role.
CC. Encrypt the Social Security column with a KMS key in the application before loading, and give the analysts the decryption key only through the claims team.
DD. Move the Social Security numbers into a separate table, and grant the claims team access to that table only, while the analysts keep the main table.
Show the answer and explanation
Answer: B
A. Wrong: The view masks the value, but the analysts could still query the base table, which stays readable to them. A view on its own does not enforce the masking.
B. Right: Correct. Dynamic data masking applies the policy at query time to each user or role, so the same table returns a masked value to analysts and the full value to claims staff. No copies are needed. Learn more: Dynamic data masking (opens in a new tab)
C. Wrong: Encrypted values are hard for analysts to join and count, and the decryption process would sit outside Redshift. The option does not use the database's own masking controls.
D. Wrong: Splitting the table makes joins harder, and the main table would still hold the data the analysts must not see if it were ever copied. The option does not reliably protect the value.
Explanation
Redshift dynamic data masking applies a masking policy to a column for the users or roles it is attached to, at query time. Analysts see a masked value and claims staff see the full one, from the same table. Views without base-table restrictions, application encryption and table splits each leave a path to the full value or make analysis harder.
Want more on Redshift users, roles and data sharing? The 5 full-length practice sets have 9 more Redshift users, roles and data sharing questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →