Spring JdbcTemplate tenant predicates: put ownership in the SQL query

The same receipt ID exists twice

The H2 fixture stores R-41 for tenant-east and tenant-west with different states. Querying by receipt ID and tenant ID returns accepted for east, held for west and no row for a third tenant. The authenticated web request for east reaches that query; a west route under the east token is denied by method security before query execution. The two checks cover separate boundaries.

Do not fetch by receipt ID alone and filter afterward. That can disclose the wrong row through logging, caching, serialization or a later code path. Use the trusted identity mapped in JWT validation, then bind it as a SQL parameter. For writes, include tenant_id and expected state or version in the update predicate, and inspect the affected row count. The earlier write fixture is only an in-memory service rule.

Keys and plans matter

The fixture uses a composite primary key (receipt_id, tenant_id). A real schema should choose an index order based on its query patterns and verify the plan on the target database. The predicate is an application safeguard; database row-level security can add another independent boundary if the platform supports it. Never form SQL identifiers from tenant text.

Checked source

String state = jdbc.queryForObject(
    "select state from tenant_receipt where receipt_id = ? and tenant_id = ?",
    String.class, "R-41", trustedTenant);

Verification boundary

JwtTenantBoundaryTest.scopedQueryExcludesRowsForOtherTenantValues and JwtTenantBoundaryTest.trustedTenantClaimPassesTheMatchingServiceRule runs in the downloadable Spring source kit. The excerpt is shortened; the kit contains the complete test.

Costs and limits

The test checks three tenant values against two H2 rows and one authenticated request. It does not prove target-database plans, row-level security, large-table latency or that every application repository uses the predicate. An indexed point lookup is expected to scale with index depth plus returned rows; measure the chosen schema.

Common Mistakes

• Do not query by receipt ID and filter in Java afterward.
• Do not pass a URL or header tenant into SQL without a membership decision.
• Do not ignore a zero-row update in a tenant-scoped write.

Read next

Spring Security JWT tenant principal: map only a validated claim, Spring Security scope versus tenant ownership: two separate decisions, Spring method security: reject a cross-tenant receipt mutation, Spring JdbcTemplate: bound values and visible database constraints.

Continue with checked tenant commands

Continue with Spring JDBC versioned tenant update: inspect the affected row count.

Continue with checked settings and schema rollout

Continue with Spring database backfill: assign historical rows before enforcing ownership.