Dynamic Data Permissions in Spring Boot: Row/Column-Level Control with MyBatis-Plus

Why Stop Hardcoding WHERE Clauses in Business Code

Data permissions differ from functional permissions (menus/buttons) because they live inside business logic, depend heavily on context, change frequently, and become chaotic with cross-table joins. The old approach of hardcoding WHERE dept_id = ? or embedding conditions in XML fails in modern multi-tenant, matrix-organization, SaaS systems.

Four Pain Points

SQL Coupling : Permission logic scattered across DAOs, WHERE conditions copied everywhere; changing one breaks three others.

Deep Context Binding : Same interface needs different visibility (sales sees own, manager sees department, director sees all). Static SQL cannot handle this without piles of if-else in service layer.

Multi-table JOIN Failures : Subqueries, alias reuse, self-joins cause dynamically appended conditions to attach to wrong tables, leading to Unknown column errors or cross-tenant data leaks (P0 incidents).

Observability Blind Spots : No unified metrics on whether conditions hit, violations blocked, or latency added.

Evolution Path

Early hardcoding → database views/stored procedures (too deployment-coupled) → AOP parsing XML (broke with native pagination) → converged on ORM Interceptor + Strategy Engine . Push permission logic down to data access layer; business code stays untouched, rule changes require zero Java modifications.

Architecture: RBAC Baseline + ABAC Dynamic Convergence

Pure RBAC (role-based) is insufficient — org changes force role rebuilds. Production uses RBAC for baseline + ABAC (attributes) for dynamic filtering .

Model Design

RBAC Baseline : Roles define base visibility scope, e.g., SELF (self), DEPT (department), GLOBAL (all).

ABAC Dynamic Attributes : Combine real-time user context (department tree path, project groups, temporary tags, time windows, data sensitivity) for secondary filtering.

Policy Expressions : Described in JSON or SpEL for flexibility. Example:

{
  "row_policy": "userId == #currentUser.id || deptId in #currentUser.deptScope",
  "col_policies": {
    "salary": "roles contains 'HR' || roles contains 'MANAGER'",
    "id_card": "roles contains 'AUDITOR' && audit_time < now()"
  }
}

Rule Distribution & Caching

Fetching rules from DB on every query would overload it. Solution: Local Caffeine + Redis two-level + Pub/Sub broadcast .

Policy center publishes changes on Redis channel data-perm:update:*.

Application instances receive broadcast, call cache.invalidate(ruleId) to clear local cache; next query fetches fresh from Redis or DB.

Version field enables incremental updates.

Cache failure falls back to DB for availability. Strong consistency not needed; AP priority with eventual consistency suffices for permissions.

Core Code: Row-Level Interception & Column-Level Masking

3.1 Row-Level Interceptor (MyBatis-Plus + JSqlParser)

MP 3.5+ provides InnerInterceptor. Extend JsqlParserSupport and override beforeQuery. Never manipulate raw SQL strings; use JSqlParser to convert to AST, then inject conditions — avoids quote and precedence nightmares.

@Slf4j
@Component
@RequiredArgsConstructor
public class DynamicDataPermInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private final PermissionRuleLoader ruleLoader;
    // ThreadLocal must be cleaned per request to prevent context leakage across thread pool reuse
    private static final ThreadLocal<PermissionContext> CONTEXT = new ThreadLocal<>();

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter,
                            RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        PermissionContext ctx = CONTEXT.get();
        if (ctx == null || ctx.isGlobalAdmin()) return;

        String originalSql = boundSql.getSql();
        DataPermissionRule rule = ruleLoader.getRule(ctx.getUserId(), ctx.getRequestUri());
        if (rule == null) return;

        // JSqlParser parse & inject condition
        String finalSql = processSelect(originalSql, rule);

        // Replace SQL safely via MetaObject (avoids reflection pitfalls across MP versions)
        MetaObject metaBoundSql = SystemMetaObject.forObject(boundSql);
        metaBoundSql.setValue("sql", finalSql);
    }

    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        DataPermissionRule rule = (DataPermissionRule) obj;
        if (!(select.getSelectBody() instanceof PlainSelect plainSelect)) return;

        FromItem fromItem = plainSelect.getFromItem();
        if (!(fromItem instanceof Table mainTable)) return;

        String alias = mainTable.getAlias() != null ? mainTable.getAlias().getName() : mainTable.getName();
        Expression whereExpr = buildPermissionExpression(alias, rule);

        if (whereExpr != null) {
            Expression originalWhere = plainSelect.getWhere();
            // Wrap with Parenthesis to prevent AND/OR precedence chaos
            plainSelect.setWhere(new Parenthesis(new AndExpression(originalWhere, whereExpr)));
        }
    }

    private Expression buildPermissionExpression(String tableAlias, DataPermissionRule rule) {
        Column col = new Column(tableAlias + "." + rule.getColumn());
        ExpressionList<Expression> values = new ExpressionList<>();
        rule.getScopeValues().forEach(v -> values.add(new StringValue(String.valueOf(v))));
        return new InExpression(col, new Parenthesis(values));
    }

    // External call: DynamicDataPermInterceptor.CONTEXT.set(ctx);
}

JsqlParserSupport

handles SQL-to- Select conversion; we directly modify PlainSelect. Parenthesis wrapping is mandatory; otherwise A AND B OR C injection scrambles execution plans.

Using MetaObject instead of ReflectUtil to modify BoundSql is more stable; MP guarantees long-term compatibility for MetaObject.

3.2 Column-Level Visibility & Masking

Don't alter SELECT * SQL for column permissions — it's messy and hurts execution plans. Production uses Jackson serialization interception to dynamically trim or mask fields during JSON serialization. Zero DB impact, rule changes need no restart.

