RBAC vs ABAC for Data Platforms: Roles, Attributes & Policy Engines (OPA, Immuta, Privacera)

RBAC vs ABAC is the pick-one authorization decision that decides whether your warehouse can honestly answer "who can see this row, and why" — and it is the single governance choice senior data engineers under-think, because "just add a role" works right up until the day you have four hundred roles and no idea what any of them still grant. Every column of personally identifiable data, every regional data-residency rule, every "analysts can see their own team's orders but not payroll" requirement has to be encoded somewhere: in role-based access control roles baked into the warehouse, in attribute-based access control rules evaluated at request time, or in a dedicated policy engine sitting between the query and the data. The engineering trade-off is not "should we control access" — every platform with more than one consumer and one byte of sensitive data needs it — but which model expresses your rules without collapsing under its own weight, and what enforces it.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through RBAC vs ABAC and when each breaks," or "your role count exploded to 900 — what do you do?", or "how does a policy engine like Open Policy Agent actually enforce a decision against Snowflake?" It walks through role-based access control (users → roles → privileges with hierarchy and inheritance), attribute-based access control (subject, resource, action, and environment attributes combined by a rule), the policy engines that separate the decision from the enforcement — Open Policy Agent (OPA) with Rego and the PDP/PEP model — and the managed data access governance platforms Immuta and Privacera that push tag-driven dynamic masking and row filtering natively into the warehouse. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. Throughout, the recurring themes are fine-grained access control, policy as code, and getting authorization right without a role for every combination of conditions.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse system trade-offs on the design practice library →, and sharpen the pipeline-governance angle with the ETL practice library →.

---

On this page

• Why the RBAC vs ABAC choice decides data access governance
• RBAC — role-based access control done right
• ABAC — attribute-based access control and policy as code
• Policy engines — OPA and the PDP/PEP model
• Immuta and Privacera — managed governance and hybrid RBAC/ABAC
• Cheat sheet — RBAC vs ABAC and policy engine recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the RBAC vs ABAC choice decides data access governance

Two authorization models, one decision that binds your platform for years

The one-sentence invariant: authorization on a data platform is a picking exercise between encoding access as a fixed set of roles a user is assigned (role-based access control), evaluating a rule over the attributes of the subject, resource, action, and environment at request time (attribute-based access control), or delegating the decision to a dedicated policy engine that both models can call — and each choice trades granularity against scale, context-awareness, and the ongoing cost of managing the policy in a way that cannot be undone cheaply. The model you pick in month one becomes the model you fight to migrate away from in year three, because every downstream grant, every audit report, and every "can user X see row Y" question hard-codes assumptions about where the decision lives — in a GRANT statement baked into the warehouse, in a Rego rule shipped as a bundle, or in a governance platform's tag policy.

The four axes interviewers actually probe.

• Granularity. RBAC grants at the object level — table, schema, sometimes column. Getting to row-level ("analysts see only their region's rows") or cell-level ("mask the SSN column unless purpose = fraud-investigation") with pure RBAC means one role per combination, which does not scale. ABAC and policy engines express granularity as a rule, so a single policy covers thousands of row/column combinations. Interviewers open here because it separates people who've only run GRANT SELECT from people who've enforced GDPR-style data residency.
• Scale (role explosion). RBAC's role count grows multiplicatively: roles × regions × sensitivity levels × teams × purposes. A modest enterprise reaches thousands of roles, most of them near-duplicates nobody dares delete. ABAC's rule count grows additively — one rule for "region match," one for "PII masking" — because attributes compose. The role-explosion story is the single most-probed RBAC weakness.
• Context-awareness. RBAC decisions are static: a user either has the role or doesn't, regardless of time, location, request purpose, or data classification. ABAC decisions are dynamic — "allow if request.time is within business hours AND subject.clearance >= resource.sensitivity AND subject.purpose == resource.allowed_purpose." Any requirement that mentions "when," "from where," or "for what purpose" is an ABAC signal.
• Auditability and policy-management cost. RBAC is trivially auditable at a point in time (list the roles, list the grants) but the why is lost — nobody remembers why role_fin_eu_ro_2019 exists. ABAC as policy as code is version-controlled, diffable, testable, and self-documenting, but the decision is computed, so audit requires decision logs rather than a static grant dump. The cost trade is "cheap to snapshot, expensive to understand" (RBAC) versus "cheap to understand, requires decision logging to audit" (ABAC).

The 2026 reality — RBAC is the floor, ABAC and policy engines are the fine-grained ceiling.

• RBAC is the baseline in every warehouse: Snowflake, BigQuery, Databricks Unity Catalog, and Postgres all ship a native role system, and coarse access ("the analytics team can read the analytics schema") is best expressed as roles. You do not rip out RBAC; you build on it.
• ABAC layers on top when the requirements become conditional: data residency, PII masking by clearance, purpose-based access, need-to-know. It rarely replaces RBAC wholesale — the mature pattern is RBAC for the coarse grant, ABAC for the fine-grained last mile.
• Open Policy Agent (OPA) is the reference open-source policy engine: it externalises the decision into a purpose-built service so authorization logic lives in one place as policy as code (Rego), not scattered across every microservice and warehouse. It is a general-purpose PDP; it does not itself enforce inside the database — a Policy Enforcement Point calls it.
• Immuta and Privacera are the managed data access governance platforms that make ABAC operational for data teams without hand-rolling OPA: tag-driven, plain-language / attribute policies that compile down to native warehouse controls (Snowflake row-access policies, dynamic masking, Databricks Unity Catalog), plus centralised audit. Immuta is attribute/tag-first; Privacera grew out of the Apache Ranger ecosystem with broad connector reach.

What interviewers listen for.

• Do you frame it as "RBAC for coarse access, ABAC for fine-grained" rather than "RBAC vs ABAC, pick one"? — senior signal.
• Do you name role explosion as the reason RBAC alone fails at fine granularity? — required answer.
• Do you separate the decision (PDP) from the enforcement (PEP) when you describe a policy engine? — senior signal.
• Do you say "policy as code" — version-controlled, tested, diffable — rather than "we click grants in the UI"? — senior signal.
• Do you describe authorization as "a decision over subject, resource, action, and environment" rather than vague "permissions"? — required answer.

Worked example — the four-axis RBAC vs ABAC comparison table

Detailed explanation. The single most useful artifact for an access-control interview is a memorised comparison across the four axes. Every senior governance discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from hand-waving. Walk through building the table for a hypothetical orders warehouse table that must serve regional analysts, a fraud team, and auditors under GDPR-style residency rules.

• Granularity axis. How fine can each model go without exploding?
• Scale axis. How does the policy count grow with new requirements?
• Context axis. Can the decision depend on time, purpose, IP, data classification?
• Audit axis. How do you answer "who could see this, and why" six months later?

Question. Build the four-axis comparison for RBAC, ABAC, and a policy engine, and state which axis forces which model.

Input.

Axis	RBAC	ABAC	Policy engine (OPA / Immuta)
Granularity	object-level (table/schema)	row + column + cell via rules	row + column, decided per request
Scale	multiplicative (role explosion)	additive (attributes compose)	additive; policy bundled centrally
Context-awareness	static (has role or not)	dynamic (time, purpose, IP, tag)	dynamic; enforced by PEP
Auditability	snapshot grants; "why" lost	policy-as-code + decision logs	central decision logs

Code.

-- The shared target table all three models will govern (Snowflake dialect)
CREATE TABLE analytics.orders (
    order_id      NUMBER      NOT NULL,
    customer_id   NUMBER      NOT NULL,
    region        STRING      NOT NULL,   -- 'EU', 'US', 'APAC'  (drives residency)
    total_cents   NUMBER      NOT NULL,
    ssn           STRING,                 -- PII: mask unless purpose = fraud
    created_at    TIMESTAMP_NTZ NOT NULL
);

-- RBAC alone would need a role PER (region x sensitivity x purpose):
--   role_orders_eu_ro, role_orders_us_ro, role_orders_eu_fraud, ...
-- ABAC expresses the same rules as attributes:
--   allow if subject.region == row.region
--   mask ssn unless subject.purpose == 'fraud'

Step-by-step explanation.

1. The orders table has three governance drivers baked into columns: region (data residency), ssn (PII masking), and an implied purpose (fraud vs analytics). RBAC must materialise a role for each combination of these drivers, because a role is a static bundle of grants.
2. Count the RBAC roles: 3 regions × 2 sensitivity levels (sees-SSN / masked) × 2 purposes (analytics / fraud) = 12 roles for one table. Multiply across a hundred sensitive tables and the role count is in the thousands. This is the role-explosion mechanism made concrete.
3. ABAC collapses those 12 roles into two rules: "row visible if subject.region == row.region" and "SSN masked unless subject.purpose == 'fraud'." The rules compose — adding a fourth region adds zero rules, just one more attribute value.
4. The context axis is where RBAC simply cannot follow: "only during an active fraud case" is a runtime condition. RBAC has no place to put it; ABAC puts it in the environment attribute set.
5. The audit axis flips the advantage: RBAC's grants are a static list you can dump today, but nobody can explain why a role exists. ABAC's policy is code in git — you read the rule and the intent is obvious — but "who actually saw the SSN column last Tuesday" requires decision logs, not a grant dump.

Output.

Requirement	Model that fits	Why
"Analytics team reads the analytics schema"	RBAC	coarse, static, object-level — a role is perfect
"Analysts see only their own region's rows"	ABAC (row filter)	one rule vs one role per region
"Mask SSN unless active fraud case"	ABAC (column mask + context)	runtime purpose can't be a static role
"Prove who could see PII in Q2, and why"	policy engine + decision logs	central, queryable audit trail

Rule of thumb. Never frame it as "RBAC or ABAC." Coarse, stable, object-level access → RBAC. Fine-grained, conditional, per-row/per-cell access → ABAC via a policy engine. Write the four-axis table on the whiteboard; the requirement tells you which row you're on.

Worked example — what interviewers actually probe on access control

Detailed explanation. The senior access-control interview has a predictable shape: an ambiguous opener ("how would you control who sees what in our warehouse?"), then progressive narrowing to test whether you know the axes and the role-explosion escape. Candidates who name the hybrid model score highest; candidates who say "we'd set up roles" and stop score lowest. Walk through the grading rubric.

• Ambiguous opener. "How do you govern access to sensitive tables?" — invites you to name RBAC and its limits.
• Follow-up 1. "Your role count hit 900 — what now?" — probes the role-explosion escape.
• Follow-up 2. "How do you do row-level and column masking?" — probes granularity.
• Follow-up 3. "How do you enforce 'only during business hours from a corporate IP'?" — probes context.
• Follow-up 4. "Prove to an auditor who saw PII last quarter." — probes auditability.

Question. Draft a 5-minute senior authorization answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Model named	"we'd use roles"	"RBAC for coarse grants, ABAC for fine-grained"
Role explosion	"add more roles"	"stop minting roles; move conditions to attributes"
Granularity	"grant on the table"	"row-access policy + dynamic masking on tags"
Context	"not sure"	"environment attributes in the policy: time, IP, purpose"
Audit	"list the grants"	"central decision logs, queryable per subject/resource"

Code.

Senior authorization answer template (5 minutes)
================================================

Minute 1 — name the model up front
  "RBAC for coarse access — the analytics team reads the analytics
   schema — and ABAC on top for the fine-grained, conditional rules."

Minute 2 — the role-explosion escape
  "When role count explodes it's a symptom: conditions that should be
   attributes are being encoded as roles. I move region, sensitivity,
   and purpose into attributes and delete the combinatorial roles."

Minute 3 — granularity
  "Row-level via a row-access policy keyed on subject.region == row.region;
   column-level via dynamic masking on a PII tag. One policy covers
   every table carrying that tag."

Minute 4 — context and enforcement
  "The decision lives in a policy engine (OPA, or a managed platform
   like Immuta/Privacera). The warehouse or a query gateway is the
   enforcement point; it asks the engine 'allow?' with the subject,
   resource, action, and environment, and gets back allow/deny plus
   any masking to apply."

Minute 5 — audit and policy as code
  "Policies are code in git — reviewed, tested, diffable. Every decision
   is logged, so I can answer 'who could see PII in Q2, and under which
   rule' from the decision log, not a stale grant dump."

