Access Control And Row Level Security Concepts

Why this matters

As a BI Analyst, you often publish dashboards and datasets to mixed audiences: executives, managers, regional teams, and sometimes external partners. Access control and Row-Level Security (RLS) prevent sensitive data from leaking while letting people see exactly what they need. You will:

• Define who can access which datasets, fields, and metrics.
• Apply dynamic filters so users only see rows for their region, department, tenant, or clients.
• Protect PII and financials via column masking and object permissions.
• Ship dashboards confidently, knowing scheduled reports and exports are safe.

Concept explained simply

Access control answers: who can see what. The semantic layer is where you describe data once (tables, joins, measures) and apply rules so the same model serves different users safely.

• RBAC (Role-Based Access Control): permissions assigned to roles (e.g., Analyst, Manager), and users get roles.
• ABAC (Attribute-Based Access Control): rules use user attributes (e.g., department_id, region) to decide access.
• RLS (Row-Level Security): filters rows by user/role attributes.
• CLS (Column-Level Security): hides or masks sensitive columns (e.g., email, salary).
• Object permissions: who can view/explore models, datasets, or reports.

Key building blocks

• Identities and groups/roles: single source of truth for who a user is.
• User attributes: department_id, region, tenant_id, seniority, employment_status.
• Objects: semantic models, explores, datasets, dashboards, queries, extracts.
• Policies: default deny, explicit allow; combine RBAC with ABAC for flexibility.
• RLS application: filters should apply at query time and to cached/extracted data.
• CLS and masking: hide or hash PII when full removal isn’t possible.
• Join safety: ensure tenant/department filters are enforced on all joined tables.
• Auditing: log who saw what; review policy changes.

Worked examples

Example 1: Department-based RLS on Sales

Goal: Managers see only their department’s sales; Finance sees all; Analysts can explore but cannot view employee PII.

Roles:
- finance_admin: all data
- manager: rows where department_id = @user.department_id
- analyst: same rows as manager if applicable, but PII columns masked

RLS rule (fact_sales):
CASE
  WHEN 'finance_admin' IN @user.roles THEN TRUE
  WHEN 'manager' IN @user.roles THEN fact_sales.department_id = @user.department_id
  WHEN 'analyst' IN @user.roles THEN fact_sales.department_id = @user.department_id
  ELSE FALSE
END

CLS masking (example):
email_masked = CASE WHEN 'finance_admin' IN @user.roles THEN email ELSE NULL END
salary_masked = CASE WHEN 'finance_admin' IN @user.roles THEN salary ELSE NULL END

Example 2: Multi-tenant SaaS dataset

Goal: Each customer (tenant) sees only their data. Avoid leakage via joins and caches.

Core principle: propagate tenant_id to every join.

JOIN pattern:
FROM fact_events f
JOIN dim_users d
  ON f.user_id = d.user_id
 AND d.tenant_id = @user.tenant_id
WHERE f.tenant_id = @user.tenant_id

Also apply tenant_id predicate to any aggregate tables or materializations.
Cache: partition by tenant_id so one tenant’s cached results aren’t reused by others.

Example 3: Territory lists via a mapping table

Goal: Reps can see multiple territories assigned to them in a bridge table.

Tables:
- fact_opportunities(territory_id, amount, ...)
- user_territory(user_id, territory_id)

RLS predicate:
EXISTS (
  SELECT 1
  FROM user_territory ut
  WHERE ut.user_id = @user.id
    AND ut.territory_id = fact_opportunities.territory_id
)

Who this is for

• BI Analysts designing semantic models and dashboards for varied audiences.
• Analytics engineers collaborating on governed datasets.
• Team leads responsible for compliant reporting.

Prerequisites

• Comfort with SQL joins, filters, and aggregates.
• Basic understanding of your BI tool’s modeling layer.
• Familiarity with roles/groups in your identity provider.

Learning path

1. Review your org’s roles and user attributes (department, region, tenant).
2. Map datasets to sensitivity levels; mark PII columns for masking.
3. Draft default-deny policies; layer explicit allows.
4. Implement RLS for one dataset; add CLS for sensitive columns.
5. Test with impersonation accounts; verify joins enforce filters.
6. Document rules and add monitoring/audit checks.

Hands-on: Exercises

Complete these, then take the quick test. Tip: write policies in plain text first, then translate into your tool’s syntax.

Common mistakes and self-checks

• Mistake: Allow-by-default. Fix: Start with default deny and list explicit allows.
• Mistake: Filters on fact but not on dimensions. Self-check: Inspect every JOIN has tenant/region predicate.
• Mistake: Masking only in the UI. Self-check: Enforce masking in the semantic layer or SQL views.
• Mistake: Ignoring caches/exports. Self-check: Verify RLS applies to extracts, scheduled emails, and downloads.
• Mistake: Static groups for dynamic org changes. Self-check: Use user attributes (e.g., department_id) from your IdP.

Practical projects

• Harden one production dashboard: add default-deny, implement RLS and CLS, and publish a short policy doc.
• Build a multi-tenant analytics sandbox with seeded users and impersonation tests.
• Create a policy regression suite: a set of queries that validate no leakage for common roles.

Quick test: how it works

The quick test is available to everyone for free. Only logged-in users get their progress saved.

Next steps

• Roll out RLS to one high-impact dataset; measure support tickets and incidents before/after.
• Add attribute sync from your identity provider to keep policies current.
• Schedule quarterly access reviews and policy audits.

Mini challenge

You support Global, Regional, and Store roles. Global sees all; Regional sees their region; Store sees only their store. Some analysts should see all rows but with PII masked. Draft a concise policy that covers RLS and CLS, and write three test cases that would catch misconfigured joins.