7.3 Row-Level Security and Ledger in Azure SQL

Why RLS and Ledger Appear Together

Both controls appear in the secure-environment domain because they protect trust: RLS protects trust that a user only sees rows they are entitled to, and Ledger protects trust that historical rows have not been silently modified. RLS is a runtime access control; Ledger is a cryptographic integrity control. Expect scenarios that ask when to choose RLS over a view, when to choose Ledger over a trigger-based audit, and how SESSION_CONTEXT interacts with connection pooling.

Row-Level Security (RLS)

Row-Level Security filters or blocks rows based on the calling user's identity or session context. It is implemented through three pieces: a predicate function, a security predicate that binds the function to a table, and a schema-bound function body that the optimizer can rely on.

Predicate Function

The predicate function is a schema-bound, inline table-valued function that returns 1 (allow) or 0 (deny). It is written against a filter column on the target table, typically joining to a security table or reading session context:

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_TenantPredicate (@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_result
           WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);
GO

The WITH SCHEMABINDING clause is required so the function cannot be altered while bound to a security predicate, preventing the predicate from silently changing shape.

Security Predicates: Filter vs Block

The security predicate binds the function to a table and declares the predicate type:

CREATE SECURITY POLICY Security.TenantFilterPolicy
    ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders,
    ADD BLOCK PREDICATE Security.fn_TenantPredicate(TenantId) ON dbo.Orders
    WITH (STATE = ON);

Two predicate types matter for the exam:

Block predicates have an additional timing modifier: BEFORE (default for INSERT, UPDATE, DELETE) checks the post-operation row state before the change is committed; AFTER (default for UPDATE and DELETE) checks the pre-operation state, preventing a user from deleting or moving a row they no longer should be able to see. The full set is BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE. A common trap is to add only a FILTER predicate; without a BLOCK predicate, a user can INSERT a row into another tenant's id, so block predicates are required for true isolation.

Group-Based vs Tenant-Based Filtering

The predicate function can read from a security table to support group-based filtering, or read session context for tenant-based filtering. Group-based filtering queries the user's group membership and joins to the table's allowed-group column; tenant-based filtering reads a session variable that the application sets. The exam pattern:

• Group-based: predicate reads USER_NAME() or SUSER_SNAME(), joins to a Security.GroupMembership table, returns rows where the user's group matches the row's OwnerGroup. Requires no application change; trusts the database user identity.
• Tenant-based: predicate reads SESSION_CONTEXT(N'TenantId'). Requires the application to set the context after each connection open, but lets a connection pool be shared across tenants safely because the predicate is evaluated per session.

SESSION_CONTEXT and Connection Pooling

SESSION_CONTEXT is a key-value bag scoped to the session. The application sets the tenant id immediately after opening a connection:

EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;

The @read_only = 1 flag prevents the value from being changed mid-session, closing a bypass where a user resets the tenant id after the predicate is bound. The predicate function reads via SESSION_CONTEXT(N'TenantId'). Because pool returns a reset session, the application must set the context on every checkout - the most common bug the exam tests is forgetting to set context on a pooled connection and getting empty result sets.

Performance Considerations

The predicate function is evaluated for each row the engine considers during a scan or seek. Performance rules:

• Keep the predicate cheap. Avoid complex joins inside the function; cache lookups in a security table indexed on the join column.
• Index the filter column on the protected table so the predicate can be pushed down into an index seek.
• Avoid calling SESSION_CONTEXT multiple times per row; assign to a local variable inside the function.
• Use a security table with the user's allowed keys, and join it rather than calling user functions per row.
• Predicate functions are not invoked when the caller is a member of the db_owner fixed database role or has the CONTROL permission on the table - RLS is bypassed for these principals, which the exam tests as a trap when a scenario describes a DBA who can see all rows despite RLS being enabled.

RLS Limitations and Traps

• RLS does not protect against side-channel inference: a user can SELECT COUNT(*) FROM Orders and observe filtered totals, so leak protection requires additional controls if row counts are sensitive.
• Schema-bound functions cannot be altered without dropping the security policy first.
• A FILTER predicate alone does not prevent cross-tenant INSERT; you must add a BLOCK predicate.
• RLS does not apply to members of sysadmin server role or db_owner.
• Index-backed predicates can still scan if the predicate cannot be converted to a seek; monitor execution plans.

