DEV Community

Cover image for I Built a Java Agent That Injects Tenant Filters Into Every SQL Query Automatically
maksoud13
maksoud13

Posted on Originally published at Medium

I Built a Java Agent That Injects Tenant Filters Into Every SQL Query Automatically

Using ByteBuddy, JSqlParser, and AST rewriting to enforce multi-tenant isolation at the JDBC layer — with zero application code changes.


I am a solo developer, and I spent the last several months building TenantWall — an open-source Java agent that automatically injects WHERE tenant_id = ? into every SQL query your application executes. It is early stage, it has real limitations, and it just shipped v1.2.0 to Maven Central. This article explains how it works, how to try it in under 5 minutes, and where it currently falls short.


The Problem That Made Me Build This

If you are building a shared-database, shared-schema SaaS application, you have probably written a query like this:

SELECT * FROM orders WHERE status = 'PENDING';
Enter fullscreen mode Exit fullscreen mode

And in a multi-tenant system, that query silently returns every tenant's pending orders to whoever asked. Customer A sees Customer B's data. No exception is thrown. No warning is logged. The application just returns the wrong rows.

This is not a hypothetical scenario. It is the most common class of data isolation bug in shared-database SaaS applications, and it compounds quickly. A developer writes a reporting query under deadline pressure. A new team member adds a native SQL query without knowing the tenant filtering convention. A legacy endpoint gets migrated and the WHERE clause doesn't come with it. Each missed filter is a potential GDPR incident.

The standard answers — Hibernate @Filter annotations, Spring AOP aspects, custom repository base classes — help, but they all share the same blind spot: they only protect the code paths that the developer remembered to protect. Native JDBC calls bypass them. Complex reporting queries frequently skip them. And they require every developer on the team to remember the convention on every query they write, forever.

I wanted a solution that worked at the infrastructure level. If the filter is injected at the JDBC layer, it doesn't matter what ORM you use, what query you write, or whether a developer forgot the convention. The filter is always there.


How TenantWall Works

TenantWall runs as a Java agent attached to your JVM at startup. It never touches your application code. Here is what happens on every query:

HTTP Request (X-Tenant-Id: ACME)
        ↓
  Spring Boot App
        ↓
  ByteBuddy intercepts at JDBC / Hibernate boundary
        ↓
  JSqlParser rewrites SQL AST
        ↓
  SELECT * FROM orders WHERE tenant_id = 'ACME'
        ↓
      Database
Enter fullscreen mode Exit fullscreen mode

Layer 1 — Bytecode Interception with ByteBuddy

The agent uses ByteBuddy to instrument two targets at startup: Hibernate 6.x's StatementPreparerImpl and the standard JDBC Statement / PreparedStatement execution path. This dual interception means queries are caught whether they come from Hibernate, MyBatis, Spring Data JPA, or a raw jdbcTemplate.query() call. Because it operates at the bytecode level, no annotations or base classes are required anywhere in your codebase.

Layer 2 — AST-Based SQL Rewriting

String manipulation breaks immediately on real-world SQL. A regex that injects WHERE tenant_id = ? works fine on SELECT * FROM products and silently corrupts SELECT * FROM products WHERE id = 1 OR 1=1. TenantWall parses every incoming query into an Abstract Syntax Tree using JSqlParser, then walks the tree to inject the tenant predicate correctly — including across JOINs, subqueries, CTEs, and window functions.

For complex SQL dialects (CTEs, recursive subqueries, window functions), an optional Python sidecar using sqlglot handles rewriting across 10 SQL dialects. The sidecar runs locally as a Docker container and communicates with the Java agent over localhost only.

Layer 3 — Caffeine Caching

Parsing SQL on every request would be unacceptable in production. Rewritten query templates are cached in a bounded Caffeine cache (10,000 entries, 30-minute TTL). On a cache hit, overhead is effectively zero. On a parse miss, the rewrite adds approximately 0.8ms. In load testing, agent CPU overhead was 4.3% under sustained load and GC overhead 0.043% — both negligible.


Five Tenant Resolution Strategies Built In

TenantWall needs to know the current tenant's identifier at query time. Five strategies ship out of the box:

  • header — Reads X-Tenant-Id from the HTTP request header. Best for APIs and microservices.
  • jwt — Decodes a Bearer token and extracts a configured claim. Best for OAuth2/OIDC APIs.
  • session — Reads from JSESSIONID. Best for traditional web apps.
  • cookie — Reads a named cookie value. Best for legacy apps and SPAs.
  • fixed — Uses a hardcoded value. Useful for scripts and batch jobs.

If more than one strategy key is present in configuration, TenantWall logs a warning at startup. If the strategy is misconfigured, the application refuses to start rather than silently running unprotected.


Setup in Under 5 Minutes

Step 1 — Add the Spring Boot Starter

<dependency>
    <groupId>io.github.maksoud13</groupId>
    <artifactId>tenantwall-spring-boot-starter</artifactId>
    <version>1.2.0</version>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Alternatively, download the standalone agent (agent-module-1.2.0.jar) from Maven Central and attach it at JVM startup without touching pom.xml:

java -javaagent:agent-module-1.2.0.jar=tenantwall.properties -jar your-app.jar
Enter fullscreen mode Exit fullscreen mode

Step 2 — Add Four Lines of Configuration

tenantwall.enabled=true
tenantwall.filter.column=tenant_id
tenantwall.tenant.strategy=header
tenantwall.tenant.headerName=X-Tenant-Id
Enter fullscreen mode Exit fullscreen mode

