Why tenant isolation belongs in the query layer, not in a document

Every multi-tenant system, once it has been in production for a while, runs into the same problem: a query against some table is missing the tenant_id condition. In code review it looks completely normal — the SQL is syntactically fine, the business logic makes sense; it just returns a few extra rows that were never meant to be seen.

The root cause of this class of bug is placing the security boundary on the premise that “developers remember to write it”. People forget, newcomers do not know, refactors drop it. So our approach is to push enforcement of the tenant predicate down into the data access layer.

Central declaration, not scattered memory

The first step is to turn “which tables are tenant-scoped” into a single central registry. 45 tables are declared once, in TenantScopedTables, instead of being scattered across dozens of mappers and held together by convention.

01// 集中注册：租户作用域表是一份清单，不是隐式约定
02object TenantScopedTables {
03    val ALL: Set<String> = setOf(
04        "sys_user", "sys_role", "sys_permission",
05        "sys_dept", "sys_space", "sys_audit_event",
06        // …共 45 张
07    )
08}

Intercept missing predicates at the query layer

The second step is to have jOOQ check on every query: does this SQL touch a tenant-scoped table? If it does, does its WHERE clause carry a tenant predicate? If not, the configuration decides whether a security log gets written or the query fails outright.

01override fun visit(ctx: VisitContext): Queries? {
02    if (!touchesTenantScopedTable(ctx.query())) return null
03
04    if (!hasTenantPredicate(ctx.query())) {
05        // DETECT_LOG：只记录，用于灰度观察
06        // DETECT_THROW：直接失败，生产环境默认
07        when (mode) {
08            DETECT_LOG -> audit.missingTenantPredicate(ctx)
09            DETECT_THROW -> throw MissingTenantPredicateException()
10        }
11    }
12    return null
13}

The token is the only trusted source, not the request headers

tenant_id is read from the JWT and nowhere else. If a request header also declares a tenant and the two disagree, the request is rejected on the spot — a request header can be written to say whatever the caller likes; a token cannot.

• TenantContextFilter parses tenant_id out of the JWT and writes it into the request context
• Header claim disagrees with the token → TenantMismatchException (403)
• System-level operations that genuinely must cross tenants go through the explicit runAsSystem bypass — and the bypass itself is audited
• UserContext is cleared at the end of each request, so thread-pool reuse cannot leak state across requests

What it costs

This machinery is not free. It requires every tenant-scoped table to follow one shared tenant-column naming convention, and it requires developers who genuinely need a cross-tenant query to call the bypass channel explicitly — more trouble than dashing off a query with no conditions at all.

We accept the trouble. In a multi-tenant system, “a bit of extra work” and “a data leak” are not problems of the same magnitude.