Row Column Level Security Concepts

Why this matters

Data Platform Engineers must protect sensitive data while keeping analytics fast and self-serve. Row-Level Security (RLS) and Column-Level Security (CLS) let you:

• Run one shared table for many tenants while showing each tenant only their rows.
• Comply with regulations by restricting access by geography, department, or purpose.
• Hide or mask sensitive columns (e.g., PII, salaries) from most users.
• Enable analysts to explore data safely without creating separate copies.
• Reduce operational overhead by avoiding per-tenant schemas where possible.

Concept explained simply

RLS filters which rows a user can see, based on attributes like their role, tenant, or region. CLS controls what a user can see at the column level; it may fully hide a column or mask values for some users.

Mental model

• Think of a two-gate system: Gate 1 (RLS) lets only certain rows pass. Gate 2 (CLS) decides what each visible row reveals.
• Policies are just rules (predicates) evaluated at query time. If a row doesn’t satisfy the rule, it’s invisible.
• Role- or attribute-based: use RBAC (roles/permissions) and ABAC (user attributes like region) together.
• Typical evaluation: row filters are applied before column masking. Exact behavior can vary by engine, but this mental model is safe for design.

Key terms in 60 seconds

• RLS Policy: A row predicate that must be true for a row to be returned.
• CLS/Masking: A rule that hides or transforms sensitive columns.
• ABAC: Attribute-Based Access Control (e.g., user.region, user.tenant_id).
• RBAC: Role-Based Access Control (e.g., role=analyst, hr_admin).
• Session context: Runtime attributes set per connection/session (e.g., current_tenant_id).
• Secure view: A view that enforces policies and hides underlying details.
• Data classification: Labeling columns as PII/Restricted to attach policies consistently.

Worked examples

-- Table: orders(order_id, tenant_id, order_date, amount)
-- Session attribute set during login or connection setup:
--   current_tenant_id = <UUID>

-- Generic predicate idea (pseudo-SQL):
POLICY orders_rls AS
  USING (tenant_id = session.current_tenant_id);

-- Postgres-style sketch (conceptual):
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Snowflake-style sketch (conceptual):
CREATE ROW ACCESS POLICY tenant_isolation AS (tenant_id STRING)
RETURNS BOOLEAN -> tenant_id = CURRENT_CLIENT() : CURRENT_ROLE() : CURRENT_ACCOUNT() -- replace with your context
;
ALTER TABLE orders ADD ROW ACCESS POLICY tenant_isolation ON (tenant_id);

-- Table: customers(customer_id, region, signup_date, lifetime_value)
-- Session context (e.g., a comma-separated list):
--   allowed_regions = 'US,CA'

POLICY customers_region_rls AS
  USING (region IN session.allowed_regions);

-- Implementation tip: store allowed regions in a joinable table keyed by user_id
-- to avoid long string parsing, then filter via a semi-join:
--   WHERE customers.region IN (SELECT region FROM user_allowed_regions WHERE user_id = session.user_id)

-- Table: employees(emp_id, name, role, salary)
-- CLS idea: mask the salary unless user has role HR_ADMIN.

MASKING POLICY mask_salary AS (salary NUMBER)
RETURNS NUMBER -> CASE WHEN session.has_role('HR_ADMIN') THEN salary ELSE NULL END;

-- If your engine supports dynamic data masking, you can apply the policy on the column:
ALTER TABLE employees MODIFY COLUMN salary SET MASKING POLICY mask_salary;

-- RLS: region-limited rows (as in Example 2)
-- CLS: mask email unless role MARKETING_LEAD
MASKING POLICY mask_email AS (email STRING)
RETURNS STRING -> CASE WHEN session.has_role('MARKETING_LEAD') THEN email ELSE '***masked***' END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY mask_email;

-- Optional: use a secure view with k-anonymity-like guard:
CREATE SECURE VIEW mkt_customers AS
SELECT *
FROM customers
WHERE region IN (SELECT region FROM user_allowed_regions WHERE user_id = session.user_id)
WITH CHECK OPTION; -- conceptual: ensure predicates hold

-- Analysts should aggregate only when COUNT(>= 5) to reduce re-identification risk.

Design steps you can reuse

1. Classify data: Tag columns as Public, Internal, Sensitive (PII/PHI/Financial).
2. Define access model: Who should see which rows? Which columns? Use RBAC + ABAC.
3. Choose context: Decide how to pass user attributes (session parameters, JWT claims mapped to DB variables, groups/roles).
4. Write policies: Start deny-by-default; then add explicit allow predicates and masks.
5. Test realistic queries: JOINS, aggregates, BI dashboards, and ad-hoc filters.
6. Harden: Secure views, limit owner privileges for service accounts, and enable auditing.
7. Monitor performance: Index predicate columns, avoid non-sargable functions, and keep predicates simple.

Exercises

Do these tasks, then compare with the solutions. Everyone can access exercises and the test; only logged-in users will see saved progress.

• Exercise 1 (ex1): Tenant + salary masking. Implement RLS for tenant isolation and CLS to mask salary for non-HR users.
• Exercise 2 (ex2): Regional policy with performance. Implement RLS by region using a helper table and propose indexes.

Self-check checklist

• RLS predicates reference only columns and session attributes, not subqueries that break optimization.
• Default is effectively deny, then explicit allow.
• CLS masks reveal nothing sensitive to unauthorized roles.
• Policies preserved through views/joins in your tests.
• Predicate columns are indexed or partitioned where appropriate.

Common mistakes and how to self-check

Mini challenge

You have a single customer table used by Sales and Support:

• Sales should see only customers in their assigned territories and can view phone numbers but not emails.
• Support should see all customers but only masked payment tokens.

Who this is for

• Data Platform Engineers and DBAs designing shared analytics platforms.
• Analytics Engineers and BI Developers who need safe self-serve data.
• Security-minded Engineers implementing least-privilege access.

Prerequisites

• Comfortable with SQL and roles/permissions.
• Basic understanding of your data warehouse/database session context features.
• Data classification (know which columns are sensitive).

Learning path

• Start: Data classification and RBAC foundations.
• Then: RLS design with ABAC (session/user attributes).
• Next: CLS and dynamic masking patterns.
• After: Secure views, auditing, and performance tuning.
• Finally: Governance guardrails (change management, reviews, tests).

Practical projects

• Build a multi-tenant analytics mart: implement tenant RLS and test with two fake tenants.
• Create an HR dataset: mask PII and salaries for non-HR roles; add an aggregate-only secure view.
• Dashboard hardening: configure a BI connection using a low-privilege role and validate policies hold across filters and joins.

Next steps

• Extend policies to staging and derived tables so protections stay end-to-end.
• Add audit logs and alerts for policy denials or unusual access patterns.
• Review policies regularly as teams or regions change.

Progress and test info

The Quick Test below is available to everyone. Only logged-in users will have their test progress saved.