Step-by-step explanation.

1. Minute 1 frames the whole answer as a hybrid, which is the senior framing. Weak candidates present RBAC and ABAC as a binary and pick one; strong candidates use RBAC as the floor and ABAC as the fine-grained ceiling.
2. Minute 2 pre-empts the role-explosion follow-up. Naming it as a symptom ("conditions masquerading as roles") rather than a problem to solve with more roles is the senior signal — you diagnose the root cause instead of adding to it.
3. Minute 3 demonstrates you know the two granularity mechanisms — row-access policies and dynamic column masking — and that a tag-driven policy covers many tables at once, which is the scale win.
4. Minute 4 introduces the PDP/PEP separation without jargon-dumping: the engine decides, the warehouse enforces. Naming OPA and the managed platforms shows range.
5. Minute 5 closes on policy as code and decision logs, hitting the audit axis. Saying "decision log, not grant dump" signals you've actually run a compliance audit, not just read a docs page.

Output.

Grading criterion	Weak score	Senior score
Frames as hybrid RBAC + ABAC	rare	mandatory
Diagnoses role explosion as a symptom	rare	senior signal
Names row + column mechanisms	occasional	mandatory
Separates decision from enforcement	rare	senior signal
Cites decision logs for audit	rare	senior signal

Rule of thumb. The senior authorization answer is a 5-minute monologue that covers granularity, scale, context, and audit without waiting for the follow-ups. Rehearse it once; deploy it every time an interviewer says "access control."

Senior interview question on choosing an access-control model

A senior interviewer often opens with: "You inherit a Snowflake warehouse with 600 hand-crafted roles, nightly complaints that analysts can see other regions' data, and an upcoming SOC 2 audit that needs 'who could access PII and why.' Walk me through the model you'd move to, how you'd stop the role explosion, and how you'd make the whole thing auditable."

Solution Using a hybrid RBAC-plus-ABAC design with a policy engine and decision logs

-- Step 1 — keep a SMALL set of coarse RBAC roles (the durable floor)
CREATE ROLE role_analyst;      -- read analytics schema
CREATE ROLE role_fraud;        -- fraud investigators
CREATE ROLE role_platform;     -- data platform / admin

GRANT USAGE   ON DATABASE analytics TO ROLE role_analyst;
GRANT USAGE   ON SCHEMA analytics.public TO ROLE role_analyst;
GRANT SELECT  ON ALL TABLES IN SCHEMA analytics.public TO ROLE role_analyst;
GRANT ROLE role_analyst TO ROLE role_fraud;   -- fraud inherits analyst

-- Step 2 — push the CONDITIONS out of roles and into attributes.
-- Attributes come from the IdP / a lookup table, not from role names.
CREATE TABLE governance.user_attributes (
    user_name STRING PRIMARY KEY,
    region    STRING,      -- 'EU' | 'US' | 'APAC'
    purpose   STRING       -- 'analytics' | 'fraud'
);

-- Step 3 — ROW-LEVEL security: one policy replaces one-role-per-region
CREATE ROW ACCESS POLICY analytics.rap_region_match
AS (row_region STRING) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1 FROM governance.user_attributes ua
        WHERE ua.user_name = CURRENT_USER()
          AND (ua.region = row_region OR ua.purpose = 'fraud')  -- fraud sees all regions
    );

ALTER TABLE analytics.orders
    ADD ROW ACCESS POLICY analytics.rap_region_match ON (region);

-- Step 4 — COLUMN-LEVEL masking: one policy covers every PII-tagged column
CREATE MASKING POLICY analytics.mask_ssn AS (val STRING) RETURNS STRING ->
    CASE
        WHEN EXISTS (SELECT 1 FROM governance.user_attributes ua
                     WHERE ua.user_name = CURRENT_USER() AND ua.purpose = 'fraud')
             THEN val
        ELSE '***-**-****'
    END;

ALTER TABLE analytics.orders MODIFY COLUMN ssn SET MASKING POLICY analytics.mask_ssn;

# Step 5 — the same rules as portable policy-as-code (OPA / Rego) for
# services that query outside Snowflake (the PDP a query gateway calls)
package dataplatform.authz

import future.keywords.if
import future.keywords.in

default allow := false

# coarse RBAC: role gate
allow if {
    input.action == "select"
    "role_analyst" in input.subject.roles
    row_visible
    not blocked_by_time
}

# fine-grained ABAC: region residency (row-level intent)
row_visible if input.subject.region == input.resource.region
row_visible if input.subject.purpose == "fraud"       # fraud overrides residency

# environment attribute: business-hours guardrail
blocked_by_time if {
    input.environment.hour < 6
}

Step-by-step trace.

Step	Before (600 roles)	After (hybrid)
Coarse access	1 role per team + variants	3 stable roles
Region isolation	1 role per region (explosion)	1 row-access policy on region
PII masking	1 role per sensitivity	1 masking policy on the ssn column
Context (fraud override, hours)	impossible in RBAC	attributes + Rego environment
Audit "who + why"	grant dump, no "why"	decision logs + policy-as-code in git
New region onboarded	new role + regrants	insert one attribute row

After the migration, coarse access is three durable roles, region isolation and PII masking are two policies that cover every table carrying the pattern, the fraud-override and business-hours conditions live as attributes the policy reads at request time, and the SOC 2 auditor gets a queryable decision log plus a git history of every policy change — not a 600-row grant spreadsheet nobody can explain.

Output:

Metric	Before	After
Role count	~600	3 coarse roles
Region-isolation objects	1 role per region	1 row-access policy
PII-masking objects	1 role per level	1 masking policy per tag
Context-aware rules	0 (not possible)	N attributes, composable
Audit answer time	days (manual)	minutes (query the log)

Why this works — concept by concept:

• RBAC as the coarse floor — a small, stable set of roles (analyst, fraud, platform) grants object-level access. Roles are cheap to reason about when they stay coarse; the mistake is trying to encode conditions as roles. Keep roles for "which schema," not "which row."
• Attributes replace combinatorial roles — region and purpose live in a user_attributes lookup (sourced from the IdP), so a new region is one row, not a new role plus re-grants. This is the mechanism that stops role explosion — conditions become data, not schema.
• Row-access policy + masking policy — Snowflake evaluates one ROW ACCESS POLICY per query to filter rows and one MASKING POLICY per column to redact cells, both reading the current user's attributes. One policy object covers every table that carries the pattern, which is the additive-scale win over one-role-per-combination.
• Policy as code (Rego) — the same rules expressed in OPA make the decision portable to services that query outside Snowflake (a query gateway, a data API). The policy is version-controlled, testable, and diffable, so the "why" is self-documenting.
• Cost — one row-access policy + one masking policy per sensitive pattern (O(patterns), not O(roles)), a small attribute lookup, and decision logging. Compared to 600 hand-maintained roles (O(regions × levels × purposes)), this is O(1) per new dimension. The eliminated cost is the human time spent reasoning about a role graph nobody understands, and the audit that used to take days now takes a query.

Design
Topic — design
System-design problems on access-control architecture

Practice →

SQL Topic — sql SQL grants, roles, and row-filtering problems

Practice →

---

2. RBAC — role-based access control done right

role-based access control is the durable floor every warehouse ships — coarse, auditable, and prone to explosion when you push it too fine

The mental model in one line: role-based access control assigns privileges to named roles, assigns roles to users (or to other roles, forming a hierarchy), and answers "can this user do this action on this object" by asking "does the user hold a role that has been granted that privilege" — it is cheap to reason about and trivially auditable at the object level, but its role count grows multiplicatively the moment you try to encode row-level, column-level, or conditional access, which is the failure mode every senior engineer has lived through. Every warehouse — Snowflake, BigQuery, Databricks, Postgres — implements RBAC natively, so it is the floor you build on, never the thing you remove.

The four axes for RBAC.

• Granularity. Object-level by design: database, schema, table, and (in modern warehouses) column via column-level grants. Row-level is not native to classic RBAC — you fake it with one role per row-partition, which is where explosion begins. Treat RBAC as excellent for "which schema" and poor for "which row."
• Scale. The Achilles heel. Role count = teams × environments × sensitivity levels × regions × purposes. Each new dimension multiplies the count. A 50-person startup lives happily on 15 roles; a regulated enterprise drowns in 3,000. The count itself is fine — the combinatorial growth is the problem.
• Context-awareness. None. A role is static: you hold it or you don't. "Only during business hours," "only from the corporate VPN," "only for an active case" cannot be expressed — there is no place in a GRANT for a runtime condition. This is the hard ceiling that forces ABAC.
• Auditability. Excellent at a point in time, poor over time. You can dump every role and every grant in one query, but the intent — why role_fin_eu_ro_legacy exists and whether it's safe to drop — is lost the moment its author leaves. This is why role review is a recurring, dreaded chore.

Role hierarchy and inheritance — the mechanism that keeps role counts sane (when used well).

• What it is. A role can be granted to another role, so the child role inherits all the parent's privileges. role_admin inherits role_analyst inherits role_base. Users are assigned the most specific role they need.
• Why it helps. Common privileges live once, in a base role. Adding a warehouse-wide read grant means granting it to role_base; every descendant inherits it. Without hierarchy you'd re-grant to every role.
• Where it hurts. Deep or diamond-shaped hierarchies become as hard to reason about as the flat explosion they were meant to prevent. "What can role_x actually do" requires walking the whole inheritance graph. Keep hierarchies shallow (2–3 levels) and acyclic.
• Separation of duties. Two roles that must never be held together (e.g. role_payments_write and role_payments_audit) enforce SoD. RBAC expresses this as a constraint you check at assignment time; most warehouses don't enforce it natively, so it lives in your provisioning tooling.

The three failure modes senior engineers pre-empt.

• Role explosion. The multiplicative growth described above. Mitigation: (a) keep roles coarse and object-level; (b) the moment a role name encodes a condition (region, purpose, sensitivity), that condition belongs in an attribute, not a role; (c) a periodic role-review that deletes orphaned and duplicate roles.
• Over-broad grants (privilege creep). Roles accrete grants over time — someone needs one table, grants SELECT ON ALL TABLES, and the over-grant is never revoked. Mitigation: grant the minimum object scope, prefer future-grants scoped to a schema over blanket grants, and audit grant diffs in review.
• Orphaned and duplicate roles. Roles created for a project that ended, or near-identical roles created because nobody could find the existing one. Mitigation: roles as code (Terraform / dbt / a provisioning repo) so every role has an owner and a reason in version control; no click-ops role creation.

Common interview probes on RBAC.

• "What's the role-explosion problem and how do you escape it?" — required answer is "conditions encoded as roles; move them to attributes."
• "How does role hierarchy work and when does it hurt?" — inheritance; deep/diamond graphs become unauditable.
• "How do you do least privilege in RBAC?" — minimum object scope, future-grants, periodic review.
• "Can RBAC do row-level security?" — only by faking it with one role per partition; use row-access policies / ABAC instead.

Worked example — a clean Snowflake role hierarchy with least privilege

Detailed explanation. The canonical warehouse RBAC setup separates access roles (grant privileges on objects) from functional roles (assigned to users), with functional roles inheriting access roles. This two-layer pattern — popularised by Snowflake's own guidance — keeps grants in one place and makes "what can this user do" answerable. Walk through building it for an analytics database.

• Access roles. One per object-scope: ar_analytics_read, ar_finance_read, ar_analytics_write.
• Functional roles. One per job function: fr_analyst, fr_finance_analyst, fr_engineer.
• Assignment. Functional roles inherit the access roles they need; users get functional roles only.
• Least privilege. Each access role grants the minimum on exactly one scope.

Question. Build the two-layer role hierarchy so an analyst reads analytics, a finance analyst reads analytics and finance, and an engineer reads and writes analytics.

Input.

Role	Type	Inherits	Effective access
ar_analytics_read	access	—	SELECT on analytics schema
ar_finance_read	access	—	SELECT on finance schema
ar_analytics_write	access	ar_analytics_read	+ INSERT/UPDATE analytics
fr_analyst	functional	ar_analytics_read	read analytics
fr_finance_analyst	functional	ar_analytics_read, ar_finance_read	read analytics + finance
fr_engineer	functional	ar_analytics_write	read + write analytics

