Data Security Row Column Level

Why this skill matters for BI Developers

Row-Level Security (RLS) and Column-Level Security (CLS) allow you to show each user only the data they are allowed to see. Done well, they unlock secure self-service analytics, reduce the number of duplicated datasets, and help satisfy legal and compliance requirements (PII, finance, healthcare). Done poorly, they leak data or block business users. This guide shows you how to implement, test, and maintain RLS/CLS in a practical, tool-agnostic way with examples you can adapt to your BI stack.

What you will be able to do

• Design role-based access for business audiences and sensitive fields.
• Implement static and dynamic RLS using user-to-attribute mappings.
• Mask or hide sensitive columns (CLS) based on roles or purpose.
• Test security using real user scenarios and document expected visibility.
• Share reports safely without breaking security rules.

Who this is for

• BI Developers and Analytics Engineers integrating reports with governed data.
• Data Analysts who need to publish dashboards to mixed audiences.
• Team Leads and Data Stewards responsible for access governance.

Prerequisites

• Comfort with SQL joins and filters.
• Basic data modeling (facts, dimensions, relationships).
• Familiarity with at least one BI tool (e.g., Power BI, Tableau, Looker) helps.

Quick glossary

• RLS (Row-Level Security): Restrict which rows a user can see.
• CLS (Column-Level Security): Restrict which columns or values a user can see.
• Static security: Hard-coded filters for a role (e.g., Region = 'EMEA').
• Dynamic security: Filters depend on who is logged in, using a user-to-permission mapping table.
• Least privilege: Users get only what they need, nothing more.

Core concepts

• Use a security bridge table mapping users (or groups) to data attributes (region, customer, department). Join this to your dimension(s) to filter facts.
• Prefer group-based access over individual users to simplify management.
• CLS can be implemented by hiding columns in the semantic model, masking values (hashing/partial reveal), or separate secure views.
• Always test with real user identities and scenarios before sharing.

Learning path

1. Milestone 1 — Identify security domains: List which entities drive access (e.g., region, business unit, customer). Mark sensitive columns (PII, financial).
2. Milestone 2 — Design roles and mappings: Create groups/roles and a user-to-attribute mapping table. Choose static vs dynamic patterns.
3. Milestone 3 — Implement RLS: Build filtered views or BI model filters driven by the mapping table. Validate joins and filter directions.
4. Milestone 4 — Implement CLS: Hide/mask sensitive columns based on roles. Use separate views if needed.
5. Milestone 5 — Test & share: Validate with user scenarios, audit logs, and share via secure workspaces with least privilege.

Worked examples

Example 1 — SQL dynamic RLS via mapping table

Goal: Users see Sales only for regions they are assigned to.

-- Users to regions mapping
-- user_id here should match your identity provider or BI username
CREATE TABLE user_region AS (
  SELECT 'alice@corp.com' AS user_id, 'EMEA' AS region UNION ALL
  SELECT 'bob@corp.com', 'NA' UNION ALL
  SELECT 'bob@corp.com', 'LATAM'
);

-- RLS view
CREATE OR REPLACE VIEW sales_secure AS
SELECT s.*
FROM sales s
JOIN dim_region r ON s.region_id = r.region_id
JOIN user_region ur ON ur.region = r.region_name
WHERE ur.user_id = CURRENT_USER; -- or SESSION_USER()/SYSTEM_USER() in your SQL dialect

Users query sales_secure; rows are filtered to their assigned regions.

Example 2 — Power BI dynamic RLS with DAX

Tables: UsersRegions(UserEmail, Region), DimRegion(Region), FactSales(...)

• Relationships: UsersRegions[Region] to DimRegion[Region]; DimRegion to FactSales on RegionKey.
• Create a role: RLS_User on table UsersRegions with filter:

UsersRegions[UserEmail] = USERPRINCIPALNAME()

Ensure filter flows from UsersRegions to DimRegion to FactSales. Avoid unintended bi-directional filters unless necessary.

Example 3 — Tableau row-level security with user functions

Create a data source filter (or calc field) like:

[Region] IN (
  IF USERNAME() = 'alice@corp.com' THEN 'EMEA' END,
  IF USERNAME() = 'bob@corp.com' THEN 'NA' END,
  IF USERNAME() = 'bob@corp.com' THEN 'LATAM' END
)

