Introduction: Why Authorization Matters

Imagine you’ve discovered an internal bank API.

This API, when called, returns the last N transactions of the logged-in customer.

• If you’re logged in as User A, it should only return User A’s transactions.
• Even if you know User B’s account number, calling the API with your own session must not return their data.

This is possible because authorization checks are enforced on top of the query.

Now, suppose the bank launches chat support in its mobile app.

That new feature has its own APIs, with slightly different request/response structures.

A new developer creates a separate service for it. But to make it secure, they now have to look up and re-implement the same authorization criteria that already exist in the web APIs.

If they forget or apply it incorrectly, sensitive data may leak.

Example Query

-- Without authorization: ❌ risk of data leak
SELECT * FROM transactions WHERE account_number = :accountNumber;

-- With authorization: ✅ secure
SELECT * FROM transactions
WHERE account_number = :accountNumber
  AND user_id = :currentUserId;

The difference is small — just one extra filter — but missing it completely changes the security of the system.

The Bigger Picture: Multiple Queries in Enterprise Apps

In an enterprise application, there aren’t just one or two queries like this.

You’ll find dozens, sometimes hundreds, across repositories:

• Paginated queries for dashboards – e.g., “show my last 20 transactions.”
• Detail queries for single records – e.g., “fetch this account by ID.”
• Search queries with filters – e.g., “find all active loans with status = OPEN.”
• Export/Download queries – similar to dashboards, but without pagination.
• Reporting queries with aggregates/joins – e.g., “show total transactions per branch for my region.”

So far, we’ve been looking at queries from the perspective of a single type of user — the account holder. Even with just one role, every query must correctly enforce authorization to prevent data leaks.

But in reality, a table like transactions is accessed by multiple roles, each with different rules:

• Bank Manager – sees all transactions in their branch.
• Relationship Manager – sees only transactions of clients assigned to them.
• Cashier – may see only deposits/withdrawals for teller operations.

Each role comes with different authorization criteria for every query.

Now imagine: a change in rules for any one role must be applied consistently across dozens of queries — and confidence that nothing breaks or leaks is very low if the rules are scattered.

The Problem: Scattered Security in Queries

In typical enterprise applications, scattered authorization logic creates multiple risks:

1. Inconsistent enforcement – Some queries correctly filter data, while others forget to apply authorization rules, leading to potential data leaks.
2. Rule duplication – Each developer may re-implement the same rules in different queries, making maintenance harder and error-prone.
3. High cognitive load – Developers must remember complex rules for every query, increasing the chance of mistakes.
4. Difficulty in auditing – It’s hard to track which queries enforce which rules, making compliance and auditing challenging.
5. Scalability issues – As new roles, features, or queries are added, the risk of missing authorization checks grows exponentially.

Even small differences, like paginated vs non-paginated queries or minor variations in filters, can become sources of security holes if rules are implemented manually in multiple places.

The Solution: A Secure Query Builder

To enforce security consistently and cleanly, we can centralize authorization and business filters in a single query builder.

Here’s a simplified pseudo-code illustrating the concept:

class TransactionSecureQueryBuilder {

    // Main query builder function
    public String buildQuery(String entity, UserContext user, Map<String, Object> filters) {
        String query = "SELECT * FROM " + entity;

        // Apply authorization first
        String authClause = getAuthorizationClause(user);
        query += " WHERE " + authClause;

        // Apply business filters
        query += " AND " + getFilterClause(filters);

        return query;
    }

    // Returns SQL snippet for role-based authorization
    private String getAuthorizationClause(UserContext user) {
        switch (user.getRole()) {
            case "ACCOUNT_HOLDER":
                return "user_id = " + user.getUserId();
            case "BANK_MANAGER":
                return "branch_id = " + user.getBranchId();
            case "RELATIONSHIP_MANAGER":
                return "client_id IN (" + user.getAssignedClientIds() + ")";
            case "CASHIER":
                return "transaction_type IN ('DEPOSIT','WITHDRAWAL')";
            default:
                throw new IllegalArgumentException(
                    "Unrecognized role: " + user.getRole()
                );
        }
    }

    // Returns SQL snippet for business filters
    private String getFilterClause(Map<String, Object> filters) {
        String clause = "";
        clause += "name LIKE '%" + filters.get("name") + "%'";
        clause += " AND status = '" + filters.get("status") + "'";
        return clause;
    }
}