Code.

-- 1. Access roles — the ONLY roles that hold object privileges
CREATE ROLE ar_analytics_read;
CREATE ROLE ar_finance_read;
CREATE ROLE ar_analytics_write;

GRANT USAGE ON DATABASE prod TO ROLE ar_analytics_read;
GRANT USAGE ON SCHEMA prod.analytics TO ROLE ar_analytics_read;
GRANT SELECT ON ALL TABLES IN SCHEMA prod.analytics TO ROLE ar_analytics_read;
-- future grant: new tables are covered automatically (least surprise, not least privilege risk)
GRANT SELECT ON FUTURE TABLES IN SCHEMA prod.analytics TO ROLE ar_analytics_read;

GRANT USAGE ON SCHEMA prod.finance TO ROLE ar_finance_read;
GRANT SELECT ON ALL TABLES IN SCHEMA prod.finance TO ROLE ar_finance_read;

-- write role INHERITS read, then adds mutation privileges
GRANT ROLE ar_analytics_read TO ROLE ar_analytics_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA prod.analytics TO ROLE ar_analytics_write;

-- 2. Functional roles — assigned to USERS; they only inherit access roles
CREATE ROLE fr_analyst;
CREATE ROLE fr_finance_analyst;
CREATE ROLE fr_engineer;

GRANT ROLE ar_analytics_read TO ROLE fr_analyst;

GRANT ROLE ar_analytics_read TO ROLE fr_finance_analyst;
GRANT ROLE ar_finance_read  TO ROLE fr_finance_analyst;

GRANT ROLE ar_analytics_write TO ROLE fr_engineer;

-- 3. Users get FUNCTIONAL roles only — never access roles directly
GRANT ROLE fr_analyst         TO USER alice;
GRANT ROLE fr_finance_analyst TO USER bob;
GRANT ROLE fr_engineer        TO USER carol;

Step-by-step explanation.

