Developing applications often requires restricting data visibility based on user roles. For instance, a manager might view department-wide records while a standard employee only sees their own entries. While filtering logic can be implemented directly within Service methods, this approach becomes cumbersome if requirements change mid-cycle or if multiple enterfaces require similar constraints.

A more maintainable solution involves intercepting SQL execution before it reaches the database. By utilizing MyBatis Plus's extension mechanism, we can dynamically modify the WHERE clause of SELECT statements to enforce data boundaries. This strategy ensures global applicability without cluttering business logic.

Core Architecture

The implementation relies on three primary components:

1. Custom Annotation: Identifies Mapper methods requiring data filtering.
2. Interceptor Class: Implements InnerInterceptor to manipulate SQL strings during runtime.
3. Handler Logic: Determines specific SQL fragments (e.g., user ID, department IDs) based on security context.

Component Implementation

1. Defining the Annotation

Create a marker annotation to tag specific operations that need permission enforcement.

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataScoped {
}

2. Building the Interceptor

This class extends JsqlParserSupport to parse and modify the generated SQL. It checks for the custom annotation before applying filters.

import com.baomidou.mybatisplus.core.plugins.InnerInterceptor;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectBody;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.List;

public class DataScopeInterceptor extends JsqlParserSupport implements InnerInterceptor {

    private final DataScopeResolver resolver;

    public DataScopeInterceptor(DataScopeResolver resolver) {
        this.resolver = resolver;
    }

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter,
                            RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        
        // Skip interception if explicitly ignored by configuration
        String methodId = ms.getId();
        // Check if logic should proceed
        
        // Retrieve modified SQL
        com.baomidou.mybatisplus.core.toolkit.PluginUtils.MPBoundSql mpBs = 
            com.baomidou.mybatisplus.core.toolkit.PluginUtils.mpBoundSql(boundSql);
        mpBs.sql(parserSingle(mpBs.sql(), methodId));
    }

    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        SelectBody body = select.getSelectBody();
        if (body instanceof PlainSelect) {
            addPermissionFilter((PlainSelect) body, sql);
        } else if (body instanceof SetOperationList) {
            List<SelectBody> selects = ((SetOperationList) body).getSelects();
            selects.forEach(s -> addPermissionFilter((PlainSelect) s, sql));
        }
    }

    private void addPermissionFilter(PlainSelect plainSelect, String sqlContext) {
        Expression filterClause = resolver.buildFilterClause(plainSelect, sqlContext);
        if (filterClause != null && plainSelect.getWhere() != null) {
            plainSelect.setWhere(new net.sf.jsqlparser.expression.operators.conditional.AndExpression(
                plainSelect.getWhere(), filterClause));
        } else if (filterClause != null) {
            plainSelect.setWhere(filterClause);
        }
    }
}

3. Resolver Logic

The resolver extracts the current user's identity and determines the appropriate SQL fragment. It supports different scope types such as 'All', 'Department', or 'Self'.

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.PlainSelect;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

@Slf4j
@Component
public class DataScopeResolver {

    public Expression buildFilterClause(PlainSelect select, String mappedStatementId) {
        try {
            // Extract table information
            Table fromTable = (Table) select.getFromItem();
            Column column = new Column(fromTable.getName() + ".creator_code");
            
            // Parse method details from statement ID
            String[] parts = mappedStatementId.split("\\.");
            String className = parts[0];
            String methodName = parts[parts.length - 1];
            
            // Check for annotation
            Method targetMethod = Class.forName(className).getMethod(methodName);
            if (!targetMethod.isAnnotationPresent(DataScoped.class)) {
                return null;
            }

            // Get User Context
            UserInfo currentUser = SecurityContextHolder.getCurrentUser();
            List<String> roleCodes = currentUser.getRoles();

            DataRange range = determineScope(roleCodes);

            switch (range) {
                case ALL:
                    return null; // No restriction needed
                case SELF:
                    EqualsTo eq = new EqualsTo();
                    eq.setLeftExpression(column);
                    eq.setRightExpression(new StringValue(currentUser.getUserCode()));
                    return eq;
                case DEPT:
                    List<String> deptUsers = fetchDeptUserCodes(currentUser.getDeptId());
                    InExpression in = new InExpression(column, new ExpressionList(deptUsers.stream().map(StringValue::new).toList()));
                    return in;
                default:
                    return new HexValue(" 1 = 2 "); // Force empty result set
            }
        } catch (Exception e) {
            log.error("Data scope resolution failed", e);
            return null;
        }
    }

    private DataRange determineScope(List<String> roleCodes) {
        // Logic to map roles to ranges
        if (roleCodes.contains("ADMIN")) return DataRange.ALL;
        if (roleCodes.contains("MANAGER")) return DataRange.DEPT;
        return DataRange.SELF;
    }
    
    private List<String> fetchDeptUserCodes(String deptId) {
        // Mock lookup service call
        return Arrays.asList("user1", "user2");
    }
}

enum DataRange { ALL, SELF, DEPT }

Configuration

Register the interceptor within the Spring Boot application context. Ensure it is added to the MybatisPlusInterceptor chain.

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(DataScopeResolver resolver) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        
        // Register custom data scope plugin
        interceptor.addInnerInterceptor(new DataScopeInterceptor(resolver));
        
        // Standard pagination support
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(com.baomidou.mybatisplus.extension.plugins.handler.DbType.MYSQL));
        
        return interceptor;
    }
}

Handling Service Layer Queries

Standard MyBatis Plus generic methods (e.g., baseMapper.selectList(...)) execute SQL dynamically. To apply annotations there without modifying existing implementations, define an intermediary interface that extends BaseMapper.

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Param;

public interface ScopedBaseMapper<T> extends BaseMapper<T> {

    @DataScoped
    @Override
    T selectOne(@Param("ew") QueryWrapper<T> wrapper);

    @DataScoped
    @Override
    List<T> selectList(@Param("ew") QueryWrapper<T> wrapper);

    @DataScoped
    <E extends IPage<T>> E selectPage(E page, @Param("ew") QueryWrapper<T> wrapper);
    
    // Override other necessary base methods with @DataScoped annotation
}

By having your Mapper implementation extend ScopedBaseMapper instead of directly extending BaseMapper, you inherit these annotated methods, ensuring coverage across standard CRUD operations without repetitive code.

Usage Example

Apply the @DataScoped annotation to any Mapper method where data isolation is required.

// Mapper Interface
public interface CustomerMapper extends ScopedBaseMapper<Customer> {

    @DataScoped
    List<CustomerVO> findActiveCustomers(Page<CustomerVO> page, @Param("status") String status);
}

// Controller/Service calls will automatically enforce the SQL restriction defined in the interceptor

Key Considerations

• Performance: Parsing SQL adds overhead. Test thoroughly under load.
• Complexity: Complex joins or non-standard SQL may require manual handling within the handler logic.
• Caching: Be aware of query cache invalidation rules when dynamic filtering is applied.
• Security: Do not rely solely on client-side annotations; verify authorization at the Service level for write operations.