Annotate DTO fields:

@Data
public class UserDTO {
    @DataPermissionColumn(code = "salary", visibleRoles = {"HR", "FINANCE"})
    private BigDecimal salary;

    @DataPermissionColumn(code = "phone", maskType = MaskType.MIDDLE)
    private String phone;
}

Register BeanSerializerModifier to take over serialization:

@Component
public class DataPermJsonSerializerModifier extends BeanSerializerModifier {
    @Override
    public List<BeanPropertyWriter> changeProperties(SerializationConfig config,
                                                       BeanDescription beanDesc,
                                                       List<BeanPropertyWriter> beanProperties) {
        return beanProperties.stream().map(writer -> {
            DataPermissionColumn ann = writer.getAnnotation(DataPermissionColumn.class);
            if (ann != null) {
                return new DataPermAwareWriter(writer, ann);
            }
            return writer;
        }).collect(Collectors.toList());
    }
}

In DataPermAwareWriter.serialize, read PermissionContext; if role mismatches, call jgen.writeNull() or write masked string. Note: this only applies to JSON responses. Excel exports or third-party XML integrations need separate result-set filtering logic.

Production Pitfalls: JOIN Chaos, Cache Consistency & Performance

Multi-table JOIN Pitfalls

JSqlParser's FROM parsing may pick wrong table (first table not necessarily the target) when facing subqueries, views, or multi-joins. Blind injection causes Unknown column or wrong data.

Only intercept entities annotated with @DataPermissionTable — whitelist filtering; others pass through.

At startup, scan TableInfoHelper to build entity-to-table/alias mapping dictionary.

Complex reports (3-5+ joins) should avoid interceptor; use post-query in-memory filtering or pre-aggregated wide tables in data warehouse. Interceptor suits standard CRUD, not OLAP.

Add fallback switch perm.injection.fail-fast=false; parse errors log WARN and allow query — core transaction chains must not fail due to permission parsing.

Hot Update Consistency

Full cache refresh spikes CPU (GC alerts during load tests). Switched to Incremental Diff + Canary Push .

Policy center pushes only changed ruleId; app invalidates single key.

Spring @Scheduled every 5 minutes as safety net against Pub/Sub message loss.

Running 1+ years with zero permission-inconsistency complaints.

Performance Overhead

Baseline: interceptor disabled. With row-level + column-level enabled, extra latency ~1.5–3 ms per request. Main cost: JSqlParser AST building and Jackson serialization traversal. After cache hit rate >90%, overhead becomes imperceptible.

Optimization 1: Guava cache for SQL -> AST; same template parses once.

Optimization 2: Interceptor pre-check — skip methods without annotations or whitelisted URLs.

At 10k QPS, P99 latency increase <2%, CPU up ~4%; fully acceptable.

Real-World SaaS Customer Isolation Scenario

Requirements

Regular sales: view only self-created customers.

Department manager: view own department + sub-departments.

Temporary grant: Sales A accesses Dept B data for 48 hours, auto-expires.

Rule Config (YAML Simplified)

data-perm:
  rules:
    sales:
      table: customer
      column: creator_id
      scope: SELF
    manager:
      table: customer
      column: dept_id
      scope: DEPT_TREE
    temp_grant:
      table: customer
      column: dept_id
      scope: CUSTOM_LIST
      ttl: 48h

Controller Passes Context

@GetMapping("/api/customer/list")
public Page<CustomerVO> list(HttpServletRequest req) {
    PermissionContext ctx = new PermissionContext();
    ctx.setUserId(TokenUtil.getCurrentUserId());
    ctx.setDeptTreePath(UserDeptCache.getPath(TokenUtil.getCurrentDeptId()));
    // Temporary grants via Redis ZSet, score controls expiry
    ctx.setTempGrantDepts(tempPermClient.getValidDepts(ctx.getUserId()));

    DynamicDataPermInterceptor.CONTEXT.set(ctx);
    try {
        return customerService.page(new Page<>(1, 10));
    } finally {
        DynamicDataPermInterceptor.CONTEXT.remove();
    }
}

Interceptor rewrites SQL to:

SELECT * FROM customer
WHERE (dept_id IN (101, 102) OR dept_id IN (999))
AND tenant_id = 'T001'
ORDER BY create_time DESC

Business code stays clean; permission logic enforced at bottom layer.

Veteran Advice: Don't Chase Perfect, Seek Stability First

Zero Intrusion is Baseline . Business code only passes intent (context); never build conditions in Service.

Default Deny (Deny-by-Default) . Interceptor failure throws exception or returns empty; never silently allow. Over-permission is far worse than under-permission.

Observability Must Keep Up . Log traceId, ruleId, injected SQL, latency to ELK. Without logs, troubleshooting over-permission is blind.

No RPC in Interceptor . Fetch rules from local cache only; network jitter would stall all queries.

String Concatenation SQL is Red Line . 100% JSqlParser API. One misplaced bracket = SQL injection vulnerability or syntax error.

Don't Force Interceptor for Complex Reports . Post-query filtering, wide-table pre-aggregation — whatever is stable. Interceptor fits standard CRUD, not OLAP.

Regularly Clean Temporary Grants . ZSet expiry is logical; cache must also scan periodically to prevent permission bloat.

Data permission has no one-shot solution. It's a continuous governance engineering system. Stabilize the foundation first: hot-updatable rules, traceable failures, then iterate. Don't start with OPA/Rego; first ensure ThreadLocal cleanup, cache avalanche prevention, and correct SQL generation. Clear boundaries let business run confidently.