Data Masking, Ledger, and Row-Level Security

Protecting sensitive data

Dynamic data masking

Masking hides sensitive data from non-privileged users at query time — the actual data in the table is unchanged.

Masking functions

Function	What It Does	Example Input → Output
Default	Full mask based on data type	”Hello” → “XXXX”, 12345 → 0
Email	Shows first letter + domain	”amara@hospital.com” → “aXXX@XXXX.com”
Partial	Custom prefix/suffix with padding	SSN “123-45-6789” → “XXX-XX-6789”
Random	Random number in range	50000 → random between 1-100

-- Add masking when creating a table
CREATE TABLE Patients (
    PatientID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    SSN NVARCHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'),
    Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'random(1, 100)')
);

-- Add masking to an existing column
ALTER TABLE Patients ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Grant a user permission to see unmasked data
GRANT UNMASK TO [dr_chen@harbourhealth.com];

-- Granular unmask (SQL Server 2022+ / Azure SQL)
GRANT UNMASK ON dbo.Patients(SSN) TO [billing@harbourhealth.com];

Key points for the exam:

• Masking is not encryption — data is stored unmasked, only displayed masked
• Users with UNMASK permission see the real data
• db_owner always sees unmasked data
• Masking doesn’t protect against direct table access via T-SQL tricks (e.g., WHERE clauses can still filter on masked values)

💡 Exam tip: masking limitations

Dynamic masking is a presentation-layer control, not a security boundary:

• It protects against casual exposure (support screens, reports)
• It does NOT protect against determined attackers who can infer values through queries
• For true protection against privileged users, use Always Encrypted instead
• The exam loves combining masking with Always Encrypted: “masking for support staff, Always Encrypted for the data itself”

Azure SQL Ledger

Ledger provides tamper-evident storage — cryptographic proof that data hasn’t been altered without authorisation.

How ledger works

1. Every change to a ledger table is recorded in a transaction log hash chain
2. Each block of transactions is hashed and linked to the previous block (blockchain-style)
3. The hash chain is stored in database digests — write these to tamper-proof storage (Azure Confidential Ledger or Azure Blob Storage with immutability)
4. Verification compares database content against the digest — any tampering is detected

Two types of ledger tables

Type	Use Case	How It Works
Updatable ledger table	Tables with normal DML (INSERT, UPDATE, DELETE)	Creates a history table that records all changes. Main table + history table + ledger view.
Append-only ledger table	Insert-only scenarios (audit logs, event records)	No updates or deletes allowed. Every row is permanent.

-- Create an updatable ledger table
CREATE TABLE dbo.AccountBalances (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(18,2),
    LastUpdated DATETIME2
) WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

-- Create an append-only ledger table
CREATE TABLE dbo.AuditLog (
    EventID INT IDENTITY PRIMARY KEY,
    EventType NVARCHAR(50),
    EventData NVARCHAR(MAX),
    EventTime DATETIME2 DEFAULT GETUTCDATE()
) WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Verify ledger integrity
EXECUTE sp_verify_database_ledger;

Amara’s use case: Harbour Health uses an updatable ledger table for medication administration records. If a regulator asks “was this record modified after the fact?”, the ledger verification proves data integrity.

Enabling database-level ledger

You can enable ledger at the database level — ALL new tables become ledger tables by default:

ALTER DATABASE HarbourHealthDB SET LEDGER = ON;

Row-level security (RLS)

RLS lets you control which rows a user can see — enforced at the database engine level.

How it works

1. Create a filter predicate function — returns 1 (allow) or 0 (deny) for each row
2. Create a security policy that binds the function to a table
3. Every query on the table automatically filters rows based on the predicate

-- Step 1: Create a schema for security objects
CREATE SCHEMA Security;