Prefer group-based logic (e.g., ISMEMBEROF) and join to a Users-Regions table to avoid hard-coding users in calculations.

Example 4 — Column-level masking in SQL

Goal: Show full customer_email only to Support role, otherwise masked.

CREATE TABLE user_roles AS (
  SELECT 'alice@corp.com' AS user_id, 'Support' AS role UNION ALL
  SELECT 'bob@corp.com', 'Sales'
);

CREATE OR REPLACE VIEW customers_secure AS
SELECT
  c.customer_id,
  CASE
    WHEN EXISTS (
      SELECT 1 FROM user_roles ur
      WHERE ur.user_id = CURRENT_USER AND ur.role = 'Support'
    ) THEN c.customer_email
    ELSE CONCAT(LEFT(c.customer_email, 3), '***@***')
  END AS customer_email_masked,
  c.signup_date
FROM customers c;

Downstream reports use customers_secure instead of the raw table.

Example 5 — Testing scenarios with a simple matrix

Make a test table with expected visibility:

User            | Region visible     | Columns visible
----------------+--------------------+-----------------
Alice (Support) | EMEA               | Full email
Bob (Sales)     | NA, LATAM          | Masked email

Use your BI tool's "View as role/user" feature or connect as those users. Verify counts, example records, and that hidden columns remain hidden in exports.

Drills and mini tasks

• List 3 data domains in your org that need RLS (e.g., region, customer, cost center).
• Build a Users-to-Region mapping table from your HR/IDP export.
• Create a secure view or BI role that filters rows using the mapping.
• Mark columns containing PII and decide mask vs hide.
• Test with two real users and record results.
• Document your role design and share it with a reviewer.

Common mistakes and debugging tips

• Forgetting the bridge join: RLS rules do nothing if the mapping table is not properly related to the filtered dimension/fact.
• Bi-directional filter traps: In BI models, unnecessary bi-directional relationships can over-filter or leak data. Prefer single-direction plus explicit filter tables.
• Hard-coding users: Use groups/roles and a mapping table; avoid listing users in expressions.
• Export loopholes: Ensure exports/downloads are governed by the same RLS/CLS rules. Disable raw data exports if your tool cannot enforce masks.
• PII in calculated columns: Calculations can re-materialize masked data. Apply masking at the source or secure view and avoid exposing raw fields.
• Untracked changes: Keep versioned definitions for roles/filters. Review audit logs after permission changes.

Practical project: Secure Sales Analytics Mart

Build a small but realistic secured dataset and dashboard.

1. Model: FactSales, DimRegion, DimCustomer. Create UsersRegions and UserRoles tables.
2. RLS: Implement dynamic region-based filters using the UsersRegions mapping.
3. CLS: Mask customer_email for non-Support roles; hide customer_phone unless Support.
4. Dashboard: Sales by region and customer. Confirm users only see allowed regions and masked columns.
5. Testing: Validate with at least 3 user personas. Capture screenshots and row counts.
6. Governance: Store role definitions, test cases, and a change log in your team docs.

Subskills

• Implementing Row Level Security: Build static and dynamic filters using user-to-attribute mappings.
• Implementing Column Level Security: Hide or mask sensitive columns in views or the semantic model.
• Role Based Access Design: Define groups/roles aligned to business domains and least privilege.
• Dynamic Security Patterns Basics: Use bridge tables and user functions to drive per-user filters.
• Testing Security With User Scenarios: Validate visibility and exports with real identities.
• Secure Sharing And Workspace Permissions: Share via governed workspaces; avoid unsecured extracts.
• Handling PII In Reports: Mask, tokenize, or remove PII based on need-to-know.
• Audit And Access Review Basics: Review logs and memberships regularly and after changes.

Next steps

• Extend your mapping tables to cover additional domains (e.g., product lines, projects).
• Add scheduled access reviews with an owner, frequency, and checklist.
• Package secured views/models as reusable datasets to reduce duplication.
• Prepare for the skill exam below to check your readiness.

Skill exam

When you are ready, take the exam below. Anyone can take it for free. If you are logged in, your progress and results will be saved to your profile.