AWS Certified Data Engineer Associate DEA-C01 Practice Question

Your company uses an Amazon Redshift RA3 cluster. Ten users in the analytics team must be able to run SELECT statements against every table that exists now or will be created later in the analytics schema. They must not receive INSERT, UPDATE, or DELETE privileges. The same users also need to load data with the COPY command from an Amazon S3 bucket by assuming the cluster's default IAM role. Which approach provides the required access while minimizing future administration?

Create a database role named r_analytics. GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE r_analytics; then run ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO ROLE r_analytics. GRANT ASSUMEROLE ON DEFAULT TO ROLE r_analytics FOR COPY. Grant ROLE r_analytics TO each of the 10 users. Verify the cluster's default IAM role is attached.
Register the cluster as a data source in AWS Lake Formation, create an LF-tag-based policy that grants SELECT on the analytics schema, and allow Lake Formation to propagate permissions to new tables.
Attach the AmazonRedshiftReadOnlyAccess AWS managed policy to each IAM user and let them specify IAM_ROLE 'default' in COPY commands.
Add every analytics user to a new database group named analytics_ro and run GRANT USAGE ON SCHEMA analytics, GRANT SELECT ON ALL TABLES IN SCHEMA analytics, and ALTER DEFAULT PRIVILEGES … GRANT SELECT ON TABLES TO GROUP analytics_ro. Rely on inherited permissions for COPY.

Report Issue

Answer Description

A dedicated database role keeps read-only object privileges separate from the permission to invoke COPY. GRANT SELECT ON ALL TABLES IN SCHEMA immediately covers existing objects, and ALTER DEFAULT PRIVILEGES makes the grant automatic for any tables added later. Granting the role to each user satisfies least privilege without per-table or per-user grants. The additional GRANT ASSUMEROLE ON DEFAULT … FOR COPY lets anyone who holds the role invoke COPY with the cluster's default IAM role, avoiding separate S3 credentials. The other options either rely on non-existent system groups, use IAM console policies that do not control database access, or try to use Lake Formation, which does not manage internal Redshift table privileges.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.