-- Step 2: Create the predicate function
CREATE FUNCTION Security.fn_BranchFilter(@BranchID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
  WHERE @BranchID = CAST(SESSION_CONTEXT(N'BranchID') AS INT)
     OR IS_MEMBER('db_owner') = 1;

-- Step 3: Create the security policy
CREATE SECURITY POLICY BranchFilter
ADD FILTER PREDICATE Security.fn_BranchFilter(BranchID) ON dbo.Orders
WITH (STATE = ON);

-- Step 4: Set the session context for each connection
EXEC sp_set_session_context @key = N'BranchID', @value = 42;

-- Now: SELECT * FROM Orders only returns rows where BranchID = 42

Filter vs block predicates

Predicate	Controls	Behaviour
FILTER	SELECT, UPDATE, DELETE	Silently filters out rows — user doesn’t know hidden rows exist
BLOCK	INSERT, UPDATE (AFTER/BEFORE)	Prevents DML that would violate the policy — raises an error

Priya’s multi-tenant use case: ScaleWave uses RLS to isolate tenant data. All tenants share the same table, but each only sees their own rows. A filter predicate checks TenantID against the authenticated user’s tenant.

ℹ️ RLS performance considerations

• The predicate function runs for every row the query touches — ensure it’s efficient
• Use SCHEMABINDING on the function (required by security policy)
• Index the column used in the predicate (e.g., BranchID, TenantID)
• Test with execution plans to verify the filter is applied early (predicate pushdown)
• RLS doesn’t prevent users from seeing row counts or aggregate information about hidden rows (e.g., COUNT(*) may still reflect all rows depending on the predicate)

Question

Is dynamic data masking the same as encryption?

Click or press Enter to reveal answer

Answer

No. Masking is a presentation-layer control — data is stored unmasked, only displayed masked to non-privileged users. Users with UNMASK permission or db_owner see the real data. For true protection, use Always Encrypted.

Click to flip back

Question

What is the difference between updatable and append-only ledger tables?

Click or press Enter to reveal answer

Answer

Updatable: supports INSERT, UPDATE, DELETE with a history table recording all changes. Append-only: INSERT only — no updates or deletes allowed. Every row is permanent.

Click to flip back

Question

How does row-level security work?

Click or press Enter to reveal answer

Answer

A filter predicate function runs for every row, returning 1 (visible) or 0 (hidden). A security policy binds the function to a table. All queries are automatically filtered — users only see rows that pass the predicate.

Click to flip back

Question

What are the two types of RLS predicates?

Click or press Enter to reveal answer

Answer

FILTER: silently hides rows from SELECT/UPDATE/DELETE. BLOCK: prevents INSERT/UPDATE that would violate the policy (raises an error).

Click to flip back

Knowledge Check

Harbour Health's support team needs to look up patient records but should NOT see full SSN numbers. Dr. Chen (CISO) needs to see everything. What should Amara implement?

AAlways Encrypted with deterministic encryption on SSNBDynamic data masking on SSN with UNMASK granted to Dr. ChenCRow-level security filtering patient records by roleDTDE with customer-managed keys

Check Answer

Knowledge Check

Kenji needs to prove to auditors that no one tampered with NorthStar's financial transaction records after they were written. Which feature should he implement?

ASQL auditing with Blob StorageBChange Data CaptureCAzure SQL Ledger (append-only table)DDynamic data masking on financial columns

Check Answer

Knowledge Check

ScaleWave's SaaS application stores all customer data in shared tables. Each query should only return data belonging to the authenticated tenant. What should Priya implement?

ADatabase-level firewall rules per tenantBSeparate databases per tenant with elastic poolsCRow-level security with a filter predicate on TenantIDDDynamic data masking on TenantID column

Check Answer

🎬 Video coming soon

You’ve completed Domain 2! You can now secure Azure SQL with Entra auth, permissions, encryption, network controls, classification, auditing, masking, ledger, and RLS.

Next up: Performance Baselines and Monitoring Tools — build an operational performance baseline and learn the monitoring toolkit.