Implementing Fine-Grained Data Permission Control with MyBatis Interceptors

1. Usage

Data permission control requires filtering the queried data. The least intrusive approach is to modify existing business SQL using MyBatis or a database connection pool interceptor. After the interceptor logic is completed, only a small amount of marker code is needed in the business layer, leaving legacy logic largely untouched and keeping new development focused on core business.

Because many different connection pool types are used in the department, slicing logic is hard to implement quickly. Since SQL concatenation is mainly done via MyBatis or direct Java string concatenation, we adopt the MyBatis slicing method. By adding an annotation to the mapper interface—specifying the permission type, table name, and column name—the interface’s data permission can be controlled.

Since the same mapper method may be called in multiple places—some requiring data permission control and others not—we introduce a ThreadLocal‑based rule propagation mechanism to apply permissions only when needed.

The permission rule format is:

limitRuleCode1(table1.column1,table2.column2);limitRuleCode2(table3.column3,table4.column4)

Example:

enterprise(channel.enterprise_code);account(table.column);channel(table3.id)

The context propagation utility class is shown below; it uses a callback to ensure the ThreadLocal is cleared after use.

public class DataAuthContextUtil {
    /** Not convenient to use annotations, set data rule directly */
    private static ThreadLocal<String> useDataAuth = new ThreadLocal<>();

    /** Some SQL only needs data permission in certain cases */
    public static <T> T executeSqlWithDataAuthRule(String rule, Supplier<T> supplier) {
        try {
            useDataAuth.set(rule);
            return supplier.get();
        } finally {
            useDataAuth.remove();
        }
    }

    /** Get current data permission rule */
    public static String getUseDataAuthRule() {
        return useDataAuth.get();
    }
}

2. Slice Implementation Process

3. Other Technical Details

(1) Retrieve Original SQL in Interceptor

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import reactor.util.function.Tuple2;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Component
@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
@Slf4j
public class DataAuthInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {
            MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
            BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
            String sql = boundSql.getSql();
            // permission logic would be applied here
        } catch (Exception e) {
            log.error("Data permission addition error, current SQL lacks permission restriction!", e);
            throw e;
        }
        return invocation.proceed();
    }
}

(2) Add Permission Items to Original SQL

Use Druid's built‑in AST parser to modify the SQL:

/**
 * Write permission limits into SQL
 * @param sql original SQL
 * @param tableAuthMap key:table value1:column value2:set of permission values
 * @return modified SQL
 */
public static StringBuilder addAuthLimitToSql(String sql, Map<String, Tuple2<String, Set<String>>> tableAuthMap) {
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, "mysql");
    StringBuilder authSql = new StringBuilder();
    for (SQLStatement stmt : stmtList) {
        stmt.accept(new MySqlASTVisitorAdapter() {
            @Override
            public boolean visit(MySqlSelectQueryBlock x) {
                SQLTableSource from = x.getFrom();
                Set<String> tableList = new HashSet<>();
                getTableList(from, tableList);
                for (String tableName : tableList) {
                    if (tableAuthMap.containsKey(tableName)) {
                        x.addCondition(tableName + " in (...omitted)");
                    }
                }
                return true;
            }
        });
        authSql.append(stmt);
    }
    return authSql;
}

private static void getTableList(SQLTableSource from, Set<String> tableList) {
    if (from instanceof SQLExprTableSource) {
        SQLExprTableSource tableSource = (SQLExprTableSource) from;
        String name = tableSource.getTableName().replace("`", "");
        tableList.add(name);
        String alias = tableSource.getAlias();
        if (StringUtils.isNotBlank(alias)) {
            tableList.add(alias.replace("`", ""));
        }
    } else if (from instanceof SQLJoinTableSource) {
        SQLJoinTableSource join = (SQLJoinTableSource) from;
        getTableList(join.getLeft(), tableList);
        getTableList(join.getRight(), tableList);
    } else if (from instanceof SQLSubqueryTableSource) {
        SQLSubqueryTableSource sub = (SQLSubqueryTableSource) from;
        tableList.add(sub.getAlias().replace("`", ""));
    } else if (from instanceof SQLLateralViewTableSource) {
        log.warn("SQLLateralView not processed");
    } else if (from instanceof SQLUnionQueryTableSource) {
        log.warn("union not processed");
    } else if (from instanceof SQLUnnestTableSource) {
        log.warn("Unnest not processed");
    } else if (from instanceof SQLValuesTableSource) {
        log.warn("Values not processed");
    } else if (from instanceof SQLWithSubqueryClause) {
        log.warn("subquery not processed");
    } else if (from instanceof SQLTableSourceImpl) {
        log.warn("Impl not processed");
    }
}

(3) Write Modified SQL Back to MyBatis

MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
// Build new MappedStatement
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), new MySqlSource(boundSql), ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
    StringBuilder keyProperties = new StringBuilder();
    for (String keyProperty : ms.getKeyProperties()) {
        keyProperties.append(keyProperty).append(",");
    }
    keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
    builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
MappedStatement newMappedStatement = builder.build();
MetaObject metaObject = MetaObject.forObject(newMappedStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
metaObject.setValue("sqlSource.boundSql.sql", newSql);
invocation.getArgs()[0] = newMappedStatement;

Reference article: https://blog.csdn.net/e_anjing/article/details/79102693