SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsrls-multi-tenant

rls-multi-tenant

Data & analytics

View repository

Updated 10-16 00:31

About this skill

Tenant isolation that holds, and the test that proves it

The deliverable is the test, not the policy. The value is an assertion that goes red when isolation breaks, because every way it breaks here is silent: no error, no warning, no log line, and a catalog view that still reports the policy enabled.

Measured 2026-08-28 against a live engine reporting EngineEdition 5 and Edition SQL Azure. Open the measured runs when you need the statement behind a count or a message number below, or before changing one.

1. A filter predicate is not the boundary

Microsoft Learn states it plainly: the application can INSERT rows, even if they will be filtered during any other operation. Measured as a low-privilege user holding tenant id 1, filter predicate only:

Statement Result What it cost
INSERT a row stamped tenant_id = 2 succeeded the row exists, invisible to its author, visible to tenant 2
UPDATE one of my own rows to tenant_id = 2 succeeded my row moved into another tenant, silently
UPDATE a row of tenant 2 0 rows affected the filter protects the read behind a write
DELETE a row of tenant 2 0 rows affected same
INSERT a key that already exists in tenant 2 Msg 2627 the key space of another tenant is probeable

The first two rows are the leak. The application that wrote them can never read them back and never delete them, because the filter now hides them from their own author. Recovery meant disabling the policy.

The last row is separate and smaller. A filter predicate hides rows, not their existence: Msg 2627 on a duplicate key and success on a free one let a caller enumerate another tenant's keys one attempt at a time. Use identifiers with no guessable ordering.

2. The block predicate, and why it is two predicates

Learn defines four block operations: AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE. Adding the first two turned both cross-tenant writes into:

Msg 33504: The attempted operation failed because the target object '<db>.<schema>.<table>'
has a block predicate that conflicts with this operation.

The legitimate write into the caller's own tenant still succeeded.

AFTER UPDATE is the one people leave out. Measured: with the filter predicate and AFTER INSERT in place but no AFTER UPDATE, the cross-tenant insert was refused and the tenant-move update still succeeded. One row left the tenant with no error.

CREATE FUNCTION sec.fn_tenant(@tenant_id int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS ok
       WHERE @tenant_id = CAST(SESSION_CONTEXT(N'tenant_id') AS int);
GO

CREATE SECURITY POLICY sec.p_orders
    ADD FILTER PREDICATE sec.fn_tenant(tenant_id) ON dbo.orders,
    ADD BLOCK PREDICATE sec.fn_tenant(tenant_id) ON dbo.orders AFTER INSERT,
    ADD BLOCK PREDICATE sec.fn_tenant(tenant_id) ON dbo.orders AFTER UPDATE
    WITH (STATE = ON);
GO

-- the other half of the guarantee: the application never rewrites a tenant id
DENY UPDATE ON dbo.orders(tenant_id) TO app_user;

Learn adds two things. The optimizer skips an AFTER UPDATE block predicate when the update changed no column the predicate reads, so ordinary updates pay nothing for it. And Learn's middle-tier example omits AFTER UPDATE only because it denies UPDATE on the tenant column instead. Take both. BEFORE UPDATE and BEFORE DELETE are redundant while a filter predicate is present, since the filter has already removed the other tenant's rows from the statement.

3. How the tenant reaches the predicate, and what pooling does to it

Two designs, and the difference is measurable rather than stylistic.

SESSION_CONTEXT is a variable, not an identity

Learn is explicit that any user can set a session context for their session, and the measurement matches: a user granted only CONNECT plus SELECT, INSERT, UPDATE and DELETE on one table set its own tenant id to 2 and read tenant 2's rows. No grant was needed, and none can be revoked to stop it.

So a SESSION_CONTEXT design is exactly as strong as the guarantee that nothing attacker-influenced ever reaches that connection as SQL. prevent-sql-injection owns that: open it whenever any part of a statement here is assembled from input instead of passed as a parameter, because under this design a successful injection rewrites the tenant boundary itself.

The failure that reaches production

The tenant id outlives the request, because the connection does. Measured over a five connection pool, 200 concurrent requests alternating between two tenants:

Application shape Cross-tenant reads in 200 requests
Set the context in one pooled request, run the query in another 103, then 91 on a repeat
Set the context and run the query on one acquired connection 0
A database user per tenant, one pool per tenant, no session context 0

Half the requests returned another tenant's rows and no caller saw an error. The application set the tenant on every request; it handed the set and the query to the pool separately, and the pool gave them different connections. Two rules follow:

  1. Set the tenant and read on the same acquired connection, in one batch, one transaction, or one explicitly held connection. Not two calls to a pool.
  2. Never leave the tenant set when a connection returns to the pool. That failure is open, not closed: the next caller that forgets reads the previous tenant's rows.

@read_only = 1 looks like the fix, and here the documentation and the measurement point different ways. Learn presents it as preventing the value changing again until the connection returns to the pool, which is exactly what it does. Measured on a reused pooled connection, the next tenant's correct attempt to claim its own id was refused with Msg 15664 while the request that forgot to set anything still read the previous rows. It is safe only where the connection ends with the request.

A database user per tenant

The predicate reads USER_NAME() or DATABASE_PRINCIPAL_ID() instead of session state, resolved through a mapping table:

CREATE FUNCTION sec.fn_tenant_by_user(@tenant_id int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS ok FROM sec.tenant_map m
       WHERE m.db_user = USER_NAME() AND m.tenant_id = @tenant_id;

Measured: setting SESSION_CONTEXT under this predicate changed nothing, and borrowing another tenant's identity with EXECUTE AS USER was refused with Msg 15517, since impersonation is a permission the tenant does not hold. Connection pools are keyed by connection string, so each tenant gets its own pool and there is no shared connection to leak through.

The cost is one login, one database user, one connection string and one pool per tenant, which fits hundreds of tenants and not tens of thousands. Choose it when isolation has to survive an application bug rather than depend on its absence.

4. Who bypasses the policy

Principal Reads through the policy Can turn it off
A plain application user no, 0 rows with no tenant set no, Msg 33268
The table owner, which is also the database owner no, 0 rows with no tenant set yes
A member of db_owner no, 0 rows with no tenant set yes, in one statement, then reads every row
ALTER ANY SECURITY POLICY alone no no, Msg 33268
ALTER on the schema holding the policy alone no no, Msg 33268
Both of the above together no yes, in one statement, then reads every row

Ownership is not a read-through. Learn says a dbo user, a db_owner member and the table owner are all filtered or blocked as the policy defines, and all three returned zero rows with no tenant set. What they hold is the ability to disable, a separate and auditable statement.

ALTER ANY SECURITY POLICY alone is not enough. Learn splits the requirement, altering a policy against that permission and creating or dropping one against ALTER on the schema. Measured, disabling one needed both: either alone failed with Msg 33268, reported as a missing object rather than a permission failure. Grant them as a pair, to a principal no application connects as.

5. A policy can be present and not enforcing, in three ways

All three look healthy from the application: rows come back, nothing errors.

Shape What the application sees What gives it away
WITH (STATE = OFF) every tenant's rows sys.security_policies.is_enabled = 0
A predicate that returns a row when the tenant is unset every tenant's rows, to a session that set nothing nothing in any catalog view; is_enabled is still 1
A second tenant-scoped table nobody added to the policy that table unfiltered, while the covered table is correct no predicate rows for that object

A predicate of the form WHERE @tenant_id = <context> OR <context> IS NULL returned all four rows to a session with no tenant set, while every catalog view reported a healthy enabled policy. Predicates fail closed or they are not predicates. Never add an escape clause for administrative sessions.

The third is a sweep, not a review. Run it after every migration that adds a table:

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS table_name,
        SUM(CASE WHEN sp.predicate_type = 0 THEN 1 ELSE 0 END) AS filter_predicates,
        SUM(CASE WHEN sp.predicate_type = 1 THEN 1 ELSE 0 END) AS block_predicates,
        MIN(CAST(pol.is_enabled AS int))                       AS policy_enabled
FROM sys.tables t
JOIN sys.columns c
  ON c.object_id = t.object_id AND c.name = N'tenant_id'
LEFT JOIN sys.security_predicates sp ON sp.target_object_id = t.object_id
LEFT JOIN sys.security_policies  pol ON pol.object_id = sp.object_id
GROUP BY t.schema_id, t.name
HAVING SUM(CASE WHEN sp.predicate_type = 1 THEN 1 ELSE 0 END) < 2
    OR MIN(CAST(pol.is_enabled AS int)) IS NULL
    OR MIN(CAST(pol.is_enabled AS int)) = 0;

predicate_type is 0 for filter and 1 for block, read back from sys.security_predicates rather than recalled. Read the list rather than count it: a tenant mapping table carries a tenant_id column, legitimately has no predicate, and is dismissed once. Anything else in it is a tenant table that is not isolated. Verified by planting a failure: drop one block predicate from a correct table and that table appears.

One shape the sweep misses, documented by Learn rather than measured here: predicates are not replicated to a system-versioned table's history table, which needs its own predicate by name.

6. The local rehearsal gap for retrieval

A tenant predicate on a chunk table is the cheapest defence against a retrieval answering one tenant with another tenant's text, and on the Azure SQL Database container it cannot be rehearsed on an indexed table. Reproduced on 300 rows with a real vector index, in both orders: index first then CREATE SECURITY POLICY gives Msg 37579, policy first then CREATE VECTOR INDEX gives Msg 42244. On Azure SQL Database the two coexist. So the isolation control is exactly the part of a retrieval design that never runs locally beside the index it will meet in production.

What does work locally: the filter predicate applies correctly to exact search over VECTOR_DISTANCE with no vector index, and two tenants over 300 chunks each got back only their own rows. Rehearse the predicate unindexed, keep index creation out of the local path, and run the isolation test again once the index exists on the cloud database. The same local/cloud pair was measured for both outcomes.

Check it worked

Three checks, cheapest first, against the reference fixture: dbo.orders holding four rows, two each for tenants 1 and 2, and app_user granted only CONNECT and the four table permissions.

One: the filtered count is smaller than the table, and the administrator is not exempt. Run it on an administrative connection, which is the only one that can impersonate:

EXEC sys.sp_set_session_context @key = N'tenant_id', @value = 1;
SELECT COUNT(*) AS admin_tenant_1 FROM dbo.orders;
EXECUTE AS USER = 'app_user';
SELECT COUNT(*) AS app_tenant_1 FROM dbo.orders;
EXEC sys.sp_set_session_context @key = N'tenant_id', @value = 2;
SELECT COUNT(*) AS app_tenant_2 FROM dbo.orders;
EXEC sys.sp_set_session_context @key = N'tenant_id', @value = NULL;
SELECT COUNT(*) AS app_no_tenant FROM dbo.orders;
REVERT;

Expected against four seeded rows: 2, 2, 2, 0, and the two twos are different rows. A 4 anywhere means the policy is not enforcing, which is section 5; zero everywhere means the predicate is not reading the key you set.

Two: a cross-tenant write raises rather than vanishing.

EXECUTE AS USER = 'app_user';
EXEC sys.sp_set_session_context @key = N'tenant_id', @value = 1;
INSERT INTO dbo.orders (order_id, tenant_id, customer, amount) VALUES (901, 2, N'probe', 1.00);
REVERT;

Expect Msg 33504. If it succeeds you have the leak in section 1, and the row is already invisible to the connection that wrote it.

Three: the isolation test, as the application's own user, on every deployment, saved as tenant-isolation.sql:

SET NOCOUNT ON;
DECLARE @mine int = 1, @theirs int = 2, @fail int = 0, @n int;
-- unique per run: a failing run strands a probe row it can no longer see or delete
DECLARE @probe int = 900000 + ABS(CHECKSUM(NEWID())) % 90000;

EXEC sp_set_session_context @key = N'tenant_id', @value = 1;

-- 1. every visible row is mine
SELECT @n = COUNT(*) FROM dbo.orders WHERE tenant_id <> @mine;
IF @n <> 0 BEGIN PRINT 'FAIL 1: the filter predicate leaks another tenant''s rows'; SET @fail = 1; END

-- 2. an unfiltered aggregate cannot see more than the filtered set
SELECT @n = COUNT(*) FROM dbo.orders;
IF @n <> (SELECT COUNT(*) FROM dbo.orders WHERE tenant_id = @mine)
   BEGIN PRINT 'FAIL 2: aggregate disagrees with the filtered set'; SET @fail = 1; END

-- 3. a write addressed to another tenant is REFUSED, not silently accepted
BEGIN TRY
    INSERT INTO dbo.orders (order_id, tenant_id, customer, amount)
    VALUES (@probe, @theirs, N'probe', 1.00);
    PRINT 'FAIL 3: cross-tenant INSERT accepted, no BLOCK PREDICATE AFTER INSERT';
    SET @fail = 1;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 33504 BEGIN PRINT CONCAT('FAIL 3: unexpected error ', ERROR_NUMBER()); SET @fail = 1; END
END CATCH

-- 4. moving one of my own rows into another tenant is REFUSED
INSERT INTO dbo.orders (order_id, tenant_id, customer, amount) VALUES (@probe + 1, @mine, N'probe', 1.00);
BEGIN TRY
    UPDATE dbo.orders SET tenant_id = @theirs WHERE order_id = @probe + 1;
    PRINT 'FAIL 4: tenant-move UPDATE accepted, no BLOCK PREDICATE AFTER UPDATE';
    SET @fail = 1;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 33504 BEGIN PRINT CONCAT('FAIL 4: unexpected error ', ERROR_NUMBER()); SET @fail = 1; END
END CATCH
DELETE FROM dbo.orders WHERE order_id = @probe + 1;

-- 5. no tenant set means no rows: the predicate fails closed
EXEC sp_set_session_context @key = N'tenant_id', @value = NULL;
SELECT @n = COUNT(*) FROM dbo.orders;
IF @n <> 0 BEGIN PRINT 'FAIL 5: the predicate fails OPEN when no tenant is set'; SET @fail = 1; END

IF @fail = 1 THROW 50001, 'tenant isolation test FAILED', 1;
PRINT 'tenant isolation: ALL PASS';

export SQLCMDPASSWORD='<app-user-password>'
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U app_user -C \
  -b -m-1 -V 16 -i tenant-isolation.sql -o tenant-isolation.out
echo "exit $?"
grep -nE '^FAIL |^Msg ' tenant-isolation.out

Expect exit 0, tenant isolation: ALL PASS in the file, and no output from grep. THROW sets severity 16, above the severity 10 -b ignores, so a failed assertion does set a non-zero exit; -m-1 puts every message in the file, including the PRINT naming which assertion went red.

-m-1 is an ODBC sqlcmd instruction, the 18.x build from mssql-tools18. Measured 2026-09-05, go-sqlcmd 1.10.0 prints no Msg header on a severity 10 message at any -m value, so there the PRINT lines arrive with no number.

Each failure was planted and watched going red, which is the only evidence an assertion asserts anything:

Planted What went red
Dropped the AFTER INSERT block predicate 3
Dropped the AFTER UPDATE block predicate 4
WITH (STATE = OFF) 1, 2, 4, 5
Swapped in the predicate that returns rows when the tenant is unset 5

Assertions 1 and 2 are the same claim from two angles: 2 catches a predicate that filters a direct scan and not an aggregate. Add the pooling case in application test code, because no single connection reproduces it: issue concurrent requests for two tenants against one pool and assert every response carries only its own tenant, the shape that measured 103 wrong answers in 200.

Do not

  • Do not ship a filter predicate on its own. A cross-tenant insert succeeds under it and the row is then unreadable and undeletable by the application that wrote it.
  • Do not add AFTER INSERT and stop. The tenant-move update was measured succeeding with that block predicate in place.
  • Do not set the tenant in one pooled call and query in another, and do not reach for @read_only = 1 to make that safe. It refuses the correct caller and leaves the forgetful one leaking.
  • Do not treat SESSION_CONTEXT as an identity. Any caller on the connection rewrites it with no permission, so one concatenated statement defeats the whole policy.
  • Do not assume the table owner or db_owner reads through the policy. They do not. They disable it, which is a different and visible act.
  • Do not grant ALTER ANY SECURITY POLICY believing it is inert alone. Paired with ALTER on the policy's schema it is a full read of every protected table, in one statement.
  • Do not read is_enabled = 1 as proof of isolation. A fail-open predicate and an uncovered table both leave it at 1.
  • Do not conclude row level security is unavailable because the container refuses it beside a vector index. The refusal is about the index, not about the policy.
  • Do not leave the tenant column nullable. A row that arrives with no tenant is invisible to every tenant and belongs to none.

References

  • Open the measured runs when a number above is disputed, before changing one, or to reproduce the pooling harness and the four planted failures.
  • Read Row-level security before designing a predicate more complex than one column, for the block operations, the permission split, and the cross-feature list that names indexed views and temporal tables.
  • Read sp_set_session_context before choosing session context over a user per tenant: the 8,000 byte value limit, the 1 MB session total, and the @read_only argument.
  • Read sqlcmd utility when wiring the isolation test into a pipeline, for what -b, -m and -V do.
  • prevent-sql-injection when any statement here is assembled rather than parameterised, which is what a session context design depends on. entra-id-auth for getting an identity onto a working connection; use a custom least-privilege role for the permissions that identity receives.
  • design-azure-sql-schema for the tenant column and its place in the key, t-sql-correctness for the predicate function as T-SQL, and rag-on-azure-sql with vector-search-azure-sql for the retrieval this policy sits under.