Step 3 — Write Your Queries Normally

This is the entire point. Your existing queries stay exactly as they are:

@Query(value = "SELECT * FROM products", nativeQuery = true)
List<Product> findAll();
Enter fullscreen mode Exit fullscreen mode

When a request arrives with X-Tenant-Id: ACME, TenantWall intercepts and rewrites before execution:

-- What your code sends:
SELECT * FROM products

-- What actually hits the database:
SELECT * FROM products WHERE tenant_id = 'ACME'
Enter fullscreen mode Exit fullscreen mode

This works identically for UPDATE and DELETE queries. An UPDATE without a matching tenant_id cannot affect another tenant's rows because the WHERE clause is injected before the statement executes.


For Complex SQL — The Python Sidecar

For queries involving CTEs, window functions, or recursive subqueries, TenantWall routes to an optional Python sidecar that uses sqlglot for more robust parsing across 10 SQL dialects:

docker run -d --name tenantwall-parser \
  -p 127.0.0.1:8000:8000 \
  -e TENANTWALL_PARSER_SECRET="your-32-char-secret" \
  -e TENANTWALL_PARSER_ENFORCE_AUTH=1 \
  maksoud13/tenantwall-parser:latest
Enter fullscreen mode Exit fullscreen mode

The sidecar binds to 127.0.0.1 only — it is never exposed to the network. Communication between the Java agent and the sidecar is authenticated using short-lived HMAC-SHA256 JWTs, so even local traffic cannot be forged.


Load Testing Results

TenantWall was load tested against MySQL 8.0 with 10 million rows distributed across 20 tenants, running 1,000 parallel requests over a 90-second window.

Results:

  • Test suite: 390 automated tests (232 Java unit + 48 Python + 88 integration + 14 strategy + 2 concurrency + 6 JWT E2E)
  • Integration tests: 88/88 passing across MySQL 8.0 (30), SQL Server 2022 (29), and Oracle XE 21c (29)
  • Cross-tenant leaks detected: 0
  • Cross-tenant requests blocked: 528 / 528
  • p50 latency (product lookup): ~96ms — includes DB query, network, and JSON serialization
  • p99 latency (product lookup): ~283ms
  • Agent overhead per query: ~0.8ms on cache miss, ~0ms on cache hit
  • Agent CPU overhead: 4.3% under sustained load
  • Agent GC overhead: 0.043%

The isolation number is the only one that matters for a security tool. Zero leaks under concurrent load is the bar, and it passed.

Note: CTE-heavy reporting queries run slower — p50 around 1.5 seconds for complex window function queries. This is a known area of active optimization.


Current Limitations — The Honest List

This is an early-stage solo project. Here is what does not work yet or works with caveats:

No first-class PostgreSQL support yet. MySQL, SQL Server, and Oracle are verified with the full test suite. The Python parser already supports the postgres SQL dialect, but PostgreSQL is not yet in the verified integration matrix. If you are on Postgres, hold off or test carefully and report what breaks — Postgres support is the #1 item on the roadmap.

The Python sidecar is a separate process. For applications with complex SQL, you need to run and manage the Docker container alongside your application. It is a single docker run command, but it is still an operational dependency that some teams will want to avoid.

Java 21+ only. This tool requires Java 21 or later and does not help if your stack is Node.js, Python, Go, or anything that does not run on the JVM.

No production reference customers yet. This has been tested in controlled load tests but not battle-tested in a live production environment at scale. If you try it in production, I want to hear what happens.

Stored procedures are partially covered. The outer call is filtered, but queries executed internally inside a stored procedure are not intercepted.


Why Not Just Use Postgres RLS or Hibernate Filters?

These are the first two questions any experienced developer will ask, and they are fair.

Postgres RLS is genuinely good if you are on PostgreSQL and can configure database policies. It has two practical constraints: it requires DBA access to configure and maintain, which many application teams do not have, and it locks you to PostgreSQL. If you have a mixed database environment, run on MySQL or Oracle, or cannot touch database configuration, RLS is not an option.

Hibernate @FilterDef works well for Hibernate-managed queries but has a blind spot: it does not cover native SQL queries, raw JDBC calls, or any code path that bypasses the ORM. In most real codebases, there are more of these than developers expect.

TenantWall's specific value is the combination of zero code changes, ORM-agnostic coverage, and database portability. It is not a replacement for RLS where RLS is available and appropriate. It is the right tool when you need coverage across a polyglot data layer or cannot afford to audit every query in an existing codebase.


Try It and Tell Me What Breaks

TenantWall v1.2.0 is on Maven Central now:

<dependency>
    <groupId>io.github.maksoud13</groupId>
    <artifactId>tenantwall-spring-boot-starter</artifactId>
    <version>1.2.0</version>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Full source, documentation, and test scripts are at github.com/maksoud13/tenantwall. MIT license.

If you try it on a real query that breaks — a CTE that does not rewrite correctly, a dialect it does not handle, a strategy that misbehaves — open an issue. That is exactly the kind of feedback that makes an early-stage project real. If it works for you, a GitHub star helps other developers find it.

If you are currently handling multi-tenant isolation a different way — Postgres RLS, separate schemas, Hibernate filters, or something custom — I would genuinely like to know. Drop a comment below.


TenantWall is an open-source solo project under active development. MIT license. Issues and contributions welcome.

Top comments (0)