1. Access roles are the only roles that ever hold object privileges (GRANT SELECT ON ...). This centralises grants: to change what "read analytics" means, you edit one access role, and every functional role that inherits it updates automatically.
2. Functional roles are what users actually receive. They hold no object grants directly — they only inherit access roles. This means "what can Bob do" is answered by listing the access roles his functional role inherits, not by walking a tangle of direct grants.
3. ar_analytics_write inherits ar_analytics_read and adds mutation privileges. This models the natural "writers can also read" relationship without re-granting SELECT — the inheritance does it.
4. GRANT SELECT ON FUTURE TABLES ensures new tables in the schema are covered without a manual re-grant. This is a deliberate trade: least surprise (analysts don't file tickets for new tables) at the cost of least privilege granularity (a new sensitive table is auto-readable). For sensitive schemas, drop the future-grant and require explicit grants.
5. Users receive functional roles only. The rule "never grant an access role directly to a user" is what keeps the model auditable — every user's access is fully described by their functional roles, and every functional role's access is fully described by its access roles.

Output.

User	Functional role	Effective privileges
alice	fr_analyst	SELECT on analytics
bob	fr_finance_analyst	SELECT on analytics + finance
carol	fr_engineer	SELECT + INSERT/UPDATE/DELETE on analytics

Rule of thumb. Separate access roles (hold object grants, one per scope) from functional roles (assigned to users, inherit access roles). Users get functional roles only. This two-layer pattern keeps grants in one place, makes "what can this user do" answerable, and delays role explosion — but it does not prevent it once conditions enter the picture.

Worked example — watching the role count explode (and diagnosing it)

Detailed explanation. A regulated fintech starts with the clean two-layer hierarchy above, then compliance adds requirements one at a time: data must stay in-region, PII must be masked by clearance, and access must be scoped by business purpose. Each requirement, encoded as roles, multiplies the count. Walk through the explosion so you can recognise and reverse it.

• Baseline. 3 functional roles, 3 access roles = 6.
• + Region (EU/US/APAC). Read roles fork per region: ar_analytics_read_eu, _us, _apac.
• + Sensitivity (full/masked). Each region forks again.
• + Purpose (analytics/fraud). Each forks a third time.

Question. Quantify the role count as each requirement is encoded as roles, then show the attribute-based alternative that flattens it.

Input.

Stage	Dimensions	Read-role count	Total roles
Baseline	—	3	6
+ region	×3	9	~12
+ sensitivity	×2	18	~21
+ purpose	×2	36	~39

Code.

# Quantify the multiplicative growth of RBAC roles vs additive ABAC rules
def rbac_role_count(teams, regions, sensitivity_levels, purposes):
    """RBAC: one role per COMBINATION -> multiplicative."""
    return teams * regions * sensitivity_levels * purposes

def abac_rule_count(dimensions):
    """ABAC: one rule per DIMENSION -> additive."""
    return len(dimensions)   # region rule + sensitivity rule + purpose rule

# A modestly regulated platform
print(rbac_role_count(teams=5, regions=3, sensitivity_levels=2, purposes=2))
# -> 60 roles for a SINGLE object family

print(abac_rule_count(["region", "sensitivity", "purpose"]))
# -> 3 rules, regardless of how many regions/levels/purposes exist

# Scale the regions from 3 to 12 (global rollout):
print(rbac_role_count(5, 12, 2, 2))   # -> 240 roles
print(abac_rule_count(["region", "sensitivity", "purpose"]))  # -> still 3

Step-by-step explanation.

1. The baseline six-role hierarchy is healthy — coarse, object-level, auditable. The explosion does not start with the hierarchy; it starts when conditions (region, sensitivity, purpose) get encoded as roles.
2. Adding regions forks every read role three ways because a role is static — there is no runtime "which region is this user" check, so you must bake the region into the role name and grant. Three regions triples the read roles.
3. Adding a sensitivity dimension (sees-PII vs masked) doubles again; adding purpose doubles a third time. The growth is multiplicative — 3 × 2 × 2 = 12× the baseline read roles — because each combination needs its own static bundle.
4. The Python model makes the asymmetry explicit: RBAC is teams × regions × levels × purposes (multiplicative), ABAC is len(dimensions) (additive). Scaling regions from 3 to 12 takes RBAC from 60 to 240 roles and leaves ABAC at 3 rules.
5. The diagnosis rule: any role name containing a condition (_eu, _masked, _fraud) is a smell. That condition is an attribute wearing a role costume. The fix is to delete the combinatorial roles and evaluate the condition as an attribute at request time — which is exactly what section 3 (ABAC) does.

Output.

Dimensions active	RBAC roles	ABAC rules
team only	5	0 conditions
+ 3 regions	15	+1 rule
+ 2 sensitivity	30	+1 rule
+ 2 purposes	60	+1 rule
regions 3 → 12	240	still 3 rules

Rule of thumb. A role name that encodes a condition (_eu, _pii_masked, _fraud) is role explosion in progress. Roles should name functions ("analyst," "engineer"), never conditions. When you catch a condition in a role name, that is your cue to move it to an attribute.

Senior interview question on RBAC at scale

A senior interviewer might ask: "Your Postgres analytics database has grown to 400 roles, half of them named things like ro_orders_eu_2022. New-hire onboarding takes a week because nobody knows which roles to grant. Walk me through how you'd audit the existing roles, collapse the explosion, and set up a sustainable model — with the SQL you'd actually run."

Solution Using a role audit, a two-layer redesign, and attribute-driven row security

-- 1. AUDIT — enumerate roles, their members, and their grants
-- Which roles exist and who is in them?
SELECT r.rolname AS role, m.rolname AS member
FROM   pg_auth_members am
JOIN   pg_roles r ON r.oid = am.roleid
JOIN   pg_roles m ON m.oid = am.member
ORDER  BY r.rolname;

-- What object privileges does each role actually hold?
SELECT grantee AS role, table_schema, table_name, privilege_type
FROM   information_schema.role_table_grants
WHERE  grantee NOT IN ('postgres')
ORDER  BY grantee, table_schema, table_name;

-- Orphaned roles: exist but have zero members and zero grants
SELECT r.rolname
FROM   pg_roles r
LEFT   JOIN pg_auth_members am ON am.roleid = r.oid
WHERE  r.rolcanlogin = false
  AND  am.roleid IS NULL
  AND  NOT EXISTS (
        SELECT 1 FROM information_schema.role_table_grants g
        WHERE g.grantee = r.rolname);

-- 2. REDESIGN — two-layer hierarchy; conditions become attributes, not roles
-- Access roles (object grants only)
CREATE ROLE ar_orders_read NOLOGIN;
GRANT USAGE ON SCHEMA analytics TO ar_orders_read;
GRANT SELECT ON analytics.orders TO ar_orders_read;

-- Functional roles (assigned to users)
CREATE ROLE fr_analyst NOLOGIN;
GRANT ar_orders_read TO fr_analyst;

-- Attribute table replaces the ro_orders_<region> roles entirely
CREATE TABLE governance.user_attributes (
    user_name TEXT PRIMARY KEY,
    region    TEXT NOT NULL,
    purpose   TEXT NOT NULL DEFAULT 'analytics'
);

-- 3. ROW-LEVEL SECURITY — one policy replaces every ro_orders_<region> role
ALTER TABLE analytics.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_region_isolation ON analytics.orders
    FOR SELECT
    USING (
        region = (SELECT ua.region FROM governance.user_attributes ua
                  WHERE ua.user_name = current_user)
        OR (SELECT ua.purpose FROM governance.user_attributes ua
            WHERE ua.user_name = current_user) = 'fraud'
    );

-- 4. Onboarding is now ONE functional-role grant + ONE attribute row
GRANT fr_analyst TO dave;
INSERT INTO governance.user_attributes(user_name, region) VALUES ('dave', 'EU');

Step-by-step trace.

Step	Before (400 roles)	After (redesign)
Enumerate access	walk 400 roles by hand	2 queries (members + grants)
Region isolation	~1 role per region	1 RLS policy on orders
Onboarding	find + grant N cryptic roles	1 role grant + 1 attribute row
New region	new role + backfill grants	insert attribute values
Orphan cleanup	unknown which are safe	orphan query lists them
"Why does role X exist"	lost	policy + attribute in git

After the redesign, the 400 roles collapse to a handful of functional and access roles, region isolation is one row-level-security policy reading the attribute table, and onboarding a new analyst is a single role grant plus one attribute row — down from a week of guessing which ro_orders_* roles to assign.

Output:

Metric	Before	After
Total roles	~400	< 20
Onboarding time	~1 week	minutes
New-region effort	new role + regrants	1 attribute value
Orphan detection	manual, error-prone	1 query
Auditability of "why"	none	policy-as-code + git

Why this works — concept by concept:

• Audit before you touch — pg_auth_members, information_schema.role_table_grants, and an orphan query give you the full picture (who is in what, what each grants, which are dead) before any change. You cannot safely collapse an explosion you have not enumerated.
• Two-layer hierarchy — access roles hold object grants; functional roles are assigned to users and inherit access roles. Collapsing 400 condition-named roles into a few function-named roles is the structural fix; the conditions move out entirely.
• Row-level security replaces per-region roles — Postgres RLS evaluates one USING predicate per query against the current user's attributes, so ro_orders_eu, _us, _apac, and every future region collapse into one policy that reads a lookup table. This is native RBAC meeting ABAC: the role is coarse, the row filter is attribute-driven.
• Onboarding as data, not schema — a new user is one functional-role grant plus one attribute row. Onboarding stops being a scavenger hunt through cryptic role names and becomes a two-line, reviewable change.
• Cost — a handful of roles (O(functions)) plus one RLS policy per sensitive table (O(tables)) plus a small attribute table, versus O(regions × purposes × sensitivity) roles. The per-query cost is one indexed lookup in user_attributes; the eliminated cost is the human time lost to an unauditable role graph and week-long onboarding.

SQL
Topic — sql
SQL role, grant, and row-level-security problems

Practice →

Design Topic — design Design problems on role modeling and least privilege

Practice →

---

3. ABAC — attribute-based access control and policy as code

attribute-based access control decides at request time from subject, resource, action, and environment — one rule where RBAC needed a hundred roles

The mental model in one line: attribute-based access control makes an access decision by evaluating a rule over four attribute categories — the subject (who: role, region, clearance), the resource (what: table, tag, sensitivity), the action (SELECT, INSERT), and the environment (when/where/why: time, IP, purpose) — so instead of pre-baking a role for every combination of conditions, you write one rule that combines attributes live, which is why a single ABAC policy can replace hundreds of RBAC roles. ABAC is where fine-grained access control and policy as code meet: the rule is code, version-controlled and tested, and the decision is computed per request from data, not looked up in a static grant.

The four attribute categories — the vocabulary of every ABAC rule.

• Subject attributes. Everything known about the requester: roles, department, region, clearance level, employment type, project membership. Sourced from the identity provider (Okta, Entra ID, an HR system). The subject is not just a user id — it is a bag of attributes the policy can test.
• Resource attributes. Everything known about the thing being accessed: table name, schema, data classification tag (PII, PHI, public), owning team, data-residency region, sensitivity level. In mature setups these come from a data catalog that tags columns and tables automatically.
• Action attributes. What the subject wants to do: read, write, delete, export, share. A policy can allow read while denying export of the same resource — a distinction RBAC struggles to make.
• Environment attributes. Context that is neither subject nor resource: request time, source IP / network, MFA freshness, declared purpose ("fraud-investigation"), risk score. This is the category RBAC has no place for and the reason context-dependent requirements force ABAC.

How an ABAC rule is structured — the allow/deny plus obligations model.

• The core rule. A boolean over attributes: allow if subject.clearance >= resource.sensitivity AND subject.region == resource.region AND environment.hour in business_hours. If it evaluates true, access is granted; otherwise denied (default-deny).
• Row-level filtering. Rather than a global allow/deny, the policy returns a filter: "return only rows where row.region == subject.region." The enforcement point appends this as a WHERE clause. One policy, every row, no per-region role.
• Column-level masking (obligations). The policy can attach an obligation — "allow, but mask the ssn column unless subject.purpose == 'fraud'." The enforcement point applies the transformation. This is fine-grained access control down to the cell.
• Default deny. Every serious ABAC system is default-deny: if no rule grants access, access is refused. This is the inverse of accidentally-over-broad RBAC grants and a core security property.

The three failure modes senior engineers pre-empt.

• Attribute sourcing (the PIP problem). A policy is only as trustworthy as its attributes. If subject.region comes from a stale HR export, the decision is wrong. Mitigation: source attributes from an authoritative, fresh Policy Information Point (the IdP, a live catalog), not a hand-maintained spreadsheet; treat attribute freshness as a first-class SLA.
• Policy sprawl and conflicts. Many small rules can conflict (one allows, one denies) or overlap confusingly. Mitigation: a clear combining algorithm (default-deny + explicit deny-overrides), a single policy repo, and tests — ABAC without tests is a liability because the decision is computed, not visible.
• Testing and dry-run gaps. A bad policy can silently over- or under-grant. Mitigation: unit-test policies against known subject/resource/action fixtures, run new policies in dry-run (log-only) before enforcing, and diff decisions against the old policy on a replay of real requests.

Common interview probes on ABAC.

• "What are the four attribute categories?" — subject, resource, action, environment.
• "How does ABAC avoid role explosion?" — attributes compose additively; one rule covers many combinations.
• "How do you do column masking in ABAC?" — an obligation attached to an allow decision.
• "Where do attributes come from and why does it matter?" — the PIP; stale attributes mean wrong decisions.

Worked example — a region-residency row filter as an ABAC rule

Detailed explanation. The most common ABAC requirement on a data platform is data residency: an analyst may only see rows for their own region. In RBAC this is one role per region (explosion); in ABAC it is one rule. Walk through the rule as portable policy-as-code in OPA's Rego, the language most policy engines use, returning both an allow decision and a row filter.

• Subject attribute. subject.region from the IdP.
• Resource attribute. resource.row.region — the region column of each candidate row.
• Rule. Allow the row if the regions match; fraud purpose overrides.
• Output. A partial-evaluation filter the query layer turns into a WHERE clause.

Question. Write the Rego policy that (a) allows a SELECT on orders only in business hours and (b) returns a row filter restricting rows to the subject's region, with a fraud override.

Input.

Attribute	Example value	Category
subject.roles	["role_analyst"]	subject
subject.region	"EU"	subject
subject.purpose	"analytics"	subject/environment
resource.table	"orders"	resource
action	"select"	action
environment.hour	14	environment

Code.

package dataplatform.orders

import future.keywords.if
import future.keywords.in

default allow := false

# ---- gate 1: coarse RBAC + action + environment ----
allow if {
    input.action == "select"
    "role_analyst" in input.subject.roles
    business_hours
}

business_hours if {
    input.environment.hour >= 6
    input.environment.hour <= 20
}

# ---- gate 2: the ROW FILTER returned to the query layer ----
# A caller asks OPA for `row_filter`; the query gateway appends it as WHERE.
row_filter := "region = '" || input.subject.region || "'" if {
    input.subject.purpose != "fraud"
}

# fraud sees all regions -> no restriction
row_filter := "TRUE" if {
    input.subject.purpose == "fraud"
}

// Example decision request the enforcement point sends to OPA
{
  "input": {
    "action": "select",
    "subject": { "roles": ["role_analyst"], "region": "EU", "purpose": "analytics" },
    "resource": { "table": "orders" },
    "environment": { "hour": 14 }
  }
}
// OPA response:
// { "allow": true, "row_filter": "region = 'EU'" }

Step-by-step explanation.

1. The policy is default allow := false — default-deny. Nothing is permitted unless a rule explicitly allows it, which is the core ABAC safety property and the inverse of RBAC's accidental over-grants.
2. Gate 1 combines three attribute categories in one rule: the action (select), a subject attribute (holds role_analyst), and an environment attribute (business_hours). RBAC could express the role but has nowhere to put the business-hours condition — that is the ABAC advantage made concrete.
3. row_filter is the fine-grained mechanism. Instead of a global allow/deny, the policy returns a string filter the query gateway appends as a WHERE clause. One rule — region = subject.region — covers every region without a per-region role.
4. The fraud override is a second row_filter rule returning "TRUE" (no restriction) when subject.purpose == "fraud". Attributes compose: adding "fraud sees everything" is one more rule, not a new role times every region.
5. The decision request/response shows the contract: the enforcement point sends the four attribute categories as input, OPA returns allow plus the row_filter obligation. The database never holds a per-region role; the filter is computed per request.

Output.

Subject region	Purpose	allow	row_filter
EU	analytics	true	region = 'EU'
US	analytics	true	region = 'US'
EU	fraud	true	TRUE (all regions)
EU (hour = 3)	analytics	false	— (denied: outside hours)

Rule of thumb. Express residency and need-to-know as a row filter returned by the policy, not as a role per partition. One rule reading subject.region scales to any number of regions; the enforcement point turns the returned filter into a WHERE clause.

Worked example — column masking as an obligation

Detailed explanation. The second canonical ABAC requirement is column-level masking: the ssn column is redacted unless the requester has a legitimate purpose. ABAC models this as an obligation attached to an allow decision — "you may read the row, but transform this column." Walk through the policy and how the enforcement point applies the mask.

• Resource attribute. The column carries a PII classification tag (from the catalog).
• Subject/environment attribute. subject.purpose and subject.clearance.
• Obligation. "Mask ssn unless purpose == fraud AND clearance >= 3."
• Enforcement. The query layer wraps the column in a masking expression.

Question. Write the policy that returns per-column masking obligations for a SELECT on orders, and show the resulting query rewrite.

Input.

Column	Tag	Masked when
order_id	none	never
total_cents	none	never
ssn	PII	purpose != fraud OR clearance < 3
email	PII-low	clearance < 1

Code.

package dataplatform.masking

import future.keywords.if
import future.keywords.in

# Return the SET of columns that must be masked for this request.
mask_columns[col] if {
    some col in {"ssn"}
    not fraud_cleared
}

mask_columns[col] if {
    some col in {"email"}
    input.subject.clearance < 1
}

fraud_cleared if {
    input.subject.purpose == "fraud"
    input.subject.clearance >= 3
}

-- The enforcement point (query gateway) receives mask_columns = {"ssn"}
-- and rewrites the analyst's query BEFORE sending it to the warehouse:

-- Original query the analyst wrote:
SELECT order_id, total_cents, ssn, email FROM analytics.orders;

-- Rewritten query actually executed (ssn masked, email clear):
SELECT order_id,
       total_cents,
       '***-**-****'          AS ssn,     -- obligation applied
       email
FROM   analytics.orders;

Step-by-step explanation.

1. mask_columns is a set-valued rule: it returns the set of columns that must be masked for this specific request, rather than a single allow/deny. This is the obligation pattern — the decision is "allow, but with these transformations."
2. The ssn column is added to the mask set unless fraud_cleared — which itself is a two-attribute rule (purpose == fraud AND clearance >= 3). Composing two subject attributes into one derived condition keeps the mask rule readable.
3. The email column uses a different threshold (clearance < 1), demonstrating that masking is per-column, not per-table. RBAC would need a role per (column × clearance); ABAC uses one rule per column with an attribute threshold.
4. The enforcement point reads mask_columns = {"ssn"} and rewrites the SQL: the ssn column becomes a literal mask expression while other columns pass through unchanged. The warehouse never sees the real SSN for this request.
5. Because the mask is an obligation on an allow decision, the row is still returned — the analyst sees the order, just not the SSN. This is the difference between denying access to a row (row filter) and redacting a field (column mask); ABAC does both from attributes.

Output.

Requester	purpose	clearance	ssn seen as	email seen as
analyst	analytics	2	***-**-****	clear
fraud investigator	fraud	3	clear	clear
contractor	analytics	0	***-**-****	masked

Rule of thumb. Model masking as an obligation on an allow decision — "you may read the row; transform these columns" — driven by a resource tag (PII) and a subject attribute (purpose/clearance). One rule per tag covers every column carrying it, across every table.

Senior interview question on ABAC design

A senior interviewer might ask: "You need to enforce GDPR-style data residency (analysts see only their region) plus PII masking (mask email/SSN unless the requester's purpose and clearance allow it) across 200 tables, without minting a role per combination. Walk me through the ABAC design — the attributes, where they come from, the policy structure, and how you'd test it before enforcing."

Solution Using an attribute-driven policy with a PIP, default-deny, and dry-run testing

# authz.rego — one policy governs all 200 tables via resource tags
package dataplatform.authz

import future.keywords.if
import future.keywords.in

default decision := {"allow": false, "row_filter": "FALSE", "mask": []}

decision := {
    "allow": true,
    "row_filter": rf,
    "mask": mask_cols,
} if {
    input.action == "select"
    some r in input.subject.roles
    r in {"role_analyst", "role_fraud"}
    rf := region_filter
    mask_cols := masked_columns
}

# --- row filter: residency, with fraud override ---
region_filter := "TRUE" if input.subject.purpose == "fraud"
region_filter := sprintf("region = '%s'", [input.subject.region]) if {
    input.subject.purpose != "fraud"
}

# --- column mask: any PII-tagged column, unless cleared ---
masked_columns := [c | some c in input.resource.pii_columns; not cleared]
cleared if {
    input.subject.purpose == "fraud"
    input.subject.clearance >= 3
}

# pip.py — the Policy Information Point: fresh attributes, not a spreadsheet
import requests

def build_input(user: str, table: str, action: str, catalog, idp) -> dict:
    subject  = idp.get_attributes(user)          # region, roles, clearance, purpose (live from Okta)
    resource = catalog.describe(table)           # pii_columns, region tag (live from data catalog)
    return {
        "input": {
            "action": action,
            "subject": subject,          # {"roles": [...], "region": "EU", "clearance": 2, "purpose": "analytics"}
            "resource": resource,        # {"table": table, "pii_columns": ["ssn", "email"]}
            "environment": {"hour": __import__("datetime").datetime.utcnow().hour},
        }
    }

def authorize(user, table, action, catalog, idp) -> dict:
    payload = build_input(user, table, action, catalog, idp)
    r = requests.post("http://opa:8181/v1/data/dataplatform/authz/decision", json=payload)
    return r.json()["result"]   # {"allow": true, "row_filter": "region = 'EU'", "mask": ["ssn"]}

# test_authz.py — unit tests + dry-run replay BEFORE enforcing
import opa_client  # thin wrapper over `opa eval`

def decide(subject, resource, action="select", hour=14):
    return opa_client.eval("data.dataplatform.authz.decision", {
        "action": action, "subject": subject, "resource": resource,
        "environment": {"hour": hour},
    })

def test_eu_analyst_sees_only_eu_and_masked_ssn():
    d = decide({"roles": ["role_analyst"], "region": "EU", "clearance": 2, "purpose": "analytics"},
               {"table": "orders", "pii_columns": ["ssn", "email"]})
    assert d["allow"] is True
    assert d["row_filter"] == "region = 'EU'"
    assert "ssn" in d["mask"]

def test_fraud_sees_all_regions_unmasked():
    d = decide({"roles": ["role_fraud"], "region": "EU", "clearance": 3, "purpose": "fraud"},
               {"table": "orders", "pii_columns": ["ssn", "email"]})
    assert d["row_filter"] == "TRUE"
    assert d["mask"] == []

def test_default_deny_for_unknown_role():
    d = decide({"roles": ["role_random"], "region": "EU", "clearance": 5, "purpose": "x"},
               {"table": "orders", "pii_columns": []})
    assert d["allow"] is False

Step-by-step trace.

Layer	Component	Purpose
PIP	Okta + data catalog	live subject + resource attributes
Policy	authz.rego, default-deny	one policy for all 200 tables
Row filter	region_filter	residency; fraud override
Column mask	masked_columns over pii_columns	one rule per PII tag
Enforcement	query gateway	appends WHERE, rewrites masked cols
Testing	unit tests + dry-run replay	catch over/under-grants before enforcing

After deployment, all 200 tables are governed by one policy that reads live attributes from Okta and the data catalog: residency is a computed WHERE, PII masking is a computed column rewrite driven by each table's pii_columns tag, and the whole thing ships behind unit tests plus a dry-run replay that diffs new decisions against the old grants before a single request is enforced.

Output:

Metric	RBAC approach	ABAC approach
Policy objects for 200 tables	roles per combination (thousands)	1 policy
New region	new roles + regrants	1 attribute value
PII masking	role per column/level	1 rule per PII tag
Attribute freshness	manual	live from IdP/catalog
Pre-enforce safety	none	unit tests + dry-run

Why this works — concept by concept:

• Subject / resource / action / environment — every decision is a pure function of these four attribute bags. Making the inputs explicit is what lets one policy replace thousands of roles: the combinations live in the data (attributes), not the schema (roles).
• Default-deny + explicit allow — default decision := {"allow": false, ...} means nothing is permitted unless a rule grants it. This inverts RBAC's failure mode (accidental over-grants that linger) into a safe-by-default posture.
• Policy Information Point (PIP) — attributes come live from Okta (subject) and the data catalog (resource pii_columns, region tags), not a hand-maintained table. A policy is only as correct as its attributes; sourcing them freshly is the difference between a right and a wrong decision.
• Obligations (row filter + mask) — the decision returns not just allow/deny but a row_filter and a mask set. The enforcement point applies them as a WHERE clause and column rewrites, delivering row- and cell-level fine-grained access control from one policy.
• Tested, dry-run policy as code — the policy is code with unit tests and a dry-run replay that diffs decisions against the old grants before enforcing. Because the decision is computed, testing is not optional — it is the only way to know a policy change doesn't silently over- or under-grant.
• Cost — one policy (O(1) in tables), one OPA call per query (single-digit-ms in-process or sidecar), plus attribute lookups from the IdP/catalog (cacheable). Compared to O(regions × levels × purposes × tables) roles, this is additive in dimensions. The added cost is the OPA round-trip and the discipline of testing; the eliminated cost is the entire combinatorial role graph.

JSON
Topic — json
JSON policy-document and attribute-parsing problems

Practice →

Design Topic — design Design problems on attribute-based authorization

Practice →

---

4. Policy engines — OPA and the PDP/PEP model

A policy engine externalises the decision so authorization lives in one place — Open Policy Agent (OPA) is the reference implementation

The mental model in one line: a policy engine splits authorization into a decision and an enforcement: the Policy Decision Point (PDP) — Open Policy Agent (OPA) — evaluates policy-as-code (Rego) plus data and answers "allow?", while the Policy Enforcement Point (PEP) — a query gateway, a sidecar, the warehouse itself — intercepts the request, asks the PDP, and applies the verdict, so authorization logic stops being scattered across every service and lives in one tested, versioned place. This PDP/PEP separation is the architectural idea that makes ABAC operational at scale; without it, every service re-implements (and drifts on) the same rules.

The four-role reference architecture (XACML's vocabulary, still the standard).

• PEP — Policy Enforcement Point. The chokepoint that intercepts the request and enforces the verdict: a query gateway/proxy in front of the warehouse, an API middleware, a database plugin, or a sidecar. It never decides — it asks the PDP and applies the answer (allow, deny, filter, mask). Every access path must route through a PEP or the policy is bypassable.
• PDP — Policy Decision Point. The engine that decides: OPA loads Rego policy plus data documents and, given the request input, returns a decision. It is stateless per request and side-effect-free — pure policy evaluation.
• PAP — Policy Administration Point. Where policies are authored and distributed: a git repo of Rego, built into a bundle (a tarball of policy + data) that OPA pulls periodically. Policy-as-code lives here; changes are PRs, not clicks.
• PIP — Policy Information Point. Where attributes come from: the IdP for subject attributes, the data catalog for resource tags. OPA can pull these into its data document (push model) or fetch them during evaluation (pull model). Stale PIP = wrong decisions.

How OPA actually runs — deployment models.

• Sidecar / host-local. OPA runs next to each service (a sidecar container or a host daemon). The PEP calls localhost:8181 — sub-millisecond, no network hop, no shared-service outage risk. This is the default for latency-sensitive paths.
• Centralised service. A single OPA cluster all PEPs call over the network. Simpler to operate, but adds a network hop and a shared dependency; mitigate with caching and high availability.
• Bundles. Policies + data are packaged as a bundle and served from an object store; OPA polls for new bundles and hot-swaps them. This decouples policy distribution from service deploys — you ship a policy change by publishing a bundle, no redeploy.
• Decision logs. OPA can stream every decision (input + result) to a log sink. This is the audit backbone: "who could access what, under which policy version, and what did the engine decide" becomes a query over decision logs — exactly the SOC 2 / GDPR evidence RBAC grant dumps can't provide.

The three failure modes senior engineers pre-empt.

• Latency and availability. A network PDP call on every query adds latency and a failure dependency. Mitigation: run OPA host-local (sidecar) for hot paths, cache decisions for identical inputs with a short TTL, and define a fail-closed vs fail-open posture explicitly (fail-closed for sensitive data — if the PDP is down, deny).
• Stale bundles / policy drift. If a PEP runs an old bundle, it enforces an outdated policy. Mitigation: monitor bundle age and activation status per OPA instance, alert on bundles older than N minutes, and treat "all PEPs on the current bundle" as an SLO.
• Enforcement gaps (bypassable PEP). The strongest policy is worthless if a query path skips the PEP. Mitigation: make the PEP the only route to the data (network policy forces all queries through the gateway; direct warehouse credentials are not issued to end users), and periodically prove no bypass path exists.

Common interview probes on policy engines.

• "What's the difference between a PDP and a PEP?" — the PDP decides, the PEP enforces; separation is the whole point.
• "Why externalise authorization into OPA?" — one tested, versioned place instead of drift across services.
• "How do you keep OPA fast?" — host-local sidecar, decision caching, in-process eval.
• "How do you audit decisions?" — OPA decision logs, queryable per subject/resource/policy version.

Worked example — a query gateway (PEP) calling OPA (PDP) before running SQL

Detailed explanation. The canonical data-platform PEP is a query gateway: end users have no direct warehouse credentials; they send SQL to a gateway that authenticates them, asks OPA for a decision, and only then (possibly rewritten) runs the query against the warehouse. Walk through the gateway's request flow.

• Interception. The gateway is the only holder of warehouse credentials; all queries route through it.
• Decision request. It builds input (subject from the session, resource from parsing the SQL, action, environment) and posts to OPA.
• Enforcement. It applies the verdict: deny, or allow with a row filter and column masks it splices into the SQL.
• Logging. It records the decision for audit.

Question. Implement the gateway function that authorizes and rewrites a SELECT before execution.

Input.

Field	Source
subject	authenticated session (IdP)
resource	parsed from the submitted SQL
action	select
environment	request time, source IP

Code.

# gateway.py — Policy Enforcement Point in front of the warehouse
import requests, sqlglot           # sqlglot parses/rewrites the SQL

OPA_URL = "http://localhost:8181/v1/data/dataplatform/authz/decision"  # host-local sidecar

def authorize_and_run(session, sql: str):
    table = extract_table(sql)                       # parse resource from the query
    payload = {"input": {
        "action": "select",
        "subject": session.attributes,               # roles, region, clearance, purpose
        "resource": catalog.describe(table),         # pii_columns, region tag
        "environment": {"hour": now_hour(), "ip": session.ip},
    }}

    decision = requests.post(OPA_URL, json=payload, timeout=0.2).json()["result"]

    # fail-CLOSED: any error or explicit deny -> refuse
    if not decision.get("allow"):
        audit_log(session.user, table, "DENY", decision)
        raise PermissionError(f"denied on {table}")

    # apply obligations: row filter + column masks -> rewrite the SQL
    safe_sql = apply_row_filter(sql, decision["row_filter"])
    safe_sql = apply_column_masks(safe_sql, decision["mask"])

    audit_log(session.user, table, "ALLOW", decision)
    return warehouse.execute(safe_sql)               # only the gateway holds creds

def apply_row_filter(sql: str, row_filter: str) -> str:
    if row_filter in ("TRUE", ""):
        return sql
    tree = sqlglot.parse_one(sql)
    return tree.where(row_filter).sql()              # append AND (row_filter)

def apply_column_masks(sql: str, mask_cols: list[str]) -> str:
    tree = sqlglot.parse_one(sql)
    for col in mask_cols:
        tree = replace_select_col(tree, col, f"'***' AS {col}")
    return tree.sql()

Step-by-step explanation.

1. The gateway is the only component holding warehouse credentials — end users authenticate to the gateway, not the database. This is what makes the PEP unbypassable: there is no direct path to the data.
2. It builds the four-category input — subject from the authenticated session, resource by parsing the SQL to find the table and looking up its catalog tags, action, and environment (time, IP) — then posts to a host-local OPA sidecar (localhost:8181) with a tight 200 ms timeout.
3. The posture is fail-closed: any error, timeout, or explicit allow: false raises PermissionError. For sensitive data, a PDP outage must mean "deny," never "let it through." This is an explicit, deliberate choice.
4. On allow, the gateway applies the obligations: it appends the returned row_filter as a WHERE clause and rewrites masked columns, using a SQL parser (sqlglot) rather than string concatenation so the rewrite is safe against injection and complex queries.
5. Every decision — allow or deny, with the full input and result — is written to an audit log. This is the decision-log backbone: the gateway produces the queryable "who accessed what, under which policy" trail that satisfies auditors.

Output.

Submitted SQL	Decision	Executed SQL
SELECT * FROM orders (EU analyst)	allow + filter + mask	SELECT ... , '***' AS ssn FROM orders WHERE region='EU'
SELECT * FROM orders (fraud)	allow, no obligations	SELECT * FROM orders
SELECT * FROM payroll (analyst)	deny	(not executed; PermissionError)
any query at 03:00	deny (outside hours)	(not executed)

Rule of thumb. The PEP holds the credentials and the PDP holds the logic. Route every query through the gateway, call a host-local OPA with a tight timeout, fail closed on sensitive data, apply obligations with a real SQL parser (never string concat), and log every decision. Separation of decision from enforcement is the whole architecture.

Worked example — bundle distribution and decision-log audit

Detailed explanation. Two operational pillars make OPA production-grade: bundles (how policy reaches every PDP without redeploying services) and decision logs (how you audit what the PDPs decided). Walk through configuring both.

• Bundle. Policy + data packaged as a tarball in object storage; OPA polls and hot-swaps.
• Decision log. Every evaluation streamed to a sink (Kafka, an HTTP collector, a warehouse table).
• Audit query. "Who could see PII in Q2" becomes a SELECT over the decision-log table.

Question. Configure OPA to pull a policy bundle and stream decision logs, then write the audit query that answers "which users were allowed to read a PII column last quarter."

Input.

Component	Value
Bundle source	s3://policies/dataplatform/bundle.tar.gz
Poll interval	30 s
Decision log sink	HTTP collector → governance.decision_log table
Audit window	last quarter

Code.

# opa-config.yaml — bundle pull + decision-log streaming
services:
  bundle_store:
    url: https://s3.amazonaws.com/policies
  log_sink:
    url: https://collector.internal

bundles:
  dataplatform:
    service: bundle_store
    resource: dataplatform/bundle.tar.gz
    polling:
      min_delay_seconds: 20
      max_delay_seconds: 30          # hot-swap policy within ~30s, no service redeploy

decision_logs:
  service: log_sink
  reporting:
    min_delay_seconds: 5
    max_delay_seconds: 10

status:
  service: log_sink                  # emit bundle activation status for drift monitoring

-- Decision logs land in governance.decision_log (one row per evaluation).
-- Audit: which users were ALLOWED to read a PII-tagged column last quarter?
SELECT  input:subject:user::string          AS user_name,
        input:resource:table::string        AS table_name,
        result:mask                          AS masked_columns,
        COUNT(*)                             AS decisions,
        MIN(decision_time)                   AS first_seen,
        MAX(decision_time)                   AS last_seen
FROM    governance.decision_log
WHERE   decision_time >= DATEADD('quarter', -1, CURRENT_DATE)
  AND   result:allow = true
  AND   ARRAY_SIZE(result:mask) = 0          -- saw the PII UNMASKED
  AND   ARRAY_CONTAINS('orders'::variant, input:resource:pii_tables)
GROUP   BY 1, 2, 3
ORDER   BY decisions DESC;

Step-by-step explanation.

1. The bundles block tells OPA to poll bundle.tar.gz every 20–30 seconds. Publishing a new bundle (a merged PR that rebuilds the tarball) propagates the policy to every OPA instance within ~30 seconds — without redeploying any service. Policy distribution is fully decoupled from application deploys.
2. The decision_logs block streams every evaluation (the full input and result) to a collector that lands it in governance.decision_log. This turns authorization into an observable system: every decision is a queryable fact, not an ephemeral in-memory check.
3. The status block emits bundle activation status, so you can monitor which bundle version each OPA is running and alert on drift (a PEP stuck on a stale bundle). "All PEPs on the current bundle" becomes a monitorable SLO.
4. The audit query answers the SOC 2 / GDPR question directly: filter decisions in the last quarter where allow = true and mask was empty (the user saw PII unmasked) for PII tables. This is precisely the evidence a static RBAC grant dump cannot produce — it shows what was actually decided, per user, over time.
5. Because the log carries the full input, the auditor can also see why — which attributes drove each allow. Combined with the git history of the bundle, you can reconstruct "under policy version X, user Y was allowed because attribute Z" — complete provenance.

Output.

Capability	Without OPA logs	With OPA decision logs
"Who saw PII unmasked in Q2"	not answerable	one SQL query
Policy version at decision time	unknown	recorded per decision
Policy rollout time	service redeploy	~30 s bundle poll
Drift detection	none	bundle-status monitoring

Rule of thumb. Ship policy as bundles (git → tarball → object store → OPA poll) so a policy change is a publish, not a redeploy; stream decision logs to a warehouse table so audit is a query. Monitor bundle age/status as an SLO — a stale bundle is a silently wrong policy.

Senior interview question on policy-engine architecture

A senior interviewer might ask: "Design the authorization layer for a multi-service data platform where a Trino query engine, a Python data API, and a BI tool all hit the same governed tables. Requirements: one source of policy truth, sub-10 ms decisions on the hot path, no service can bypass it, and full audit. Walk me through the PDP/PEP topology, the deployment model, the failure posture, and the audit design."

Solution Using OPA sidecars, a shared bundle, fail-closed PEPs, and centralized decision logs

Topology (one policy source, many enforcement points)
=====================================================

           git (Rego + data)  --build-->  bundle.tar.gz  --> S3
                                                 |
              +----------------+-----------------+------------------+
              |                |                                    |
        OPA sidecar      OPA sidecar                          OPA sidecar
        (Trino PEP)      (data-API PEP)                       (BI-proxy PEP)
              |                |                                    |
        Trino plugin     API middleware                       BI query proxy
              |                |                                    |
              +----------------+------------ warehouse ------------+
                               |
                     decision logs --> Kafka --> governance.decision_log

# The ONE shared policy every PEP enforces (single source of truth)
package dataplatform.authz

import future.keywords.if
import future.keywords.in

default decision := {"allow": false, "row_filter": "FALSE", "mask": []}

decision := {"allow": true, "row_filter": rf, "mask": m} if {
    input.action in {"select", "read"}
    some role in input.subject.roles
    role in data.role_allowlist[input.resource.schema]    # data doc from bundle
    rf := region_filter
    m  := masked_pii
}

region_filter := "TRUE" if input.subject.purpose == "fraud"
region_filter := sprintf("region = '%s'", [input.subject.region]) if input.subject.purpose != "fraud"

masked_pii := [c | some c in input.resource.pii_columns; input.subject.clearance < 3]

# Each PEP runs an identical OPA sidecar: same bundle, host-local, fail-closed
# opa-sidecar.yaml (shared across Trino / data-API / BI-proxy pods)
bundles:
  dataplatform:
    service: s3
    resource: dataplatform/bundle.tar.gz
    polling: { min_delay_seconds: 20, max_delay_seconds: 30 }
decision_logs:
  service: kafka_sink
  reporting: { min_delay_seconds: 5, max_delay_seconds: 10 }
# PEP code calls http://localhost:8181 with a 50ms timeout and FAILS CLOSED on error.

Step-by-step trace.

Concern	Mechanism	Result
One policy truth	git → bundle → all sidecars	every PEP enforces the same Rego
Sub-10 ms decisions	host-local OPA sidecar, in-process eval	no network hop on the hot path
No bypass	each engine's PEP is the only credentialed path	Trino/API/BI all must ask OPA
Failure posture	50 ms timeout, fail-closed	PDP down on sensitive data → deny
Audit	decision logs → Kafka → warehouse	queryable "who + why + when"
Rollout	publish bundle	~30 s to all PEPs, no redeploy

After deployment, three very different engines — Trino, a Python data API, and a BI proxy — all enforce one shared Rego policy via identical host-local OPA sidecars: decisions are sub-10 ms because evaluation is in-process, no engine can reach the warehouse without going through its PEP, sensitive-data decisions fail closed if a sidecar is unhealthy, and every decision streams to a central governance.decision_log for audit. Shipping a policy change is publishing a bundle.

Output:

Metric	Value
Policy sources of truth	1 (git bundle)
Hot-path decision latency	< 10 ms (host-local)
Enforcement points	3 (Trino, data-API, BI) — same policy
Failure posture	fail-closed on sensitive tables
Policy rollout time	~30 s (bundle poll)
Audit answer	SQL over decision logs

Why this works — concept by concept:

• PDP/PEP separation — OPA (PDP) decides; each engine's plugin/middleware (PEP) enforces. Because the decision is externalised, three heterogeneous engines share one policy instead of re-implementing (and drifting on) the rules three times.
• One bundle, many sidecars — a single git-built bundle is pulled by every OPA sidecar, so "the policy" is unambiguous and versioned. Distribution is decoupled from deployment: publishing a bundle updates all PEPs in ~30 s.
• Host-local evaluation — running OPA as a sidecar and evaluating in-process removes the network hop, delivering sub-10 ms decisions on the hot path and eliminating a shared-service outage as a single point of failure.
• Fail-closed posture — a tight timeout plus deny-on-error means a sick PDP degrades to no access on sensitive data, never to open access. The failure mode is chosen deliberately, not discovered in an incident.
• Centralized decision logs — every PEP streams decisions to one sink, producing the queryable audit trail (who, what, why, which policy version) that satisfies SOC 2 / GDPR — the evidence RBAC grant dumps structurally cannot provide.
• Cost — one OPA sidecar per service pod (a few MB of memory, sub-ms eval), a bundle pipeline, and a decision-log sink. Compared to per-service bespoke authorization code, the cost is the sidecar footprint; the payoff is one tested policy, sub-10 ms O(1) decisions, and audit-by-query.

Design
Topic — design
Design problems on policy-engine and PDP/PEP architecture

Practice →

JSON Topic — json JSON decision-request and policy-config problems

Practice →

---

5. Immuta and Privacera — managed governance and hybrid RBAC/ABAC

Immuta and Privacera make data access governance operational — tag-driven, attribute-based policy that compiles to native warehouse controls

The mental model in one line: Immuta and Privacera are managed data access governance platforms that let you author policy once — as tags plus attribute rules in plain language — and have the platform compile it down to native warehouse controls (Snowflake row-access and masking policies, Databricks Unity Catalog, BigQuery), so data teams get ABAC-grade fine-grained access control, dynamic masking, and centralised audit without hand-rolling OPA and a query gateway. They occupy the layer above raw RBAC and DIY policy engines: the coarse warehouse roles stay (RBAC floor), while the platform delivers the attribute-driven, tag-based last mile and the audit trail — the hybrid model in a box.

What the managed platforms add over DIY RBAC/OPA.

• Tag-driven policy. You classify columns/tables with tags (PII, PHI, region:EU) — often auto-discovered by the platform's sensitive-data detection — and write policy against the tags, not table names. One "mask everything tagged PII" policy covers every current and future PII column across the whole estate. This is the scale multiplier data teams want.
• Native enforcement (no proxy). Instead of routing every query through a gateway, the platform compiles your policy into the warehouse's own controls — Snowflake ROW ACCESS POLICY / MASKING POLICY, Unity Catalog row/column masks. Enforcement happens inside the warehouse on the user's normal connection, so there is no gateway to scale or bypass and no query-path change for consumers.
• Attribute / purpose-based access. Policies read subject attributes from the IdP (department, clearance) and support purpose-based access — users acknowledge a purpose ("fraud investigation") to unlock data, and the acknowledgement is logged. This is ABAC and consent captured as product features.
• Centralised audit and lineage. Every policy, every access, and (in Immuta's model) every purpose acknowledgement is logged centrally, with reports built for GDPR/HIPAA/SOC 2. The "who could see what, and why" question is a dashboard, not a forensic project.

Immuta vs Privacera — same goal, different heritage.

• Immuta. Attribute-/tag-first from the start. Policies are authored in plain language ("mask columns tagged PII for users not in the fraud group"), it leans hard into ABAC and purpose-based access, and it targets cloud warehouses/lakehouses (Snowflake, Databricks, Starburst) with native policy compilation. Strong sensitive-data discovery and a governance-team-friendly UI.
• Privacera. Grew out of the Apache Ranger ecosystem (the same lineage as Hadoop/Ranger access control), so it brings broad connector reach across many engines and a Ranger-style policy model, plus discovery/classification and encryption features. Often chosen where the estate is heterogeneous (many engines, on-prem + cloud) and a Ranger heritage is an asset.
• The shared idea. Both decouple policy authoring (central, tag + attribute driven) from enforcement (native, per-engine). Both give you the RBAC-floor + ABAC-last-mile hybrid without you building a PEP/PDP yourself. The choice is estate shape and team preference, not a difference in the core model.

The three failure modes senior engineers pre-empt.

• Tagging quality is the foundation. Tag-driven policy is only as good as the tags. A mis-tagged (or untagged) PII column is unprotected. Mitigation: combine automated sensitive-data discovery with a human review workflow, and default-deny untagged sensitive schemas until classified.
• Native-compilation limits. The platform can only enforce what the target warehouse's native controls support; an exotic rule may not compile, or may compile to something with a performance cost (a heavy row-access predicate). Mitigation: test compiled policies for both correctness and query performance, and know each engine's masking/row-policy limits.
• Lock-in and cost. Managed governance is a paid platform in the query path of your most sensitive data — a strategic dependency. Mitigation: keep the authoring model (tags + attribute rules) portable in concept, keep the coarse RBAC floor in the warehouse (not the vendor), and evaluate build-vs-buy against OPA honestly for your scale.

Common interview probes on managed governance.

• "Why not just build OPA + a gateway?" — tag-driven policy, native enforcement, and audit out of the box; build-vs-buy at your scale.
• "What's tag-based policy and why does it scale?" — policy against tags covers all current/future tagged columns; one rule, whole estate.
• "Immuta vs Privacera?" — attribute/tag-first cloud-native vs Ranger-heritage broad-connector; estate shape decides.
• "What breaks tag-driven governance?" — bad tagging; an untagged PII column is unprotected.

Worked example — one tag-driven masking policy for the whole estate

Detailed explanation. The signature managed-governance move is a single policy that protects every PII column everywhere by targeting a tag, not a table. Walk through authoring it (conceptually, as Immuta-style plain language) and the native Snowflake masking policy it compiles to — so you see both the authoring and the enforcement layer.

• Classify. Columns are tagged PII (auto-discovery + review).
• Author. One policy: "mask columns tagged PII unless the user's group is fraud."
• Compile. The platform generates and attaches a Snowflake MASKING POLICY to every PII-tagged column.
• Scale. A new PII column, once tagged, is protected automatically — no new policy.

Question. Show the tag-driven policy intent and the native masking policy it compiles to, and explain why one policy covers the whole estate.

Input.

Element	Value
Tag	PII
Condition	mask unless group = fraud
Target engine	Snowflake
Scope	every PII-tagged column, all schemas

Code.

# Immuta-style plain-language policy (authored ONCE, centrally)
Policy: "Global PII masking"
  For columns tagged  PII
  Mask  using  hashing
  Except for users in group  fraud_investigators
  Applies to  all data sources

-- What the platform COMPILES and attaches natively in Snowflake.
-- The governance tool generates this and binds it to EVERY PII-tagged column.
CREATE OR REPLACE MASKING POLICY governance.mask_pii AS (val STRING) RETURNS STRING ->
    CASE
        WHEN IS_ROLE_IN_SESSION('FRAUD_INVESTIGATORS') THEN val    -- attribute-driven exception
        ELSE SHA2(val, 256)                                        -- irreversible hash mask
    END;

-- Bound automatically to each tagged column (illustrative — the tool does this for all):
ALTER TABLE analytics.orders     MODIFY COLUMN ssn   SET MASKING POLICY governance.mask_pii;
ALTER TABLE analytics.customers  MODIFY COLUMN email SET MASKING POLICY governance.mask_pii;
ALTER TABLE hr.employees         MODIFY COLUMN ssn   SET MASKING POLICY governance.mask_pii;
-- ... and every future column tagged PII, with no new policy authored.

Step-by-step explanation.

1. Classification comes first: columns across every schema are tagged PII, ideally by the platform's automated sensitive-data discovery with a human review gate. The tag — not the table name — is the unit policy targets.
2. The policy is authored once, centrally, in plain language against the tag: "mask columns tagged PII except for the fraud group." A governance team member writes this without touching SQL, and it is the single source of truth for PII masking.
3. The platform compiles that intent into a native Snowflake MASKING POLICY and binds it to every PII-tagged column automatically. Enforcement is native — it runs inside Snowflake on the user's normal connection, so there is no gateway in the path and nothing for consumers to change.
4. The attribute-driven exception (IS_ROLE_IN_SESSION('FRAUD_INVESTIGATORS')) is the ABAC element: the mask lifts based on a subject attribute (group membership), evaluated per session. Coarse role membership still comes from the RBAC floor — the hybrid in action.
5. The scale win: when someone adds a new column and it gets tagged PII, the existing policy protects it with no new authoring. One policy covers the entire current and future estate — the additive scaling ABAC promises, delivered as a product feature.

Output.

User group	Column tagged PII	Sees
analyst	orders.ssn	SHA2(...) hash
fraud_investigators	orders.ssn	clear value
analyst	newly-tagged payments.card	SHA2(...) — auto-protected
analyst	untagged column	clear (not in scope)

Rule of thumb. Author policy against tags, not tables, and let the platform compile it to native warehouse controls. One "mask everything tagged PII" policy protects every current and future PII column across the estate — but it lives or dies on tagging quality, so pair it with automated discovery and a review workflow.

Worked example — the hybrid model: RBAC floor plus ABAC last mile

Detailed explanation. The mature production answer is neither pure RBAC nor pure ABAC — it is a hybrid: coarse RBAC roles in the warehouse for "which schema," managed attribute/tag policies for "which rows and cells." Walk through how the two layers combine for a single analyst request, so the division of labour is explicit.

• RBAC layer. Warehouse role grants schema/table access (coarse gate).
• ABAC layer. Managed platform applies a region row-filter and PII masking (fine gate).
• Both must pass. The role opens the door; the attribute policy decides which rows and cells.

Question. Trace a single SELECT by an EU analyst through both layers and show the effective result.

Input.

Layer	Rule
RBAC	fr_analyst → SELECT on analytics schema
ABAC row	rows where region = subject.region
ABAC column	mask PII unless group = fraud
Subject	EU analyst, group = analysts

Code.

-- The analyst runs a normal query on their normal connection (no gateway):
SELECT order_id, region, total_cents, ssn
FROM   analytics.orders;

-- LAYER 1 (RBAC): does fr_analyst have SELECT on analytics.orders?  YES -> proceed.
--   (a NO here would be a hard "insufficient privileges" error — coarse gate)

-- LAYER 2 (ABAC, compiled natively): Snowflake applies, transparently:
--   * ROW ACCESS POLICY  -> WHERE region = <subject region 'EU'>
--   * MASKING POLICY     -> ssn hashed unless session role in FRAUD_INVESTIGATORS

-- Effective query Snowflake executes for THIS EU analyst:
SELECT order_id,
       region,
       total_cents,
       SHA2(ssn, 256) AS ssn          -- column mask (ABAC)
FROM   analytics.orders
WHERE  region = 'EU';                  -- row filter (ABAC)

Step-by-step explanation.

1. The analyst issues an ordinary query on their ordinary warehouse connection — no gateway, no rewrite on their side. Both layers are enforced natively inside Snowflake, which is why the hybrid is transparent to consumers.
2. Layer 1 is the RBAC floor: does fr_analyst hold SELECT on analytics.orders? This is the coarse gate — if the role lacks the grant, the query fails with a privileges error before any row is considered. RBAC answers "which objects."
3. Layer 2 is the ABAC last mile, compiled by the governance platform into a native row-access policy and masking policy. Snowflake transparently appends WHERE region = 'EU' (the analyst's region attribute) and hashes ssn (PII tag, no fraud membership).
4. Both layers must pass and they answer different questions: RBAC decides whether the analyst can touch the table; ABAC decides which rows and which cells within it. Neither layer alone is sufficient — the role without the filter over-shares across regions; the filter without the role has no coarse gate.
5. The result is least-privilege at two granularities from one clean design: a small, stable set of roles (auditable, no explosion) plus a couple of tag/attribute policies (fine-grained, estate-wide). This is the "RBAC for coarse, ABAC for fine" hybrid every senior answer converges on.

Output.

Requester	RBAC gate	Row filter	Column mask	Effective view
EU analyst	pass	region='EU'	ssn hashed	EU rows, ssn masked
US analyst	pass	region='US'	ssn hashed	US rows, ssn masked
fraud investigator	pass	none	none	all rows, ssn clear
finance-only user	fail	—	—	privileges error

Rule of thumb. Keep RBAC as the coarse floor (few, stable, function-named roles for "which schema") and put every condition — region, PII, purpose — in tag/attribute policies for "which rows and cells." The role opens the door; the attribute policy furnishes the room. This hybrid is the production-grade answer to RBAC vs ABAC.

Senior interview question on managed governance and build-vs-buy

A senior interviewer might ask: "Your company runs Snowflake and Databricks, has GDPR data-residency and HIPAA PII requirements, a small data-governance team, and no appetite to operate a bespoke OPA + query-gateway fleet. Walk me through how you'd stand up data access governance with a managed platform like Immuta or Privacera, how the hybrid RBAC/ABAC model maps onto it, and how you'd defend the build-vs-buy decision."

Solution Using tag-driven managed governance over an RBAC floor, with native enforcement and central audit

Design: RBAC floor (warehouse) + managed ABAC (Immuta/Privacera) + native enforcement
======================================================================================

1. RBAC FLOOR (stays in Snowflake/Databricks — vendor-neutral)
   - fr_analyst / fr_engineer / fr_platform  (functional roles, coarse schema access)

2. CLASSIFY (managed platform)
   - auto-discovery tags columns: PII, PHI, region:EU/US, sensitivity
   - human review gate; untagged sensitive schemas default-deny

3. AUTHOR (once, central, against tags + attributes)
   - "mask columns tagged PII/PHI unless group in {fraud, care_team}"
   - "rows visible where subject.region == row region:tag"  (residency)
   - purpose-based unlock for fraud/care, acknowledgement logged

4. ENFORCE (native, per engine — no gateway)
   - Snowflake:  compiled ROW ACCESS + MASKING policies
   - Databricks: Unity Catalog row filters + column masks

5. AUDIT (central)
   - every access + purpose acknowledgement logged; GDPR/HIPAA reports

-- The RBAC floor you keep in the warehouse (portable, not vendor-locked)
CREATE ROLE fr_analyst;
GRANT USAGE ON DATABASE prod TO ROLE fr_analyst;
GRANT USAGE ON SCHEMA prod.analytics TO ROLE fr_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA prod.analytics TO ROLE fr_analyst;
-- NOTE: no region/PII/purpose roles here — those are ABAC, authored in the platform.

# Build-vs-buy defense (say this out loud in the interview)
BUY (Immuta/Privacera) wins here because:
  - small governance team, no SRE budget for an OPA + gateway fleet
  - native enforcement = no query-path proxy to scale or bypass
  - GDPR/HIPAA reporting + purpose-based access are out-of-box
  - tag-driven policy scales to Snowflake + Databricks from one authoring plane
BUILD (OPA + gateway) would win if:
  - highly custom logic, extreme scale, strong platform team, cost at volume
  - need to govern many non-warehouse services (APIs) uniformly
KEEP PORTABLE regardless:
  - RBAC floor stays in the warehouse (not the vendor)
  - authoring model = tags + attributes (re-expressible in OPA if we migrate)

Step-by-step trace.

Layer	Where it lives	Answers
RBAC floor	warehouse roles	which schema/table (coarse)
Classification	managed platform + discovery	what is sensitive (tags)
ABAC authoring	managed platform (central)	which rows/cells, which purpose
Enforcement	native in Snowflake/Databricks	applied on the user's own connection
Audit	managed platform, central	who + why + when (GDPR/HIPAA)
Portability	RBAC + tag/attr model	migratable to OPA if needed

After stand-up, coarse access is a handful of warehouse roles (vendor-neutral), the managed platform classifies sensitive columns and enforces residency + PII masking natively in both Snowflake and Databricks from one central authoring plane, purpose-based unlocks and every access are logged for GDPR/HIPAA reporting, and the build-vs-buy decision is defensible on team size, native enforcement, and out-of-box compliance — with the RBAC floor and tag/attribute model kept portable in case the calculus changes.

Output:

Metric	DIY OPA + gateway	Managed (Immuta/Privacera)
Team to operate	platform + SRE	small governance team
Enforcement	query gateway (build/scale)	native, no proxy
Multi-engine reach	build per engine	Snowflake + Databricks built-in
Compliance reports	build	out-of-box GDPR/HIPAA
Tagging discovery	build	included
Portability risk	low (own it)	mitigated by portable RBAC + tags

Why this works — concept by concept:

• RBAC floor kept in the warehouse — coarse, function-named roles live in Snowflake/Databricks, not the vendor. This keeps the base layer portable and vendor-neutral, so a future migration off the managed platform doesn't touch coarse access.
• Tag-driven classification — automated discovery plus human review tags columns PII/PHI/region, and policy targets the tag. One rule protects every current and future column carrying the tag across the whole estate — the additive-scale property, delivered without code.
• Native enforcement, no proxy — the platform compiles policy into Snowflake row-access/masking and Databricks Unity Catalog controls, so enforcement runs inside the warehouse on the user's normal connection. There is no gateway to scale, no query-path change, and no bypassable proxy.
• ABAC + purpose-based access — masking lifts and rows appear based on subject attributes (group, region) and declared purpose (fraud, care-team), with acknowledgements logged. This is context-aware authorization and consent as product features, not custom code.
• Central audit — one place logs every access and purpose acknowledgement and produces GDPR/HIPAA/SOC 2 reports, turning "who could see what, and why" into a dashboard rather than a forensic project.
• Cost — a paid platform in the path of sensitive data, traded against building and operating an OPA + gateway fleet. For a small team on Snowflake + Databricks with compliance deadlines, buy wins on time-to-value; the portable RBAC floor and tag/attribute model cap the lock-in. O(tags) policies, O(roles) coarse grants — additive, not combinatorial, either way.

ETL
Topic — etl
ETL problems on governed pipelines and data classification

Practice →

Validation
Topic — data-validation
Data-validation problems on tagging and policy checks

Practice →

---

Cheat sheet — RBAC vs ABAC and policy engine recipes

• Which model when. RBAC is the 2026 floor in every warehouse for coarse, object-level, stable access ("the analytics team reads the analytics schema") — few, function-named roles. ABAC is the fine-grained ceiling for conditional access — data residency, PII masking, purpose-based, need-to-know — expressed as one rule over subject/resource/action/environment attributes. A policy engine (OPA) or managed platform (Immuta/Privacera) is how ABAC is enforced at scale. The production answer is almost always the hybrid: RBAC for the door, ABAC for the room.
• The role-explosion tell. Any role name that encodes a condition (_eu, _pii_masked, _fraud, _ro) is role explosion in progress. Roles should name functions (analyst, engineer, admin), never conditions. When you catch a condition in a role name, move it to an attribute and delete the combinatorial roles. RBAC grows multiplicatively (teams × regions × levels × purposes); ABAC grows additively (one rule per dimension).
• RBAC two-layer hierarchy template. Separate access roles (hold object grants, one per scope: ar_analytics_read) from functional roles (assigned to users, inherit access roles: fr_analyst). Users get functional roles only. Keep hierarchies shallow (2–3 levels) and acyclic. Use GRANT SELECT ON FUTURE TABLES for least-surprise on non-sensitive schemas; require explicit grants on sensitive ones. Roles-as-code (Terraform/dbt), never click-ops.
• ABAC attribute vocabulary. Every rule is a function of four bags: subject (roles, region, clearance, department — from the IdP), resource (table, tag PII/PHI, region tag, sensitivity — from the catalog), action (select/write/export), environment (time, IP, purpose, MFA freshness). Default-deny; allow explicitly. Return obligations: a row_filter (residency) and a mask set (column redaction), applied by the enforcement point.
• OPA Rego policy skeleton. package ...; default decision := {"allow": false, "row_filter": "FALSE", "mask": []}; one decision rule that gates on action + role, computes region_filter (with a fraud/purpose override) and masked_columns over input.resource.pii_columns. Ship as a bundle (git → tarball → object store → OPA poll every ~30 s). Stream decision logs to a warehouse table. Unit-test against subject/resource fixtures; dry-run (log-only) before enforcing.
• PDP/PEP/PAP/PIP reference. PEP = enforcement chokepoint (query gateway / engine plugin / sidecar) that intercepts and applies the verdict; PDP = OPA, evaluates policy + data, returns the decision; PAP = git repo + bundle pipeline (author/distribute); PIP = IdP + data catalog (attribute source). Run OPA host-local (sidecar) for sub-10 ms hot-path decisions; fail-closed on sensitive data; make the PEP the only credentialed path so it can't be bypassed.
• Snowflake native enforcement. Row-level: CREATE ROW ACCESS POLICY ... RETURNS BOOLEAN -> EXISTS(SELECT 1 FROM user_attributes WHERE user=CURRENT_USER() AND region=row_region) then ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (region). Column-level: CREATE MASKING POLICY ... CASE WHEN IS_ROLE_IN_SESSION('FRAUD') THEN val ELSE '***' END then ALTER TABLE ... MODIFY COLUMN ssn SET MASKING POLICY .... Postgres equivalent: ENABLE ROW LEVEL SECURITY + CREATE POLICY ... USING (...).
• Managed platform (Immuta/Privacera) pattern. Classify columns with tags (auto-discovery + human review); author policy against tags, not tables ("mask everything tagged PII unless group=fraud"); the platform compiles it to native Snowflake/Databricks controls (no gateway); purpose-based unlock with logged acknowledgement; central GDPR/HIPAA/SOC 2 audit. Immuta = attribute/tag-first, cloud-warehouse-native; Privacera = Apache Ranger heritage, broad multi-engine connector reach.
• Auditability. RBAC = snapshot the grants (easy) but the why is lost. ABAC/policy-engine = decision logs answer "who could access what, under which policy version, and why," queryable per subject/resource/quarter — the evidence SOC 2/GDPR actually require. Monitor bundle age/activation as an SLO; a stale bundle is a silently wrong policy.
• Failure posture. Decide fail-closed vs fail-open explicitly, per data sensitivity: sensitive data fails closed (PDP down → deny). Source attributes from a fresh PIP (IdP/catalog), never a hand-maintained spreadsheet — stale attributes = wrong decisions. Make the PEP unbypassable (no direct end-user warehouse credentials; all paths route through enforcement). Test policies before enforcing; dry-run + diff against the old grants.
• Build-vs-buy. Build OPA + gateway when you have a strong platform team, extreme scale/custom logic, or many non-warehouse services to govern uniformly. Buy Immuta/Privacera when you have a small governance team, compliance deadlines, a Snowflake/Databricks estate, and want native enforcement + out-of-box GDPR/HIPAA reporting. Either way keep the RBAC floor in the warehouse and the authoring model portable (tags + attributes) to cap lock-in.
• Decision matrix (memorise). Granularity: RBAC object-level, ABAC row+cell. Scale: RBAC multiplicative, ABAC additive. Context: RBAC static, ABAC dynamic. Audit: RBAC snapshot-easy/why-lost, ABAC decision-logs. Enforcement: RBAC native grants, ABAC via PEP or native-compiled policies. Print this on a sticky note; use it in every interview.

Frequently asked questions

What is RBAC vs ABAC in one sentence?

role-based access control (RBAC) grants privileges to named roles and assigns those roles to users, so access is decided by "does the user hold a role with this privilege" — coarse, static, and object-level. attribute-based access control (ABAC) decides access by evaluating a rule over the attributes of the subject, resource, action, and environment at request time, so a single rule ("allow if subject.region == resource.region") replaces what RBAC would need a role-per-combination to express. In one line: RBAC answers who you are (your role); ABAC answers whether this specific request, in this context, should be allowed. The two are not rivals in practice — the mature pattern is RBAC for coarse access and ABAC for the fine-grained, conditional last mile, enforced by a policy engine or a managed governance platform.

When should I use RBAC vs ABAC?

Use RBAC when access is coarse, stable, and object-level — "the analytics team reads the analytics schema," "engineers can write to staging." Roles are cheap to reason about and trivially auditable when they name functions, and every warehouse ships RBAC natively, so it is your floor. Reach for ABAC the moment requirements become conditional: data residency ("analysts see only their region"), PII masking by clearance or purpose, time/location/purpose restrictions, or any rule where the role count would grow multiplicatively (regions × sensitivity × purposes). The tell is simple — if a role name is starting to encode a condition (role_orders_eu_masked), that condition belongs in an attribute, and you have crossed into ABAC territory. Most real platforms run a hybrid: a small set of RBAC roles for the coarse gate plus attribute/tag policies for fine-grained access control.

What is a policy engine and what is OPA?

A policy engine externalises authorization decisions so the logic lives in one tested, versioned place instead of being re-implemented across every service. Open Policy Agent (OPA) is the reference open-source policy engine: you write policy as code in its Rego language, load it (plus data) into OPA, and OPA becomes the Policy Decision Point (PDP) — given a request input (subject, resource, action, environment), it returns a decision. Crucially, OPA decides but does not enforce: a Policy Enforcement Point (PEP) — a query gateway, a database plugin, or a sidecar — intercepts each request, asks OPA "allow?", and applies the verdict (deny, or allow with a row filter and column masks). This PDP/PEP separation is the whole idea: one policy, evaluated consistently, enforced at every access path, with every decision logged for audit. OPA runs host-local (sidecar) for sub-10 ms decisions and pulls policy as bundles so a policy change is a publish, not a service redeploy.

What is policy as code?

policy as code means your authorization rules live as versioned, testable text (e.g. Rego for OPA, or a managed platform's declarative policies) in a git repository, rather than as clicks in a UI or hand-maintained grant lists. The benefits are the same as for application code: changes are pull requests that get reviewed and diffed, the "why" is self-documenting in the rule itself, you can unit-test a policy against known subject/resource fixtures, you can run a new policy in dry-run (log-only) and compare its decisions against the old one before enforcing, and you can roll back by reverting a commit. Because ABAC decisions are computed rather than looked up, this testing discipline is not optional — it is the only way to know a policy change doesn't silently over- or under-grant. Policy as code is what turns ABAC from "a clever idea" into an operable, auditable system.

Immuta vs Privacera — what's the difference?

Both are managed data access governance platforms that decouple policy authoring (central, tag- and attribute-driven) from enforcement (compiled natively into each engine's own controls), giving you ABAC-grade masking, row filtering, and audit without hand-rolling OPA and a gateway. Immuta is attribute-/tag-first and cloud-warehouse-native: you author policies in plain language against tags ("mask columns tagged PII unless the user is in the fraud group"), it leans into purpose-based access and sensitive-data discovery, and it targets Snowflake, Databricks, and Starburst with native policy compilation. Privacera grew out of the Apache Ranger ecosystem, so it brings a Ranger-style model and broad connector reach across many engines (including on-prem/Hadoop lineage), plus discovery, classification, and encryption features. The core model is the same; the choice usually comes down to estate shape — a cloud-lakehouse estate that wants attribute/tag-first authoring leans Immuta, while a heterogeneous multi-engine estate that values Ranger heritage and connector breadth leans Privacera.

Can you combine RBAC and ABAC?

Yes — combining them is the recommended production pattern, not a compromise. The hybrid keeps RBAC as the coarse floor (a small set of stable, function-named roles that grant schema/table access — "which objects can this user touch") and layers ABAC as the fine-grained ceiling (attribute and tag policies that decide "which rows and which cells within those objects"). A single request passes through both: the role opens the door (RBAC gate), then the attribute policy applies a region row-filter and PII masking (ABAC last mile). This is exactly what managed platforms like Immuta and Privacera deliver — coarse warehouse roles plus tag/attribute policies compiled to native row-access and masking controls — and what a DIY OPA setup expresses in one Rego policy that gates on role and computes obligations. The hybrid gives you RBAC's auditability and simplicity for the 80% of access that is coarse, and ABAC's granularity and context-awareness for the 20% that is conditional — without the role explosion that pure RBAC would suffer or the everything-is-a-rule complexity of pure ABAC.

Practice on PipeCode

• Drill the SQL practice library → for the roles, grants, row-level-security, and masking problems that show up in access-control interviews.
• Rehearse system trade-offs on the design practice library → for RBAC-vs-ABAC modeling, PDP/PEP architecture, and least-privilege design.
• Sharpen the pipeline-governance angle with the ETL practice library → for governed pipelines, data classification, and tag-driven policy scenarios.
• Work the JSON practice library → for policy-document and attribute-parsing reps that mirror Rego decision requests.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the RBAC-vs-ABAC decision matrix against real graded inputs.

Lock in RBAC vs ABAC muscle memory

Docs explain the models. PipeCode drills explain the decision — when RBAC's role count explodes, when ABAC's attributes save you, when a policy engine like OPA belongs on the hot path, and when a managed platform like Immuta or Privacera is the honest build-vs-buy call. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →