How do I set up role-based access and masking in Redshift?

In Amazon Redshift, the usual pattern is:

1. Create database roles
2. Grant privileges to those roles
3. Assign users to roles
4. Use views or UDFs for masking
5. Optionally use row-level security-like patterns with views, late-binding views, or query predicates

Below is a practical setup.

---

1) Create roles

Use roles instead of granting directly to users.

CREATE ROLE analyst;
CREATE ROLE pii_admin;
CREATE ROLE finance_reader;

---

2) Grant object privileges to roles

Schema access

GRANT USAGE ON SCHEMA sales TO ROLE analyst;
GRANT USAGE ON SCHEMA hr TO ROLE pii_admin;

Table access

GRANT SELECT ON TABLE sales.orders TO ROLE analyst;
GRANT SELECT, UPDATE ON TABLE hr.employees TO ROLE pii_admin;

Future tables

If you want new tables to inherit access, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT ON TABLES TO ROLE analyst;

---

3) Assign users to roles

GRANT ROLE analyst TO USER alice;
GRANT ROLE pii_admin TO USER bob;

If you’re using federated identities / IAM / external auth, the mapping may happen outside SQL, but the role concept is the same.

---

4) Implement masking

Redshift doesn’t provide the same built-in dynamic masking features as some other databases, so the common approaches are:

• Create masked views
• Use stored procedures / UDFs for transformations
• Control access to base tables and expose only views

Example: masked view

Suppose you have a sensitive table:

CREATE TABLE hr.employees (
    employee_id INT,
    full_name   VARCHAR(100),
    email       VARCHAR(100),
    ssn         VARCHAR(11),
    salary      DECIMAL(12,2)
);

Create a masked view for general users:

CREATE VIEW hr.v_employees_masked AS
SELECT
    employee_id,
    full_name,
    REGEXP_REPLACE(email, '(^.).*(@.*$)', '\\1***\\2') AS email,
    '***-**-****' AS ssn,
    NULL::DECIMAL(12,2) AS salary
FROM hr.employees;

Then:

• grant users access to the view
• do not grant direct access to the base table

REVOKE ALL ON TABLE hr.employees FROM ROLE analyst;
GRANT SELECT ON VIEW hr.v_employees_masked TO ROLE analyst;

---

5) Give privileged users unmasked access

Create a separate role for users who can see the raw data:

GRANT SELECT ON TABLE hr.employees TO ROLE pii_admin;
REVOKE SELECT ON VIEW hr.v_employees_masked FROM ROLE pii_admin;

You can also create a separate unmasked view if you want a consistent interface, but for privileged users, direct table access is simplest.

---

6) Control access by schema pattern

A good design is:

• raw schema: base tables, restricted
• secure schema: views, broader access

Example:

• hr_raw.employees → restricted to admins
• hr_secure.employees → masked view for analysts

REVOKE ALL ON SCHEMA hr_raw FROM PUBLIC;
GRANT USAGE ON SCHEMA hr_secure TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA hr_secure TO ROLE analyst;

---

7) Row filtering (optional)

Redshift doesn’t have a classic native row-level security model like PostgreSQL RLS in all contexts, so the usual workaround is a view that filters rows:

CREATE VIEW sales.v_orders_as_region AS
SELECT *
FROM sales.orders
WHERE region = current_user_region();

Since Redshift doesn’t provide a built-in current_user_region() function, you’d typically:

• hardcode role-based views, or
• use a mapping table joined to current_user, or
• generate per-role views

Example with a mapping table:

CREATE TABLE security.user_region_map (
    username VARCHAR(100),
    region   VARCHAR(50)
);

Then:

CREATE VIEW sales.v_orders_secure AS
SELECT o.*
FROM sales.orders o
JOIN security.user_region_map m
  ON m.username = current_user
 AND m.region = o.region;

---

8) Auditing and governance

Use:

• system tables / logs
• CloudTrail for API activity
• Redshift system views for query history
• AWS Lake Formation / IAM if accessing data via Spectrum or external tables

Also consider:

• separate roles for read, write, admin
• least privilege
• no PUBLIC grants on sensitive schemas

---

9) Recommended pattern

For sensitive data, the most reliable pattern is:

• Base tables in restricted schema
• Masked views in a secure schema
• Roles granted only to the objects they need
• No direct table access for general users

---

Example end-to-end setup

-- Roles
CREATE ROLE analyst;
CREATE ROLE pii_admin;

-- Schemas
REVOKE ALL ON SCHEMA hr FROM PUBLIC;
GRANT USAGE ON SCHEMA hr TO ROLE pii_admin;
GRANT USAGE ON SCHEMA hr_secure TO ROLE analyst;

-- Base table access
GRANT SELECT ON TABLE hr.employees TO ROLE pii_admin;

-- Masked view
CREATE OR REPLACE VIEW hr_secure.employees AS
SELECT
    employee_id,
    full_name,
    REGEXP_REPLACE(email, '(^.).*(@.*$)', '\\1***\\2') AS email,
    '***-**-****' AS ssn,
    NULL::DECIMAL(12,2) AS salary
FROM hr.employees;

GRANT SELECT ON VIEW hr_secure.employees TO ROLE analyst;

-- Users
GRANT ROLE analyst TO USER alice;
GRANT ROLE pii_admin TO USER bob;

---

If you want, I can also give you:

• a copy-paste script for a real Redshift environment,
• a pattern for masking by role in a single view, or
• an example using Redshift Spectrum / external tables.