Data Security in Amazon Redshift: A Practical Guide to RBAC, RLS, and Dynamic Data Masking
What Redshift’s built-in security features actually do, why they matter, and how to put them to work — no third-party tooling required.
Your data warehouse is where sensitive data eventually pools. Customer PII, revenue, transactions, behavioral data — it all lands in the warehouse, and it’s queried by far more people than any production database ever is: analysts, data scientists, BI tools, external partners. That combination makes the warehouse simultaneously the most useful and the most exposed system you run.
The good news is that you don’t need to bolt security on from the outside. Amazon Redshift ships with a layered set of native controls that let you enforce “the right people see the right data” inside the database itself. This guide walks through the three that do most of the heavy lifting — RBAC, Row-Level Security, and Dynamic Data Masking — what each one is, why it matters, and how to implement it.
A mental model: defense in depth
It helps to picture data protection in Redshift as a series of gates a query passes through, each answering a different question:
- Encryption — is the data protected at rest and in transit? (the foundation)
- RBAC — who are you, and which objects are you allowed to touch?
- RLS — which rows are you allowed to see?
- Column grants + DDM — which columns, and how much of each value, are you allowed to see?
No single feature is “security” on its own. Stacked, they let one shared warehouse serve many audiences safely — without copying data into a separate mart for every role.
Layer 1: Role-Based Access Control (RBAC)
What it is. Instead of granting permissions to individual users, you grant them to roles that map to job functions, then assign users to roles.
Why it matters. Per-user grants don’t scale and drift out of sync the moment someone changes teams. Roles keep access tied to what a person does, and make audits answerable (“who can read the finance schema?” becomes one query).
How to implement it. The core commands:
CREATE ROLE analyst;-- grant object privileges to the role, not the user
GRANT USAGE ON SCHEMA analytics TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst;-- assign the role to a user
GRANT ROLE analyst TO "jane@company.com";
Redshift also provides system-defined roles for administration. The one to know for this article is sys:secadmin — it's what lets a non-superuser create and manage RLS and masking policies. To create or attach any of the policies below, you need either a superuser or the sys:secadmin role:
GRANT ROLE sys:secadmin TO "security-admin@company.com";Layer 2: Row-Level Security (RLS)
What it is. RLS attaches a policy to a table that automatically filters which rows a user can see, based on their role — no WHERE clause required, and no way for the user to remove it.
Why it matters. This is how you isolate tenants in a shared table, enforce regional data rules (“EU analysts see only EU rows”), or implement need-to-know access without maintaining a forest of per-team views.
How to implement it. Create a policy with a predicate, attach it to a table for a role, then switch RLS on:
-- a policy that only returns a given region's rows
CREATE RLS POLICY region_eu
WITH (region VARCHAR(20))
USING (region = 'EU');ATTACH RLS POLICY region_eu ON analytics.customers TO ROLE eu_analyst;-- enable RLS on the table
ALTER TABLE analytics.customers ROW LEVEL SECURITY ON;
Two things worth knowing. When several policies apply to one table, Redshift combines them with a conjunction type — AND by default (all must pass), switchable to OR on the table. And an important caveat: RLS does not apply to superusers, who see every row — a good reason to keep superuser accounts rare.
Layer 3: Dynamic Data Masking (DDM)
What it is. DDM rewrites a column’s value at query time based on the querying user’s role. The stored bytes never change — Redshift simply applies a transformation as the query runs. Masking can be full (hash or redact), partial (reveal part of a value), or conditional.
Get Wahabbhatti’s stories in your inbox
Join Medium for free to get updates from this writer.
Why it matters. It lets analysts work with a table that contains PII columns without ever seeing the raw PII. A support team, a marketing team, and a fraud team can all query the same email or phone column and each see exactly the fidelity they're cleared for.
How to implement it. Create a masking policy (the USING expression defines the transformation), then attach it to a column for a role. The mechanism that makes this scale to many roles is priority:
-- full masking: an irreversible hash
CREATE MASKING POLICY mask_hash
WITH (val VARCHAR(MAX))
USING (sha2(val, 256));-- no masking: the original value, for a privileged role
CREATE MASKING POLICY mask_none
WITH (val VARCHAR(MAX))
USING (val);-- attach both to the same column, for different roles, at different priorities
ATTACH MASKING POLICY mask_hash ON analytics.customers(email) TO ROLE analyst PRIORITY 10;
ATTACH MASKING POLICY mask_none ON analytics.customers(email) TO ROLE privileged_role PRIORITY 20;
When a user queries, Redshift applies the highest-priority policy their roles grant. So the same column serves everyone: an analyst sees a hash, a privileged_role user sees the real value. One rule to remember — you can't attach two policies to the same column at equal priority, so plan a priority ladder before you start.
A useful pattern for hashing: if you hash deterministically (the same input always yields the same output), analysts can still JOIN and COUNT(DISTINCT ...) on the masked column while the raw value stays hidden. Just be aware of the tradeoff — deterministic hashes are more vulnerable to dictionary attacks on low-cardinality columns, which is why a secret salt is common — and if you need consistent tokens across differently-formatted inputs (phone numbers, say), standardize the value before hashing.
How the layers work together
The elegant part is that all three resolve on a single shared copy of the data, at query time:
- You connect with a role (RBAC decides what you can touch at all).
- RLS trims the result to the rows your role is allowed to see.
- DDM reshapes the sensitive columns in those rows to the fidelity your role is cleared for.
No duplicated tables, no per-role marts, no extra ETL. You secure the table once, and Redshift returns a role-appropriate answer to everyone who queries it.
Implementing it well: practical tips
- Start from roles, not users. Model access around job functions first; assigning users becomes trivial afterward.
- Guard superuser access. Superusers bypass RLS and see unmasked data. Treat those accounts as break-glass, not daily drivers.
- Design your masking priority ladder up front. Equal priorities on the same column are rejected, so decide the tiers before writing policies.
- Standardize before you hash if you need masked columns to stay joinable.
- Treat policies as code. Keep your
CREATE/ATTACHstatements in version control so security changes are reviewed like any other change. - Audit with the system views. Redshift exposes everything you attached:
SVV_MASKING_POLICYandSVV_ATTACHED_MASKING_POLICYfor masking,SVV_RLS_POLICYfor RLS policies, andSVV_RLS_APPLIED_POLICYto trace which policies actually fired on a query. - Test by assuming a role. Verify what a role can’t see, not just what it can — that’s the check that catches leaks.
Why it’s worth doing
Regulations like GDPR and local data-protection laws increasingly demand that PII be protected by design, not by policy documents. Multi-tenant analytics platforms need hard isolation between customers. And every data team wants to give analysts genuine self-service without a leak waiting to happen.
Native Redshift controls give you all three: one copy of the data, protected by default, enforced by the engine, and fully auditable — instead of a fragile maze of role-specific views and copies that someone has to remember to maintain.
How are you protecting sensitive data in your warehouse today — query-time masking like this, separate marts per role, or tokenization upstream before the data ever lands? I’d like to hear what’s worked for you.
Further reading
- Amazon Redshift — Role-based access control (RBAC): https://docs.aws.amazon.com/redshift/latest/dg/t_Roles.html
- Amazon Redshift — Row-level security: https://docs.aws.amazon.com/redshift/latest/dg/t_rls.html
- Amazon Redshift — Dynamic data masking: https://docs.aws.amazon.com/redshift/latest/dg/t_ddm.html
- Amazon Redshift — Dynamic data masking policy hierarchy: https://docs.aws.amazon.com/redshift/latest/dg/t_ddm-hierarchy.html
