Recommended Free Tools
Search code that serves different roles and also accepts optional filters often concatenates everything into one WHERE clause, and that is where visibility rules leak. The design in Paolo’s DEV Community article, posted September 26, 2026, separates two questions: what a user is allowed to see, and what the user asked to find. One visibility strategy, chosen by role, is required for every search. Optional filter contributors add their own predicates. A builder combines everything with bound parameters and refuses to produce a query that has no visibility decision. The example is written in Java with Spring JDBC against SQL Server.
Why an OR in a filter can undo role-based visibility
Start with the failure the design is meant to prevent. Suppose a local officer’s visibility rule is unit.id = :unitId, and a region filter is appended as a plain string: unit.id = :regionId OR unit.parent_id = :regionId. Simplified to show the mechanism, the combined WHERE clause becomes:
WHERE unit.id = :unitId AND unit.id = :regionId OR unit.parent_id = :regionIdSQL evaluates AND before OR, so this reads as (unit.id = :unitId AND unit.id = :regionId) OR unit.parent_id = :regionId. The second branch carries no visibility check at all. The article reports that its local-officer case returned documents from another region. Read on its own, the filter looks harmless, which is why the protection has to live in the builder rather than in code review.
Two decisions, two families of strategies
The design treats access and search as separate axes. The article puts each one into its own family of strategies:
#1 Best Overall
- Visibility strategy. Exactly one per search, selected by the user’s role. It is required, so the search cannot be built without one.
- Filter contributor. Zero or more per search, one per optional criterion. Each contributes its own predicate, its parameters and, where needed, joins or CTEs. No contributor owns the whole query.
The author puts the pattern this way: “The pattern is Strategy, used twice: one family of strategies decides what a user may see, the other what the user asked for, and neither writes the whole query.”
The article also frames the problem as a question of invariants: “A search with optional filters and role-based visibility is application logic, and one of its invariants is a security boundary.” Treating visibility as an invariant rather than as one more optional filter is what keeps it from being dropped.
The visibility policy by role
The example defines five roles. Each role’s rule becomes one visibility strategy.
| Role | What the visibility strategy allows |
|---|---|
| LOCAL_OFFICER | Their own unit. |
| REGIONAL_SUPERVISOR | The region and its local offices, plus chartered units only during an active, explicit delegation. |
| NATIONAL_ADMIN | All documents. |
| AUDITOR | Approved or archived documents across units. |
| DELEGATE | Only units with an active delegation. |
These role names belong to the example. Your own policy will have different roles, but the same split applies: a rule per role for what may be seen, and nothing else in that rule.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →The ten optional filters
The example adds ten filters that a user can activate on top of the visibility scope:
- Region
- Unit
- Type
- Status
- Date range
- Attachments
- Author
- Title
- Tag
- Overdue
How a search is assembled
A search runs through the same sequence each time, whatever filters are active:
- Resolve the user’s scope from their role and any delegations.
- Create one search context. It holds a single resolved date, so every date-dependent rule in the request reads the same value.
- Apply exactly one visibility strategy for the user’s role.
- Apply each filter contributor whose criterion is active in the request.
- Let the builder compose the joins, CTEs, predicates, parameters, selected columns and ordering into one statement.
Because the active filters change from request to request, each combination of filters produces its own SQL text. The article contrasts this with a single fixed statement that tries to handle every optional criterion at once.
Failing closed for roles without a visibility rule
The registry that maps roles to visibility strategies rejects any role that has no strategy, and the builder refuses to produce a query when no visibility strategy has made a decision. The article tests this with an unhandled EXTERNAL_REVIEWER role. The composed approach threw an error instead of returning every document. A missing rule becomes a visible failure in development, not a silent widening of access in production.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Security details in the builder
Bind values, and treat the fragment check as a tripwire
Values travel as bound parameters and never as concatenated text. The builder also rejects certain characters inside fragments. The article describes this check as a tripwire that catches accidental mistakes, not as proof against unsafe SQL. Bound parameters are the protection; the character check only surfaces mistakes earlier.
Map sort names through a whitelist
SQL identifiers such as column names cannot be bound as parameters. The sort field is therefore chosen from a fixed list of names, each mapped to a known column, and a user-supplied string is never placed into the ORDER BY clause.
Parenthesize every fragment
Each fragment, especially one containing OR, is wrapped in parentheses before it is ANDed with the others. The leak at the top of this article came from operator precedence, so this is the direct fix.
Reject conflicting parameter names
Two contributors may use the same parameter name only when they pass the same value. The builder rejects a duplicate name whose new value differs, which stops one contributor from silently overwriting a value another contributor depends on.
Resolve “today” once
The search context stores one resolved date. The visibility scope and the overdue filter both read that value, so a request evaluated just before and just after midnight cannot apply two different dates.
Escape LIKE wildcards
Binding a value does not change how LIKE reads wildcard characters. A user who types % or _ into a title search widens the match unless the pattern is escaped. The article’s SQL Server example escapes %, _ and [ in patterns.
Select sensitive columns only where they are needed
The article selects author email only in the national-admin scope, rather than fetching it for every user and hiding it later. A value that is never fetched for other roles cannot be exposed by a later mistake in the display layer.
Performance: measure rather than assume
Generating a separate SQL text for each filter combination can look like a cost. The article does not claim this design is faster. It notes that SQL Server 2025’s Optional Parameter Plan Optimization handles optional predicates through plan variants, and it says performance with ten optional predicates should be measured rather than assumed. Plan caching depends on your server version and configuration, so check the plans your own workload produces.
Alternatives compared
The article compares the options on five axes: whether predicates form a structured tree that handles precedence, control over SQL and database-specific features, ORM and entity requirements, where authorization is enforced and how visible that is during review, and operational or license cost.
| Approach | Predicates form a structured tree | SQL and dialect control | Entity requirement | Where authorization lives | Cost noted by the article |
|---|---|---|---|---|---|
| Parenthesized direct SQL (the example’s approach) | Only if each fragment is parenthesized by hand or by the builder | Full | None; Spring JDBC with records | In the query builder, visible in the generated SQL | Not stated |
| Spring Data Specifications / JPA Criteria API | Yes; predicates compose structurally, which prevents this precedence leak | Standard Criteria has limitations for the example’s CTE needs | JPA entities required | Not stated | Not stated |
| jOOQ | Yes; conditions are rendered from an AST | CTEs, window functions and SQL Server dialect features supported | Code generation step added | Not stated | Commercial license required for SQL Server use |
| SQL Server Row-Level Security | Not stated; a filter predicate applies to every query | Enforced in the database, including for ad-hoc reports | Not stated | In the database; session context must be set on connection checkout | Not stated |
The article says jOOQ would be the first option it evaluates for a new project. It treats Row-Level Security as a second line of defense, because a database-side rule makes visibility harder to see in application SQL and harder to test.
When parent and child conditions stop being enough
The example’s hierarchy check, unit.parent_id = :regionId, assumes a three-level hierarchy. If units can nest deeper than that, a single parent comparison misses descendants. The article names a closure table or a recursive CTE as the ways to handle descendant lookup in deeper trees. Either one moves the hierarchy logic out of the simple parent comparison and into a structure built for it.
Choosing the abstraction for your problem
The article’s guidance is conditional, and the conditions are the useful part:
Free tools Windows power users keep installed
One-click scans. No signup required.
- One role, a few filters and a small internal audience: a straightforward parenthesized query with tests can be enough. The tests should check absence as well as presence, meaning which documents a user must not see, not only the ones they should.
- Many visibility cases, filters that keep arriving, or a leak with serious consequences: the composed design earns the extra structure it adds.
What the article establishes, and what it does not
Paolo’s article presents a demo and a design proposal. It does not prove that this architecture is always the safest or the fastest option. Its test figures are the author’s own reported results, not independently reproduced numbers:
- The authorization matrix covers 21 documents and 7 users, run against both implementations for 294 cases.
- Characterization testing compares both implementations for 20 criteria combinations for every user.
The demo’s stated environment is Java 21, Spring Boot 4.1.1, Spring Framework 7.0.9, Flyway 12.4.0, Testcontainers 2.0.5, Microsoft JDBC Driver for SQL Server 13.4.0 and SQL Server 2025 CU9. These are the versions the article names for its example, not a statement about the latest releases of each component.
The demo uses Spring JDBC with NamedParameterJdbcTemplate and records, with no JPA.
Quick Recap
|
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute
