RBAC, Row-Level Security and Dynamic Data Masking in Amazon Redshift

Most Redshift security work still happens the way it did in 2015: a handful of groups, GRANT SELECT ON ALL TABLES IN SCHEMA, and a separate "safe" schema of pre-redacted views that someone has to keep in sync. It works until an auditor asks who can see the raw email column, or until a fourth business unit shows up and the view sprawl becomes its own maintenance project.

Amazon Redshift now has three native features that replace almost all of that: role-based access control (RBAC), row-level security (RLS) and dynamic data masking (DDM). Together they let one physical table serve analysts, engineers and auditors, with each seeing a different slice and a different level of detail — no duplicate views, no ETL branches.

This tutorial builds a working example end to end on a customer/orders schema, then covers the operational details that bite: ordering of policies, interaction with datashares and materialized views, and how to prove the setup to an auditor.

The example schema

CREATE SCHEMA IF NOT EXISTS sales;

CREATE TABLE sales.customers (
  customer_id   BIGINT,
  region_code   VARCHAR(8),
  full_name     VARCHAR(200),
  email         VARCHAR(200),
  phone         VARCHAR(40),
  ssn_last4     CHAR(4),
  created_at    TIMESTAMP
);

CREATE TABLE sales.orders (
  order_id      BIGINT,
  customer_id   BIGINT,
  region_code   VARCHAR(8),
  order_total   DECIMAL(12,2),
  order_ts      TIMESTAMP
);

Three audiences: EMEA and AMER analysts who should each see only their region and never see raw PII; a data engineering team that needs full access for pipeline work; and a support team that needs partially masked contact details to verify callers.

Step 1: roles instead of groups

Groups are still supported, but roles are what you want now: they nest, they can carry system permissions, and they can be granted to other roles.

CREATE ROLE analyst;
CREATE ROLE analyst_emea;
CREATE ROLE analyst_amer;
CREATE ROLE support_agent;
CREATE ROLE data_engineer;

-- Nesting: region roles inherit everything the base analyst role has
GRANT ROLE analyst TO ROLE analyst_emea;
GRANT ROLE analyst TO ROLE analyst_amer;

-- Object permissions go on the base role once
GRANT USAGE ON SCHEMA sales TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO ROLE analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO ROLE analyst;

GRANT ALL ON SCHEMA sales TO ROLE data_engineer;
GRANT ALL ON ALL TABLES IN SCHEMA sales TO ROLE data_engineer;

GRANT USAGE ON SCHEMA sales TO ROLE support_agent;
GRANT SELECT ON sales.customers TO ROLE support_agent;

-- Attach users
GRANT ROLE analyst_emea TO "amina";
GRANT ROLE analyst_amer TO "dave";
GRANT ROLE support_agent TO "callcenter_svc";
GRANT ROLE data_engineer TO "etl_svc";

Two things worth knowing. First, Redshift ships system-defined roles — sys:operator, sys:dba, sys:superuser, sys:secadmin — and you should grant sys:secadmin to whoever will own RLS and masking policies rather than handing out superuser. Second, if you federate identities through IAM Identity Center or a SAML IdP, you can map IdP groups to Redshift roles so membership is managed upstream and GRANT ROLE is only used for service accounts.

Audit membership at any time:

SELECT role_name, user_name FROM SVV_USER_GRANTS ORDER BY 1, 2;
SELECT role_name, granted_role_name FROM SVV_ROLE_GRANTS;

Step 2: row-level security

RLS attaches a predicate to a table for a given role. The predicate is applied on every query, including through views, so there is nothing for a user to work around.

-- Only a user with the sys:secadmin role (or a superuser) can do this
CREATE RLS POLICY emea_rows
  WITH (region_code VARCHAR(8))
  USING (region_code = 'EMEA');

CREATE RLS POLICY amer_rows
  WITH (region_code VARCHAR(8))
  USING (region_code = 'AMER');

