Why this matters
As a BI Developer, you often work with sensitive data (PII, salaries, health info). Column Level Security (CLS) lets you hide or mask specific columns based on who is viewing the data. This enables safe self-service analytics without creating separate datasets for each audience.
- Real tasks you will face: mask salaries for non-HR, hide PII from external partners, restrict finance metrics to leadership, and keep audit trails.
- CLS reduces data leakage risk and simplifies governance while keeping one consistent model for everyone.
Concept explained simply
CLS restricts access to specific columns for certain users or groups. Instead of duplicating tables, you add rules so some people see values, others see blanks or obfuscated values.
Mental model
Imagine a spreadsheet where everyone can open the file, but certain columns are auto-hidden or blurred unless you have the right badge. The rows are the same; the difference is whether a column’s values are visible, masked, or not returned at all.
Key terms
- Masking: Replace sensitive values with NULL, ****, or a generalized value.
- Object-Level Security (OLS): Hides entire tables/columns from the model (supported in some BI engines).
- Row-Level Security (RLS): Filters which rows a user can see (complements CLS).
- Policy-based security: Rules defined in the database/warehouse and applied consistently to columns.
Design checklist you can reuse
- Identify sensitive columns: PII, salaries, health, customer contact info, secrets.
- Decide outcome per role: Hide completely, mask, aggregate-only, or full access.
- Pick enforcement layer: Database (preferred), Semantic model/BI tool, or both.
- Centralize role mapping: Maintain a user-to-role table or identity claim mapping.
- Log access outcomes: Make it auditable (who would have seen what and when).
- Test with real identities: Verify behavior for allowed and disallowed users.
- Document behaviors: What users should expect to see, plus support contacts.
Step-by-step playbook
- List sensitive columns and classify them as PII, financial, or confidential.
- Define roles and rules: for each column, which roles can view, mask, or hide.
- Choose implementation approach:
- Database: column masking policies, views with CASE, or policy tags.
- BI layer: object-level security (if supported), or secured measures.
- Create/maintain a role mapping table that ties usernames/emails/groups to roles.
- Implement CLS rules; keep logic centralized and parameterized by user/role.
- Test: switch identities and compare expected vs. actual outputs.
- Monitor and audit: log queries and review exceptions regularly.
Worked examples
Example 1 — SQL view with conditional masking
Goal: Only HR can see salary. Others see NULL.
-- Tables
-- employees(emp_id, name, dept, salary)
-- user_roles(email, role)
-- Generic pattern (adjust CURRENT_USER/SESSION_USER for your DB)
CREATE OR REPLACE VIEW v_employees_secure AS
SELECT
e.emp_id,
e.name,
e.dept,
CASE WHEN ur.role = 'HR' THEN e.salary ELSE NULL END AS salary
FROM employees e
LEFT JOIN user_roles ur
ON LOWER(ur.email) = LOWER(CURRENT_USER);
-- Test by changing CURRENT_USER context or using SET ROLE.
Outcome: HR sees salary values; others see NULLs.
Example 2 — Power BI (Object-Level Security or secured measure)
If your environment supports Object-Level Security (OLS), hide the sensitive column for non-HR roles. If OLS is unavailable, create a secured measure and use it instead of the raw column:
-- SecurityMap columns: UserEmail, CanSeeSalary (TRUE/FALSE)
-- Relationship: SecurityMap[UserEmail] to USERPRINCIPALNAME()
Salary Secured =
VAR Allowed = CALCULATE(
SELECTEDVALUE(SecurityMap[CanSeeSalary]),
KEEPFILTERS(TREATAS({ USERPRINCIPALNAME() }, SecurityMap[UserEmail]))
)
RETURN IF(Allowed = TRUE(), SUM(Employees[Salary]), BLANK())
Replace visuals using Employees[Salary] with [Salary Secured]. Non-authorized users see blanks.
Example 3 — Tableau calculated mask
Use USERNAME() or ISMEMBEROF() to mask values. Create a calculated field:
// [Salary Secured]
IF ISMEMBEROF('HR') THEN [Salary] ELSE NULL END
Use [Salary Secured] in views and exclude raw [Salary] from shelves for non-HR audiences.
Example 4 — Warehouse policy-based masking (Snowflake)
CREATE OR REPLACE MASKING POLICY mp_salary AS (val NUMBER) RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ROLE') THEN val
ELSE NULL
END;
ALTER TABLE employees MODIFY COLUMN salary SET MASKING POLICY mp_salary;
Policy applies wherever the column is queried. Adjust to your warehouse syntax.
Exercises you can practice
Complete the exercises below. A quick checklist to guide you:
- Define who should see the sensitive column.
- Decide: hide vs. mask vs. blank.
- Implement role mapping.
- Test with allowed and disallowed identities.
- Document your rule in one sentence.
Exercise 1 — SQL view masking (see Exercises list below)
Create a secure view that masks salary for non-HR users. Validate with two test users.
Exercise 2 — Power BI secured measure (see Exercises list below)
Create a [Salary Secured] measure and replace visuals to enforce column security.
Common mistakes and how to self-check
- Only row-level filters, no column masking: Users can still see sensitive columns. Self-check: does a SELECT column list still return confidential fields?
- Masking at BI only, not at source: Other tools may bypass BI rules. Self-check: query the source directly; do you see raw data?
- Using raw columns in visuals: Measures are secured but visuals use unprotected columns. Self-check: search visuals/fields for raw sensitive columns.
- Missing role mapping or case differences: Email casing breaks lookups. Self-check: normalize to LOWER() on both sides.
- Performance regressions from row-by-row CASE: Self-check: compare explain plans before/after; consider policy-based masking or indexed views.
- No audit trail: Self-check: enable query logging and keep change history of policies.
Practical projects
- Project A: Build a secure HR dashboard where salaries and bonuses are visible only to HR. Non-HR sees headcounts and aggregates, with salary columns blanked.
- Project B: Implement warehouse-level masking for PII (email, phone) and verify the same policy works across SQL clients and your BI tool.
- Project C: Create an exception audit: a table logging when a user queried sensitive columns, including timestamp and role.
Who this is for
- BI Developers and Analytics Engineers who publish shared datasets.
- Data Analysts who need to safely self-serve sensitive metrics.
- Data Stewards and Governance leads defining access policies.
Prerequisites
- Basic SQL (SELECT, JOIN, CASE).
- Familiarity with your BI tool’s security model.
- Understanding of Row vs Column level security and roles/groups in your identity provider.
Learning path
- Review data classifications and identify sensitive columns.
- Implement CLS in the warehouse (masking policy or secure view).
- Add BI-layer protections (OLS or secured measures).
- Test with multiple identities and document the outcomes.
- Automate: add role mapping maintenance and audits.
Mini challenge
Pick one table with at least two sensitive columns (e.g., email, salary). Implement CLS so that:
- Role HR sees both.
- Role Finance sees salary but not email.
- Everyone else sees neither.
Prove it with three test users and a screenshot or result table showing differences.
Quick Test
The quick test below is available to everyone. Log in to save your progress and resume later.
Next steps
- Consolidate CLS rules into a reusable policy library.
- Extend to semi-structured data (JSON columns) by masking nested fields.
- Combine with RLS for end-to-end least-privilege access.