Implement Role-Based Data Permissions in MyBatis-Plus with Custom Interceptors

Background

In many projects a requirement arises to limit data visibility based on the current user's role. Two approaches exist: embed the filter logic in each query method, or use a global interceptor that modifies the SQL before execution. The interceptor works globally, so annotations are used to mark the methods that need data‑permission handling.

Create a custom annotation class.

Implement an interceptor by extending JsqlParserSupport and implementing InnerInterceptor, overriding the query method.

Write a handler that builds the WHERE clause according to the user's role.

Register the interceptor in the MyBatis‑Plus plugin configuration.

Basic Implementation

Custom Annotation

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

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface UserDataPermission {}

Interceptor

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
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;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class MyDataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private MyDataPermissionHandler dataPermissionHandler;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) {
            return;
        }
        PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        mpBs.sql(this.parserSingle(mpBs.sql(), ms.getId()));
    }

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

    private void setWhere(PlainSelect plainSelect, String whereSegment) {
        Expression sqlSegment = this.dataPermissionHandler.getSqlSegment(plainSelect, whereSegment);
        if (null != sqlSegment) {
            plainSelect.setWhere(sqlSegment);
        }
    }
}

Handler (Basic Version)

import cn.hutool.core.collection.CollectionUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.HexValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.PlainSelect;
import org.apache.ibatis.session.ResultHandler;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

@Slf4j
public class MyDataPermissionHandler {
    /**
     * Build the data‑permission WHERE clause.
     */
    @SneakyThrows(Exception.class)
    public Expression getSqlSegment(PlainSelect plainSelect, String whereSegment) {
        Expression where = plainSelect.getWhere();
        if (where == null) {
            where = new HexValue(" 1 = 1 ");
        }
        log.info("Starting permission filter, where: {}, mappedStatementId: {}", where, whereSegment);
        String className = whereSegment.substring(0, whereSegment.lastIndexOf('.'));
        String methodName = whereSegment.substring(whereSegment.lastIndexOf('.') + 1);
        Table fromItem = (Table) plainSelect.getFromItem();
        Alias fromItemAlias = fromItem.getAlias();
        String mainTableName = fromItemAlias == null ? fromItem.getName() : fromItemAlias.getName();
        Method[] methods = Class.forName(className).getMethods();
        for (Method m : methods) {
            if (Objects.equals(m.getName(), methodName)) {
                UserDataPermission annotation = m.getAnnotation(UserDataPermission.class);
                if (annotation == null) {
                    return where;
                }
                // Example: only allow the creator's own data
                User user = SecurityUtils.getUser();
                EqualsTo eq = new EqualsTo();
                eq.setLeftExpression(new Column(mainTableName + ".creator_code"));
                eq.setRightExpression(new StringValue(user.getUserCode()));
                return new AndExpression(where, eq);
            }
        }
        // No permission – return a condition that yields no rows
        where = new HexValue(" 1 = 2 ");
        return where;
    }
}

Registering the Interceptor

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    MyDataPermissionInterceptor dataPermissionInterceptor = new MyDataPermissionInterceptor();
    dataPermissionInterceptor.setDataPermissionHandler(new MyDataPermissionHandler());
    interceptor.addInnerInterceptor(dataPermissionInterceptor);
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
    return interceptor;
}

If the project already configures a MybatisPlusInterceptor bean, simply add the custom interceptor to the existing list to avoid affecting other settings.

Advanced Implementation

The advanced version adds role‑based scope handling (ALL, DEPT, MYSELF) and supports queries that are not defined in the mapper layer.

Scope Enum

public enum DataScope {
    ALL("ALL"),
    DEPT("DEPT"),
    MYSELF("MYSELF");
    private String name;
}

Role Enum

public enum DataPermission {
    DATA_MANAGER("数据管理员", "DATA_MANAGER", DataScope.ALL),
    DATA_AUDITOR("数据审核员", "DATA_AUDITOR", DataScope.DEPT),
    DATA_OPERATOR("数据业务员", "DATA_OPERATOR", DataScope.MYSELF);
    private String name;
    private String code;
    private DataScope scope;
    // static helpers omitted for brevity
}

Handler (Advanced)

public class MyDataPermissionHandler {
    private RemoteRoleService remoteRoleService;
    private RemoteUserService remoteUserService;

    @SneakyThrows(Exception.class)
    public Expression getSqlSegment(PlainSelect plainSelect, String whereSegment) {
        remoteRoleService = SpringUtil.getBean(RemoteRoleService.class);
        remoteUserService = SpringUtil.getBean(RemoteUserService.class);
        Expression where = plainSelect.getWhere();
        if (where == null) {
            where = new HexValue(" 1 = 1 ");
        }
        String className = whereSegment.substring(0, whereSegment.lastIndexOf('.'));
        String methodName = whereSegment.substring(whereSegment.lastIndexOf('.') + 1);
        Table fromItem = (Table) plainSelect.getFromItem();
        Alias alias = fromItem.getAlias();
        String mainTable = alias == null ? fromItem.getName() : alias.getName();
        Method[] methods = Class.forName(className).getMethods();
        for (Method m : methods) {
            if (Objects.equals(m.getName(), methodName)) {
                UserDataPermission ann = m.getAnnotation(UserDataPermission.class);
                if (ann == null) return where;
                User user = SecurityUtils.getUser();
                Set<String> roleTypes = remoteRoleService.currentUserRoleType();
                DataScope scope = DataPermission.getScope(roleTypes);
                switch (scope) {
                    case ALL:
                        return where;
                    case DEPT:
                        List<String> deptUsers = remoteUserService.listUserCodesByDeptCodes(user.getDeptCode());
                        ItemsList deptList = new ExpressionList(deptUsers.stream().map(StringValue::new).collect(Collectors.toList()));
                        InExpression inExpr = new InExpression(new Column(mainTable + ".creator_code"), deptList);
                        return new AndExpression(where, inExpr);
                    case MYSELF:
                        EqualsTo eq = new EqualsTo();
                        eq.setLeftExpression(new Column(mainTable + ".creator_code"));
                        eq.setRightExpression(new StringValue(user.getUserCode()));
                        return new AndExpression(where, eq);
                    default:
                        break;
                }
            }
        }
        return new HexValue(" 1 = 2 ");
    }
}

Usage

Annotate mapper methods with @UserDataPermission to activate the permission filter.

@UserDataPermission
List<CustomerAllVO> selectAllCustomerPage(IPage<CustomerAllVO> page, @Param("customerName") String customerName);

For services that call BaseMapper methods directly, create an intermediate mapper interface that extends BaseMapper and re‑declare the needed methods with the annotation, leaving the original mapper unchanged.

Key points to remember:

Ensure the interceptor is added to the MyBatis‑Plus plugin configuration.

The data‑filtering field used in the examples is creator_code; other fields such as dept_code can be substituted as needed.