🔐 RBAC: Role-Based Access Control

RBAC is the most common access control model. You assign permissions to roles, then assign users to roles.

User: Alice
→
Role: Analyst
→
Permission: SELECT on analytics.*

RBAC in practice (Snowflake):

-- Create role hierarchy CREATE ROLE analyst; CREATE ROLE data_engineer; -- Grant permissions to roles GRANT SELECT ON analytics.* TO ROLE analyst; GRANT ALL ON raw.* TO ROLE data_engineer; -- Assign users to roles GRANT ROLE analyst TO USER alice; GRANT ROLE data_engineer TO USER bob;
💡 Interview Gold

"RBAC works well when access aligns with job functions. It's simple to audit — just list role memberships. But it struggles with cross-cutting concerns like 'analysts who are also in the EU region.'"

🏷️ ABAC: Attribute-Based Access Control

ABAC evaluates policies using attributes — user department, data sensitivity, time of day, location, etc.

User Attrs
dept=finance, region=EU
→
Policy Engine
IF dept=finance AND sensitivity≤3
→
ALLOW

RBAC vs ABAC — the comparison:

🔑 RBAC

  • ✅ Simple to implement
  • ✅ Easy to audit
  • ✅ Works for static org structures
  • ⚠️ Role explosion in complex orgs
  • ❌ Can't handle dynamic conditions
  • ❌ Cross-cutting access is awkward

🏷️ ABAC

  • ✅ Highly flexible and granular
  • ✅ Handles dynamic conditions
  • ✅ No role explosion
  • ⚠️ More complex to implement
  • ⚠️ Harder to audit and debug
  • ⚠️ Needs good attribute management
🎯 Interview Tip

"Most orgs use RBAC as the base with ABAC-style policies for edge cases. Example: RBAC grants the 'analyst' role SELECT access, but an ABAC policy further restricts PII columns to users with sensitivity_clearance >= 3."

📊 Row-Level & Column-Level Security

These are the granular controls that make multi-tenant analytics possible.

Row-Level Security (RLS)

Restricts which rows a user can see. Common in multi-tenant or regional setups.

-- Snowflake: Row access policy CREATE ROW ACCESS POLICY region_filter AS (region VARCHAR) RETURNS BOOLEAN -> region = CURRENT_USER_REGION() OR IS_ROLE_IN_SESSION('ADMIN'); -- Apply to table ALTER TABLE orders ADD ROW ACCESS POLICY region_filter ON (region);

Column-Level Security (CLS)

Restricts which columns are visible. Used to hide PII from non-privileged users.

Column
Analyst
Engineer
Admin
order_id
✅ Visible
✅ Visible
✅ Visible
amount
✅ Visible
✅ Visible
✅ Visible
customer_email
❌ Hidden
❌ Hidden
✅ Visible
ssn
❌ Hidden
❌ Hidden
✅ Visible
💡 Interview Gold

"Row-level security answers 'which records can you see?' Column-level answers 'which fields can you see?' Together they form a fine-grained access matrix essential for multi-tenant analytics and PII protection."

🎭 Dynamic Data Masking & Least Privilege

Dynamic masking shows different views to different users at query time. The stored data doesn't change.

🎭 Dynamic Masking

Admin sees: john@acme.com
Analyst sees: j***@***.com
Applied at query time, not stored.

🔒 Least Privilege

Every user gets the minimum access needed. Start with zero access, grant incrementally. Revoke when no longer needed.

⏰ Just-In-Time Access

Temporary elevated access with auto-expiration. Engineer requests prod access for 2 hours → auto-revoked after.

Multi-Tenant Access Patterns

🏢 Shared Tables + RLS

One table, tenant_id column, row access policy filters by tenant. Most common in SaaS platforms. Efficient but requires careful policy management.

📁 Separate Schemas

Each tenant gets own schema/database. Stronger isolation, simpler access. More overhead for cross-tenant analytics.

🔑 Secure Views

Create views that filter by tenant context. Users query views, never base tables. Good balance of simplicity and security.

🎯 Interview Tip

"When asked about multi-tenant access, discuss the tradeoff: shared tables + RLS is cost-efficient but complex; separate schemas is simpler but harder to manage at scale. Most mature platforms use shared tables with robust RLS."

Quiz: Test Yourself

Q1: A company has 200 roles because every team × region combination needs its own role. What's the fix?

Q2: US analysts should only see US orders. EU analysts should only see EU orders. Which control?

Q3: An analyst queries a customer table and sees "j***@***.com" for email. The admin sees "john@acme.com". What's happening?

Q4: An engineer needs production database access for a one-time investigation. Best practice?

Q5: A SaaS platform with 500 tenants needs data isolation. Most scalable approach?