使用mybatis切片實(shí)現(xiàn)數(shù)據(jù)權(quán)限控制

一、使用方式

數(shù)據(jù)權(quán)限控制需要對(duì)查詢出的數(shù)據(jù)進(jìn)行篩選，對(duì)業(yè)務(wù)入侵最少的方式就是利用mybatis或者數(shù)據(jù)庫連接池的切片對(duì)已有業(yè)務(wù)的sql進(jìn)行修改。切片邏輯完成后，僅需要在業(yè)務(wù)中加入少量標(biāo)記代碼，就可以實(shí)現(xiàn)對(duì)數(shù)據(jù)權(quán)限的控制。這種修改方式，對(duì)老業(yè)務(wù)的邏輯沒有入侵或只有少量入侵，基本不影響老業(yè)務(wù)的邏輯和可讀性；對(duì)新業(yè)務(wù)，業(yè)務(wù)開發(fā)人員無需過多關(guān)注權(quán)限問題，可以集中精力處理業(yè)務(wù)邏輯。

由于部門代碼中使用的數(shù)據(jù)庫連接池種類較多，不利于切片控制邏輯的快速完成，而sql拼接的部分基本只有mybatis和java字符串直接拼接兩種方式，因此使用mybatis切片的方式來完成數(shù)據(jù)權(quán)限控制邏輯。在mybatis的mapper文件的接口上添加注解，注解中寫明需要控制的權(quán)限種類、要控制的表名、列名即可控制接口的數(shù)據(jù)權(quán)限。

??

由于mybatis的mapper文件中的同一接口在多個(gè)地方被調(diào)用，有的需要控制數(shù)據(jù)權(quán)限，有的不需要，因此增加一種權(quán)限控制方式：通過ThreadLocal傳遞權(quán)限控制規(guī)則來控制當(dāng)前sql執(zhí)行時(shí)控制數(shù)據(jù)權(quán)限。

??

權(quán)限控制規(guī)則格式如下：

限權(quán)規(guī)則code1(表名1.字段名1,表名2.字段名2);限權(quán)規(guī)則code2(表名3.字段名3,表名4.字段名4)

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

上下文傳遞工具類如下所示，使用回調(diào)的方式傳遞ThreadLocal可以防止使用者忘記清除上下文。

?

public class DataAuthContextUtil {
    /**
     * 不方便使用注解的地方，可以直接使用上下文設(shè)置數(shù)據(jù)規(guī)則
     */
    private static ThreadLocal useDataAuth = new ThreadLocal();

    /**
     * 有的sql只在部分情況下需要使用數(shù)據(jù)權(quán)限限制

     * 上下文和注解中均可設(shè)置數(shù)據(jù)權(quán)限規(guī)則，都設(shè)置時(shí)，上下文中的優(yōu)先
     *
     * @param supplier
     */
    public static  T executeSqlWithDataAuthRule(String rule, Supplier supplier) {
        try {
            useDataAuth.set(rule);
            return supplier.get();
        } finally {
            useDataAuth.remove();
        }
    }

    /**
     * 獲取數(shù)據(jù)權(quán)限標(biāo)志
     *
     * @return
     */
    public static String getUseDataAuthRule() {
        return useDataAuth.get();
    }
}

二、切片實(shí)現(xiàn)流程

??

三、其他技術(shù)細(xì)節(jié)

（1）在切面中獲取原始sql

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.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
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 = "update", args = {MappedStatement.class, Object.class}),
        @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();
        } catch (Exception e) {
            log.error("數(shù)據(jù)權(quán)限添加出錯(cuò),當(dāng)前sql未加數(shù)據(jù)權(quán)限限制！", e);
            throw e;
        }
        return invocation.proceed();
    }
}

（2）將權(quán)限項(xiàng)加入原始sql中

使用druid附帶的ast解析功能修改sql，代碼如下

/**
     * 權(quán)限限制寫入sql
     *
     * @param sql
     * @param tableAuthMap key:table value1:column value2:values權(quán)限項(xiàng)
     * @return
     */
    public static StringBuilder addAuthLimitToSql(String sql, Map>> tableAuthMap) {
        List 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 tableList = new HashSet();
                    getTableList(from, tableList);
                    for (String tableName : tableList) {
                        if (tableAuthMap.containsKey(tableName)) {
                            x.addCondition(tableName + "in (...略)");
                        }
                    }
                    return true;
                }
            });
            authSql.append(stmt);
        }
        return authSql;
    }
    
    private static void getTableList(SQLTableSource from, Set 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 joinTableSource = (SQLJoinTableSource) from;
            getTableList(joinTableSource.getLeft(), tableList);
            getTableList(joinTableSource.getRight(), tableList);
        } else if (from instanceof SQLSubqueryTableSource) {
            SQLSubqueryTableSource tableSource = (SQLSubqueryTableSource) from;
            tableList.add(tableSource.getAlias().replace("`", ""));
        } else if (from instanceof SQLLateralViewTableSource) {
            log.warn("SQLLateralView不用處理");
        } else if (from instanceof SQLUnionQueryTableSource) {
            //union 不需要處理
            log.warn("union不用處理");
        } else if (from instanceof SQLUnnestTableSource) {
            log.warn("Unnest不用處理");
        } else if (from instanceof SQLValuesTableSource) {
            log.warn("Values不用處理");
        } else if (from instanceof SQLWithSubqueryClause) {
            log.warn("子查詢不用處理");
        } else if (from instanceof SQLTableSourceImpl) {
            log.warn("Impl不用處理");
        }
    }
}

（3）將修改過后的sql寫回mybatis

        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
        // 組裝 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;

?

參考文章： https://blog.csdn.net/e_anjing/article/details/79102693

審核編輯 黃宇