ATTACH RLS POLICY emea_rows ON sales.customers TO ROLE analyst_emea;
ATTACH RLS POLICY emea_rows ON sales.orders    TO ROLE analyst_emea;
ATTACH RLS POLICY amer_rows ON sales.customers TO ROLE analyst_amer;
ATTACH RLS POLICY amer_rows ON sales.orders    TO ROLE analyst_amer;

-- Turn enforcement on for the table
ALTER TABLE sales.customers ROW LEVEL SECURITY ON;
ALTER TABLE sales.orders    ROW LEVEL SECURITY ON;

Hard-coding one policy per region does not scale past a handful. The maintainable pattern is a lookup table plus a session-context predicate:

CREATE TABLE sales.region_access (
  user_name    VARCHAR(128),
  region_code  VARCHAR(8)
);

CREATE RLS POLICY region_by_user
  WITH (region_code VARCHAR(8))
  USING (
    region_code IN (
      SELECT region_code FROM sales.region_access
      WHERE user_name = CURRENT_USER
    )
  );

ATTACH RLS POLICY region_by_user ON sales.customers TO ROLE analyst;

Now onboarding an analyst is one INSERT, not a DDL change. Keep the lookup table small and grant SELECT on it to the analyst role — the subquery runs as the querying user.

Rules to keep in mind:

• Multiple attached policies are ANDed. If a user picks up two policies through two roles, they see the intersection, not the union. Model unions with a single IN predicate rather than several policies.
• PUBLIC attachment is the deny-by-default lever. Attaching a restrictive policy TO PUBLIC and then a permissive one to specific roles is the safest posture for a table containing regulated data.
• Some users bypass RLS: superusers, users with the sys:secadmin role, and any user granted IGNORE RLS. Keep that list short and reviewed.
• RLS-protected tables cannot be read by a materialized view or an unprotected copy, which is deliberate — otherwise the MV would be an escape hatch.

Verify enforcement:

SELECT * FROM SVV_RLS_POLICY;          -- policy definitions
SELECT * FROM SVV_RLS_ATTACHED_POLICY; -- what is attached to what
SELECT * FROM SVV_RLS_RELATION;        -- tables with RLS enabled

Step 3: dynamic data masking

RLS controls which rows; DDM controls what the values look like. A masking policy is a SQL expression over the column, attached per role, with a priority that resolves overlaps.

-- Full redaction for the general analyst population
CREATE MASKING POLICY mask_email_full
  WITH (email VARCHAR(200))
  USING ('***REDACTED***');

-- Partial mask for support: first character plus domain
CREATE MASKING POLICY mask_email_partial
  WITH (email VARCHAR(200))
  USING (
    LEFT(email, 1) || '****@' || SPLIT_PART(email, '@', 2)
  );

ATTACH MASKING POLICY mask_email_full
  ON sales.customers(email)
  TO PUBLIC
  PRIORITY 10;

ATTACH MASKING POLICY mask_email_partial
  ON sales.customers(email)
  TO ROLE support_agent
  PRIORITY 20;

Highest priority wins for a given user, so the PUBLIC policy is the floor and role-specific policies relax it. Engineers who need raw values get no policy attached at all — or, better, an explicit unmasking policy so intent is visible in the catalog:

CREATE MASKING POLICY mask_email_none
  WITH (email VARCHAR(200))
  USING (email);

ATTACH MASKING POLICY mask_email_none
  ON sales.customers(email)
  TO ROLE data_engineer
  PRIORITY 30;

Two more patterns that come up on every engagement:

Hash rather than redact when analysts still need to count distinct people or join on identity:

CREATE MASKING POLICY mask_email_hash
  WITH (email VARCHAR(200))
  USING (SHA2(LOWER(TRIM(email)) || 'per-tenant-salt', 256));

The salt should not live in the policy text in a real deployment; keep it in a restricted lookup table the policy reads, so rotating it is a data change.

Conditional masking on another column, which the WITH clause supports by declaring the extra inputs:

