Redshift users, roles and data sharing: DEA-C01 study notes and practice questions
What to know about Redshift users, roles and data sharing for the DEA-C01 exam, from the Data Security and Governance domain (18% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
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.
A retail bank's risk team runs an Amazon Redshift RA3 cluster with transaction tables. The marketing team runs its own Redshift Serverless workgroup in a different AWS account and needs read-only access to two of those tables, always with the latest data.
The risk team does not want to give the marketing team admin access to its cluster, and it does not want nightly copies of the tables.
Which approach meets these requirements?
Choose one answer.
AA. Create an IAM user in the risk account for the marketing team, and give that user the Redshift admin role so that it can grant its own access.
BB. Unload the two tables to S3 every night, and let the marketing team load the files into its own workgroup with COPY.
CC. Create a datashare for the two tables, grant it to the marketing team's AWS account, and create a database from the share in its workgroup.
DD. Turn on public access for the risk cluster and give the marketing team its database password, so that it can connect over JDBC.
Show the answer and explanation
Answer: C
A. Wrong: An admin role gives the marketing team control over the risk cluster, which the risk team has ruled out. The option grants much more than read-only access to two tables.
B. Wrong: The nightly unload and load create copies that are a day old by the time marketing uses them. The option does not give the latest data and adds a process to maintain.
C. Right: Correct. Data sharing gives the consumer live, read-only access to the shared tables without copying them, and the producer decides which tables are in the share. After the marketing account accepts the share, it creates a database from it, and no admin role on the risk cluster is needed. Learn more: Data sharing in Amazon Redshift (opens in a new tab)
D. Wrong: Public access exposes the cluster to the internet and shares a password, which is unsafe for transaction data. The option fails the security requirements.
Explanation
Redshift data sharing lets a producer share selected tables live with a consumer namespace in another account, so the consumer reads current data without copies or admin access. The producer controls what is shared. Admin roles, nightly copies and public access each give too much access or too stale data.
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 →