Usage Example

// Example usage of TransactionSecureQueryBuilder

UserContext user = getCurrentUser(); // e.g., ACCOUNT_HOLDER
Map<String, Object> filters = Map.of(
    "name", "Alice",
    "status", "ACTIVE"
);

TransactionSecureQueryBuilder queryBuilder = new TransactionSecureQueryBuilder();
String finalQuery = queryBuilder.buildQuery("transactions", user, filters);

System.out.println(finalQuery);
// Output (example):
// SELECT * FROM transactions WHERE user_id = 123 AND name LIKE '%Alice%' AND status = 'ACTIVE'

With this query builder, developers don’t need to worry about enforcing authorization manually. No matter what filters the developer provides — for example, searching by name and status — the query builder automatically adds the authorization criteria based on the user’s role.

• If the user is not allowed to access certain data according to their role (like a relationship manager trying to access a transaction of a client not assigned to them), the query will never return unauthorized data.
• Developers always use this centralized query builder — even for “find all” queries. In that case, no business filters are passed, but the authorization filter is still applied automatically.
• This ensures that all queries consistently enforce security rules, and authorization logic is maintained in a single place rather than scattered across the codebase.

In short, the builder handles both business search criteria and security enforcement, making it impossible to accidentally bypass authorization.

Security Is Enforced Everywhere

This approach ensures that authorization is never optional and is applied uniformly across all types of operations:

• Search queries: Authorization rules are always applied on top of the business filters (e.g., name, status).
• Update and delete operations: These are protected because the system first fetches the record using the secure query builder, which applies the authorization filter. If the user is not allowed to see the record, they will never be able to update or delete it.

// Update example
long recordId = 42;
User user = getCurrentUser();

SecureQueryBuilder queryBuilder = new SecureQueryBuilder();

// Authorization filter is applied here automatically
Record record = queryBuilder.query("employees", user, Map.of("id", recordId));

if (record != null) {
    update(record); // Safe, because user was authorized to fetch this record
}

• All data access must go through the secure query builder, ensuring that authorization is always applied automatically.
• This ensures consistent security across all operations—read, update, delete, or even “find all.”

Why This Design Works

• Centralized authorization enforcement – for all roles, role-based access checks are applied through the query builder, and since rules exist in one place, any change is automatically reflected everywhere.
• Business filters are optional – developers can add domain-specific filters (e.g., name, status) on top of the authorization clause.
• Consistent query generation – the same logic is applied whether fetching one record, searching, updating, or deleting.
• Clean and maintainable code – the query builder centralizes both authorization and filtering logic, giving developers confidence to make changes without risking inconsistent security rules.

Final Thought

Security isn’t about sprinkling filters into queries — it’s about baking them into the recipe.

A secure query builder makes authorization unskippable by design, so developers focus on features, not remembering rules.

When security is structural, leaks aren’t just less likely — they’re impossible by accident.

Implementing a Secure Query Builder in Different Languages

Most modern frameworks provide ways to programmatically build queries while keeping security rules centralized.

Java Options

• JPA Criteria API
  • Fully type-safe, dynamic query builder.
  • Best for standard enterprise filtering and sorting.
• Blaze Persistence
  • Advanced query building with dynamic joins, subqueries, and fluent API.
  • Ideal for complex queries requiring centralized authorization and business filters.
• jOOQ
  • SQL-centric, programmatic query generation with full control over conditions and joins.
  • Supports reusable DSL functions.

Node.js / JavaScript Options

• Sequelize – ORM with dynamic queries and centralized role-based authorization support.
• TypeORM – QueryBuilder for SQL-like control with centralized authorization logic.

POC – Proof Of Control

At the end, I want to share a POC I created using Blaze Persistence in Java. It demonstrates an event management scenario where different types of users fetch event records according to their roles:

• Super Admin – can fetch all events.
• Event Manager – can fetch only events belonging to their tenant.
• Attendee – can fetch only events for which they have a ticket.
• Ticket Vendor – can fetch only events assigned to their vendor booth.

The POC also supports sorting and pagination, providing a practical example of how a centralized, role-based query builder can be implemented in a real-world application.

Link → https://github.com/Hardikraja/row-level-data-security