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);

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.