CREATE MASKING POLICY mask_ssn_by_consent
  WITH (ssn_last4 CHAR(4), region_code VARCHAR(8))
  USING (CASE WHEN region_code = 'EMEA' THEN 'XXXX' ELSE ssn_last4 END);

ATTACH MASKING POLICY mask_ssn_by_consent
  ON sales.customers(ssn_last4)
  USING (ssn_last4, region_code)
  TO ROLE analyst
  PRIORITY 10;

Inspect the result:

SELECT * FROM SVV_MASKING_POLICY;
SELECT * FROM SVV_ATTACHED_MASKING_POLICY;

Step 4: test it like an auditor would

Do not eyeball this. Impersonate each role and assert:

SET SESSION AUTHORIZATION 'amina';
SELECT region_code, COUNT(*) FROM sales.customers GROUP BY 1;  -- expect EMEA only
SELECT email FROM sales.customers LIMIT 1;                     -- expect ***REDACTED***
RESET SESSION AUTHORIZATION;

SET SESSION AUTHORIZATION 'callcenter_svc';
SELECT email FROM sales.customers LIMIT 1;                     -- expect a****@example.com
RESET SESSION AUTHORIZATION;

Then park those assertions in a test job — dbt tests, a scheduled query, or a step in CI against a staging namespace — so a future GRANT cannot silently widen access. The most common regression we find in reviews is not a broken policy; it is a new table created in the schema after the policies were written, inheriting default privileges and no RLS at all. Guard against it:

SELECT t.schemaname, t.tablename
FROM SVV_TABLE_INFO t
LEFT JOIN SVV_RLS_RELATION r
  ON r.relschema = t.schema AND r.relname = t.table
WHERE t.schema = 'sales' AND r.relname IS NULL;

Operational gotchas

Performance. RLS predicates are pushed into the plan, so a lookup-table policy adds a small join to every query on the protected table. Keep the lookup table tiny, DISTSTYLE ALL, and analyzed. Masking expressions are evaluated per output row: a SHA2 over a hundred million rows in a dashboard query is not free — prefer masking on the narrow serving tables, not the raw fact.

Datashares. Masking and RLS policies live in the producer database and are not inherited by consumers automatically the way people assume. If you share a table that carries policies, validate what a consumer namespace actually sees before assuming it is protected; in most designs you share a curated, already-safe view instead.

Materialized views and CTAS. Redshift blocks creating a materialized view over an RLS-protected table for the non-exempt user, but a privileged pipeline account with IGNORE RLS can absolutely create an unprotected copy. Access control on the raw layer means nothing if the pipeline role writes an unpoliced derived table. Apply policies to derived tables too, or keep them out of user-visible schemas.

Superusers. They see everything and always will. If your compliance regime requires that nobody can read raw PII ad hoc, the answer is column-level encryption or tokenization upstream of Redshift, not DDM. Be honest about that boundary in your controls documentation.

Change management. Policies are DDL. Keep CREATE ... POLICY and ATTACH statements in version control alongside table DDL and apply them through the same migration tool. A policy that exists only because someone ran it in Query Editor is a policy that will vanish at the next environment rebuild.

Where this fits

For a regulated workload — HIPAA, PCI, GDPR residency — the practical target architecture is: identities federated from the IdP, roles mapped from IdP groups, one RLS policy driven by a small entitlements table, a PUBLIC masking floor on every PII column with role-specific relaxations, and a nightly test job that asserts what each role can see. That replaces the schema-per-audience sprawl most teams inherit, and it gives auditors something they can read in three catalog views.

Getting help

If you are retrofitting RBAC, row-level security or masking onto a live Redshift warehouse — or you have an audit date and a schema full of GRANT ALL — our senior Redshift consultants do this work regularly, both on direct engagements and as a subcontracting partner to agencies. Get in touch with a description of your schema and your compliance regime and we will tell you what a realistic path looks like.