Row And Column Level Security Concepts

Why this matters

As a Data Architect, you must ensure the right people see the right slices of data—and nothing more. Row-level security (RLS) limits which records a user can read or write. Column-level security (CLS) controls which fields are visible or masked (e.g., hiding PII for most users). Together, they enable least-privilege access, reduce insider risk, and simplify compliance with standards like GDPR and HIPAA.

• Enable safe self-service analytics without copying data for each team.
• Support multi-tenant and multi-region products with a single logical model.
• Ship governed data products that BI tools and ML workflows can use safely.

Concept explained simply

- Row-level security: Think of a filter automatically applied to every query. If a user is in the Sales team, the system auto-filters to rows where department = 'Sales'.
- Column-level security: Think of a curtain covering sensitive columns unless the user has permission; they still see nonsensitive fields.

Mental model

Picture a large table as a building of rooms (rows). RLS is your keycard only opening rooms assigned to you. Each room contains locked drawers (columns). CLS is which drawers your keycard lets you open—or see as blank labels. You may enter some rooms (RLS) but not open certain drawers inside those rooms (CLS).

• RLS decides "which records".
• CLS decides "which fields of each record".
• Combined: First narrow rows, then limit fields within those rows.

Key building blocks

• User identity and attributes: user_id, roles, tenant_id, region, data domains.
• Entitlements store: tables mapping users/roles to allowed scopes (departments, tenants, products).
• Policies: expressions evaluating user context against data attributes.
• Enforcement points: database policies, views, stored procedures, or BI semantic layers.
• Auditability: log which policies were applied and when.

Worked examples

Example 1: RLS by department

Goal: Analysts only see employees in their allowed departments.

-- Conceptual policy (pseudo-SQL)
ALLOW SELECT FROM employees e
WHERE EXISTS (
  SELECT 1 FROM user_department_entitlements ude
  WHERE ude.user_id = SESSION_USER_ID()
    AND ude.department = e.department
)
OR SESSION_HAS_ROLE('hr_admin'); -- override role

Example 2: Column-level masking for PII

Goal: Hide email and phone unless the user has a PII role.

SELECT
  full_name,
  CASE WHEN SESSION_HAS_ROLE('pii_viewer') THEN email ELSE '***@***.com' END AS email,
  CASE WHEN SESSION_HAS_ROLE('pii_viewer') THEN phone ELSE '(***) ***-****' END AS phone
FROM employees_filtered_by_rls;

Example 3: Multi-tenant RLS + region constraint

Goal: SaaS app where each customer (tenant_id) only sees its data, plus a regional rule to keep EU data in-region.

ALLOW SELECT FROM orders o
WHERE o.tenant_id = SESSION_TENANT_ID()
  AND (SESSION_REGION() = 'EU' AND o.region = 'EU' OR SESSION_HAS_ROLE('global_ops'));

This prevents accidental cross-tenant leakage and supports data residency.

Example 4: Fact table RLS using dimension join

Goal: Limit access on sales_fact by department membership stored in a department dimension.

ALLOW SELECT FROM sales_fact f
WHERE EXISTS (
  SELECT 1
  FROM dim_department d
  JOIN user_department_entitlements ude
    ON ude.department_id = d.department_id
  WHERE d.department_id = f.department_id
    AND ude.user_id = SESSION_USER_ID()
);
-- Note: Some platforms implement this as a policy referencing a secure view on dim tables.

Design patterns that scale

• Central entitlements tables: one source of truth for user-to-scope mapping.
• Secure views or policies: expose only governed views to end users/BI tools.
• Session context: set user_id, roles, tenant_id at login; policies read from it.
• Default deny: If a user has no entitlements, return zero rows and masked columns.
• Test harness: include unit tests that simulate different users and assert policy results.

Exercises

Try these hands-on tasks. Solutions are available below each exercise.

Exercise 1: Department-scoped RLS

Create an RLS rule so a user only sees employees from departments they are entitled to. Include an override role for HR admins.

• Create sample tables: employees, user_department_entitlements.
• Insert data: at least Sales and Finance employees.
• Simulate users Ana (Sales) and Ravi (hr_admin).
• Write a policy/view that enforces the rule.
• Run test queries as each user and verify results.

-- Sample structure (pseudo-SQL)
CREATE TABLE employees(emp_id INT, full_name TEXT, department TEXT, email TEXT);
CREATE TABLE user_department_entitlements(user_id TEXT, department TEXT);

-- Policy via secure view
CREATE VIEW v_employees_secure AS
SELECT *
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM user_department_entitlements ude
  WHERE ude.user_id = SESSION_USER_ID() AND ude.department = e.department
)
OR SESSION_HAS_ROLE('hr_admin');

Exercise 2: Add CLS masking to PII

Extend Exercise 1: mask email unless the user has the pii_viewer role.

• Expose full_name always.
• Mask email with a generic pattern for non-PII viewers.
• Keep RLS filter from Exercise 1.
• Test as Ana (no PII role) and Priya (pii_viewer).

CREATE VIEW v_employees_secure_cls AS
SELECT
  full_name,
  CASE WHEN SESSION_HAS_ROLE('pii_viewer') THEN email ELSE '***@***.com' END AS email,
  department
FROM v_employees_secure; -- inherits RLS

Common mistakes (and self-check)

• Relying on BI tool filters only: Those are easy to bypass. Self-check: Run queries directly on the warehouse—policies must still apply.
• Forgetting default deny: Users without entitlements should see zero rows. Self-check: Test a user with no mappings; ensure empty results.
• Leaking via joins: A permissive dimension can re-expand rows. Self-check: Test policies using joined queries and aggregates.
• Masking after export: If masking happens only in the dashboard, extracts may leak PII. Self-check: Validate masking is enforced at the data source.
• Static snapshots that ignore new users: Policies must read entitlements at runtime. Self-check: Add a new user mapping; confirm instant effect.

Practical projects

1. Multi-tenant warehouse sandbox
   • Implement tenant_id-based RLS on 2–3 tables.
   • Add a pii_viewer role and mask two columns.
   • Create a test script that runs as three users and asserts counts.
2. Data product with governed view layer
   • Build secure views that apply RLS/CLS.
   • Expose them to a BI tool; confirm filters remain at the source.
3. Audit and alerting prototype
   • Log access events (user, table, policy, timestamp).
   • Flag attempts that return zero rows for expected-allowed users.

Who this is for

• Data Architects defining governance for
• Data Engineers implementing secure schemas and pipelines.
• Analytics Engineers building semantic layers with least privilege.

Prerequisites

• Comfort with SQL joins, views, and roles.
• Basic understanding of identity and access management.
• Awareness of PII and data classification levels.

Learning path

1. Data classification: identify which columns require masking.
2. Design entitlements model: users → roles/attributes → scopes.
3. Implement RLS policies on core tables.
4. Add CLS masking for sensitive columns.
5. Automate tests and auditing.

Quick test

Check your understanding with a short quiz. The test is available to everyone; only logged-in users get saved progress.

Next steps

• Generalize policies into reusable templates (department, tenant, geography).
• Add data quality checks to prevent policy breakage (e.g., missing tenant_id).
• Document examples for engineering and analytics teams.

Mini challenge

Your company serves two tenants (T1, T2) across EU and US. Sales managers should see only their tenant and their region. Finance admins see all rows but never raw emails unless they have pii_viewer. Sketch the RLS expression and the CLS CASE statement you would use. Verify it handles a user with no entitlements (should return zero rows).