Access control in Redshift and Athena

Lesson 3 of 4 in Authentication and Authorization over Data.

Amazon Redshift has its own fine-grained controls, authored in SQL, and they are not Lake Formation. Both exist for good reasons and the exam expects you to pick correctly: Lake Formation governs catalog tables read by engines over Amazon S3, while Redshift's controls govern tables in the warehouse's own storage. A Redshift Spectrum query over an external table is governed by Lake Formation; a query over a local table is governed by Redshift.

Everything in the warehouse attaches to a database role, so start there. A role holds privileges on schemas, tables, columns and functions, can nest inside another role, and ships with system roles for the usual duties — sys:secadmin for security administration, sys:dba, sys:operator, sys:superuser. Adding an analyst becomes a role membership, and that membership can be driven from the corporate directory through Identity Center. Three controls then attach to roles.

Column-level access control is the simplest and the most forgotten. GRANT SELECT (order_id, amount) ON orders TO ROLE analyst gives that role two columns; a SELECT * by that role returns a permission error, not a filtered row. That is the behavioural difference from masking, and it decides which to use: a removed column breaks a query written against the full schema, and guarantees nothing of the value is inferable.

Row-level security is authored as a policy and attached per role. Three behaviours decide questions:

• Policies are many-to-many — several on one table, one across several tables.
• All applicable policies must evaluate true for a row to be returned, so multiple policies only ever narrow. Adding a second policy cannot widen what a role sees.
• No applicable policy means no rows. A role with no policy on an RLS-protected table gets zero rows, not every row. Fail-closed, and the usual cause of "the report went empty the day we enabled RLS".

Dynamic data masking keeps the column present and rewrites its values at query time. CREATE MASKING POLICY defines an expression over the value — full redaction, a partial reveal, a hash, a call into a tokenisation function — and ATTACH MASKING POLICY … ON orders(card) TO ROLE analyst PRIORITY 20 binds it. The mechanics that get tested: several policies can attach to one column, and when an identity matches more than one the highest priority wins per column; the default priority is 0; you cannot attach two policies to the same column at equal priority, nor two to the same role-and-column pair; and TO PUBLIC sets the default everyone gets unless a higher-priority attachment covers them — which is how you make redaction the fallback and visibility the exception, rather than the other way around.

Sharing warehouse data is where teams reach for an UNLOAD and a copy, and usually should not. A datashare exposes named schemas, tables, views and functions to a consumer that queries them in place — no copy, no second storage bill, and the consumer always reads current data. Within an account the producer adds consumer namespaces directly. Across accounts there is a two-way handshake: the producer administrator authorizes the datashare, then the consumer administrator associates it with the whole account or with named clusters and workgroups, or declines.

Two requirements catch people. For cross-account sharing both the producer and the consumer cluster must be encrypted. And objects in a datashare are read-only unless the producer explicitly grants write privileges such as INSERT — with cross-account writes needing a separate authorization on the producer side and a specific association on the consumer side. The pattern worth knowing for a question phrased "share the data without granting access to the production cluster": the producer shares from a separate namespace fed by a copy, so the consumer never touches production.

On the query side, Athena workgroups are where the administrator's controls live, and two settings decide whether any of them bind.

EnforceWorkGroupConfiguration makes the workgroup's settings override whatever the client sends. Leave it false and a user can write query results wherever their own IAM policy allows, which quietly relocates sensitive output to a bucket with different controls — the classic Athena data-exposure path, because results are a copy of the data with none of its governance.

EnableMinimumEncryptionConfiguration sets a floor: users may choose the configured level or stronger — SSE-S3, then SSE-KMS, then CSE-KMS. It is ignored entirely when EnforceWorkGroupConfiguration is true, because the workgroup's own encryption setting then applies outright. Worth getting straight, because it reads like a conflict and is actually a precedence rule.

The workgroup also caps BytesScannedCutoffPerQuery, which is a cost control with a security flavour: a bounded scan limits how much data one careless statement can extract. And none of it binds unless IAM stops the user simply choosing a different workgroup — scope athena:StartQueryExecution to the workgroup ARN.

Key terms: Redshift database role (RBAC), Redshift column-level access control, Redshift row-level security (RLS), Redshift dynamic data masking (DDM), Amazon Redshift datashare, Athena workgroup

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.