Azure SQL Ledger

Azure SQL Ledger is a tamper-evident feature that cryptographically chains database rows so any modification or deletion is detectable. It is available in Azure SQL Database (and SQL MI in the preview/GA track you should verify against current docs at exam time) and provides database-level integrity without trusting the database administrator.

Append-Only vs Updatable Ledger Tables

Two table flavors:

Create them with syntax in the CREATE TABLE statement:

CREATE TABLE dbo.AuditEvent
(
    EventId    bigint IDENTITY PRIMARY KEY,
    EventType  nvarchar(50) NOT NULL,
    EventTime  datetime2 NOT NULL,
    Payload    nvarchar(max) NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

CREATE TABLE dbo.Transfer
(
    TransferId bigint IDENTITY PRIMARY KEY,
    Account    int NOT NULL,
    Amount     decimal(18,2) NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = OFF));

Database-Level Ledger and Hash Chaining

Behind every ledger table sits the database-level ledger: the engine computes a row hash from the row contents plus the previous row's hash (forming a per-transaction row chain), then a block hash that combines the row hashes of all rows committed in the same transaction plus the previous block hash. The block hashes form a Merkle-style chain; breaking any row changes its hash, which breaks the block hash, which breaks every subsequent block.

Because the chain is rooted inside the database, an attacker with DBA rights could in principle recompute the chain after tampering. To close that gap, Azure SQL Ledger produces a digest - a snapshot of the latest block hash - which you store outside the database (Azure Blob Storage, an on-prem file, even a printed copy). Storing the digest externally breaks the trust loop: tampering with the database no longer allows recomputing a valid chain because the externally stored digest will not match.

Generating and Verifying Digests

Generate a digest on a schedule (e.g., daily) and persist it off-box:

-- Generate the current digest
EXEC sys.sp_generate_ledger_digest;

-- Verify against an externally stored digest
EXEC sys.sp_verify_ledger_digest
    @ledger_name = 'default',
    @hash = 0xABC123...;   -- the previously stored digest

sp_verify_ledger_digest recomputes the chain from the ledger tables up to the block recorded in the supplied digest and compares. If any row was modified after the digest was generated, the verification fails - the row hash, the block hash, and every subsequent hash no longer match the digest, proving tamper. On success, the procedure confirms the database state is consistent with the digest.

A practical workflow:

1. Schedule sys.sp_generate_ledger_digest daily; write the result (a JSON document with the database ledger hash and block id) to Azure Blob Storage with immutable WORM policy.
2. On demand (audit, investigation, or scheduled), retrieve the most recent digest from Blob and call sys.sp_verify_ledger_digest to confirm the chain is intact.
3. If verification fails, the ledger has been tampered with; investigate which rows differ.

Ledger Tables vs Regular Tables

Ledger tables have additional generated columns (the ledger_* family) and a hidden internal history table (for updatable ledgers). Regular tables have none of these. You cannot convert a regular table to a ledger table with ALTER TABLE in the way you can toggle compression - the table is created as a ledger table from the start, or you migrate data into a new ledger table. The ledger columns appear when you select from the table or its associated history view; applications that select * will see them, so production code should name columns explicitly.

When to Use Ledger

Use Azure SQL Ledger when a scenario demands tamper-evidence - proof that historical records have not been modified by anyone, including privileged administrators. Examples:

• Financial transaction records subject to SOX or regulatory audit.
• Supply-chain provenance records.
• Audit logs themselves (log the auditor's actions into an append-only ledger so the audit trail cannot be rewritten).
• Compliance records where a regulator requires cryptographically verifiable integrity.

Do not use ledger when you only need to detect who read or changed data - that is auditing. Do not use ledger when you only need to hide data from unprivileged users - that is RLS or DDM. Ledger is the right answer when the scenario's word is tamper-evidence, immutability, or cryptographic proof of integrity.

Takeaways

RLS = per-row access control through predicate functions; always include both filter and block predicates for true isolation, use SESSION_CONTEXT for tenant-based filtering on pooled connections, and remember db_owner bypasses RLS. Ledger = cryptographic tamper-evidence through append-only or updatable tables with hashes chained in a database-level ledger; generate digests, store them externally, and verify with sys.sp_verify_ledger_digest. Choose RLS for visibility, Ledger for provability, and use both when a scenario demands both tenant isolation and immutable history.