2.4.2. Row-Level, Column-Level, and Object-Level Security

💡 First Principle: Data-level security controls what data users see within objects they already have access to. Think of it like a redacted document: OLS determines if you can see the document exists, CLS determines which paragraphs are blacked out, and RLS determines which pages you can read. Each layer serves a different purpose.

Scenario: A sales table contains data for all regions. Sales managers should only see their region's data. Column-level security hides salary columns; row-level security filters to their region only.

Row-Level Security (RLS)

• Purpose: Filter rows based on user identity
• Implementation: SQL predicate function + security policy
• Use Case: Regional sales data, department-specific records

-- Create filter predicate
CREATE FUNCTION dbo.RegionFilter(@Region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Result
WHERE @Region = USER_NAME() OR USER_NAME() = 'admin';

-- Apply security policy
CREATE SECURITY POLICY RegionPolicy
ADD FILTER PREDICATE dbo.RegionFilter(Region) ON dbo.Sales
WITH (STATE = ON);

• Warehouse and SQL analytics endpoint behavior: only filter predicates are supported. They silently filter SELECT, UPDATE and DELETE, but an INSERT still succeeds even if the new row will be filtered. The policy applies to every user, including dbo, table owners and workspace Admins, unless the predicate lets them through. A table with no policy (or a policy with STATE = OFF) is unfiltered.
• Mapping tables: predicates often join a user-to-region mapping table; a user with no row in it sees no data.
• Semantic model RLS (Power BI / Direct Lake): roles with DAX filters such as [Region] = USERPRINCIPALNAME(), usually through a mapping table. It restricts only users with Viewer access (still applied to Viewers who have Build permission); workspace Admins, Members and Contributors can edit the model and bypass it.

Column-Level Security (CLS)

• Purpose: Hide specific columns from unauthorized users
• Implementation: GRANT/DENY SELECT on specific columns
• Use Case: Hide salary, SSN, sensitive PII from general users

-- Grant access to specific columns only
GRANT SELECT ON dbo.Employees(EmployeeID, Name, Department) TO GeneralUsers;
-- Sensitive columns (Salary, SSN) not granted = hidden

Object-Level Security (OLS)

• Purpose: Control which database objects (tables, views) users can see
• Implementation: GRANT/DENY permissions on objects
• Use Case: Hide entire tables from certain user groups

Visual: Security Layers Flow

⚠️ Exam Trap: Implementing RLS on a report but not on the underlying data source creates a security illusion. Users with direct access to the lakehouse bypass report-level RLS. Security must be applied at the data layer, not just the presentation layer.