spring项目+mysql等关系型数据库实现数据权限思路

一、前言

     鉴权一般分为两种: 功能鉴权和数据鉴权。我们这里先暂且只讨论数据鉴权。

     我们在做一般的后台管理系统时（crm、cms等) ，可能还会遇到如下业务场景: 

         某CRM管理系统向公司的全部销售人员提供服务，其中用户可能不只有普通的销售人员可能还会有销售主管、销售经理等。

         1、普通销售人员只能看到自己的客户信息

         2、销售主管可以看到整个小组的客户信息

         3、经理可以看到整个部门的客户信息

    这时数据权限就显得格外重要。

二、实现思路

        1、实现前提需要有一套比较完善的RBAC体系

        2、当发起查询请求时确保可以拿到当前登录用户的组织架构信息和角色等信息

        3、关键步骤 - 拿到用户的组织架构信息拼接到sql查询条件中

三、实现

       1、我们可以自定义一个注解

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

}

      2、然后对该注解进行aop处理，也就是说在这里需要收集当前用户的组织架构信息

@Slf4j
@Aspect
@Component
@Order(2)
public class DataPermissionAspect {

    private static ThreadLocal<String> ORG_SQL_THREAD_LOCAL = new ThreadLocal<>();

    @Around(value = "@annotation(DataPermission)")
    @SneakyThrows
    public Object around(ProceedingJoinPoint pjp){
        
       //这里来获取用户的组织架构信息并set到threadLocal(偷个懒哈哈)
    }
}

       3、然后我们自定义一个mybatis拦截器

@Slf4j
@Intercepts(@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
@Component
public class SqlInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {
            Object[] args = invocation.getArgs();
            MappedStatement ms = (MappedStatement) args[0];
            Object parameter = args[1];
            RowBounds rowBounds = (RowBounds) args[2];
            ResultHandler resultHandler = (ResultHandler) args[3];
            Executor executor = (Executor) invocation.getTarget();
            CacheKey cacheKey;
            BoundSql boundSql;
            //由于逻辑关系，只会进入一次
            if (args.length == 4) {
                //4 个参数时
                boundSql = ms.getBoundSql(parameter);
                cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
            } else {
                //6 个参数时
                cacheKey = (CacheKey) args[4];
                boundSql = (BoundSql) args[5];
            }
            log.info("------ 原始的sql 为 ： {}", boundSql.getSql());
            String originalSql = boundSql.getSql();
            //这里进行组织架构条件拼接，当然还需要处理排序或者没有条件等情况完成拼接(我这里随便写一个 1 = 1)
            boolean order_by = originalSql.contains("ORDER BY");
            String newSql = !order_by ? originalSql + " AND 1 = 1" : originalSql;
            BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql,
                    boundSql.getParameterMappings(), boundSql.getParameterObject());
            MappedStatement newMs = newMappedStatement(ms, new MyBoundSqlSqlSource(newBoundSql));
            for (ParameterMapping mapping : boundSql.getParameterMappings()) {
                String prop = mapping.getProperty();
                if (boundSql.hasAdditionalParameter(prop)) {
                    newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
                }
            }
            return executor.query(newMs, parameter, rowBounds, resultHandler, cacheKey, newBoundSql);
        } catch (Exception e) {

        }
        return null;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * 定义一个内部辅助类，作用是包装 SQL
     */
    public static class MyBoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;

        public MyBoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }

    }

    private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new
                MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, 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) {
            builder.keyProperty(ms.getKeyProperties()[0]);
        }
        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());
        return builder.build();
    }
}

4、将上面的mybatis拦截器配置到mybatis的SqlSessionFactory中

@Configuration
public class MyMybatisConfig {
    //这里注入的就是步骤3中拦截器
    @Resource
    SqlInterceptor sqlInterceptor;

    @Resource
    SqlSessionFactory sqlSessionFactory;

    @PostConstruct
    public void add() {
        sqlSessionFactory.getConfiguration().addInterceptor(sqlInterceptor);
    }
}

四、结论

        经本人实测，上述方案是可行的。当运行sql时可以动态的将sql拼接起来。