细粒度权限，用户只能看到自己相关的数据（通过mysql拦截器实现）

最新推荐文章于 2022-11-01 20:54:06 发布

祁_z

最新推荐文章于 2022-11-01 20:54:06 发布

阅读量1.9k

点赞数

分类专栏： mybatis 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_36881887/article/details/111593617

版权

mybatis 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

场景：在权限中，用户只能看到自己创建的数据（场景管理数据scenario_storage）或者是其它用户分配过来的数据.

表结构：

CREATE TABLE `scenario_storage` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '场景主键ID',
    `name` varchar(500) DEFAULT NULL COMMENT '场景名称',
    PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='场景表';


 CREATE TABLE `scenario_storage_user` (
    `scenario_storage_id` int(11) NOT NULL COMMENT '场景表主键ID',
    `user_id` int(11) NOT NULL COMMENT '用户表主键ID',
    KEY `scenario_storage_id` (`scenario_storage_id`,`user_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='场景-用户中间表\r\n注意中间表不创建主键ID，\r\n遵循中间表字段命名规范：表名+下划线+id';


 INSERT INTO `scenario_storage` VALUES ('1', '仿真任务1');
 INSERT INTO `scenario_storage` VALUES ('2', '仿真任务2');
 INSERT INTO `scenario_storage` VALUES ('3', '仿真任务3');
 INSERT INTO `scenario_storage` VALUES ('4', '仿真任务4');
 INSERT INTO `scenario_storage` VALUES ('5', '仿真任务5');
 INSERT INTO `scenario_storage` VALUES ('6', '仿真任务6');

 INSERT INTO `scenario_storage_user` VALUES ('1', '1');
 INSERT INTO `scenario_storage_user` VALUES ('1', '2');
 INSERT INTO `scenario_storage_user` VALUES ('2', '1');
 INSERT INTO `scenario_storage_user` VALUES ('2', '2');
 INSERT INTO `scenario_storage_user` VALUES ('3', '1');
 INSERT INTO `scenario_storage_user` VALUES ('4', '1');
 INSERT INTO `scenario_storage_user` VALUES ('5', '1');
 INSERT INTO `scenario_storage_user` VALUES ('6', '1');

定义规则：

1. 目前只支持单表数据过滤，多表的话也只需要在增强sql上再做一些判断即可
2. 用户id从请求头获取，也就是说前端请求接口时需要将用户id以key为user-id的方式放入请求头中，如user-id:1            或者修改request.getHeader("user-id")的获取用户id的方式
3. 中间表命名规则：主表名+下划线+用户表名       如：scenario_storage_user
   中间表字段命名规则：表名+下划线+id          如：user_id

实现

自定义拦截注解

package com.cq.controller;

import java.lang.annotation.*;

/**
 * sql增强注解,目标支持单表的增强
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface InterceptAnnotation {

    String value() default "";

    /** true增强、false忽略 */
    boolean flag() default true;

}

拦截器增强sql

import com.cq.controller.InterceptAnnotation;
import lombok.extern.log4j.Log4j2;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Properties;
import java.util.regex.Pattern;

/**
 * sql拦截器，通过mybatis提供的Interceptor接口实现
 */
@Log4j2
@Component
//拦截StatementHandler类中参数类型为Statement的prepare方法（prepare=在预编译SQL前加入修改的逻辑）
//即拦截 Statement prepare(Connection var1, Integer var2) 方法
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MySqlInterceptor implements Interceptor {

    @Autowired
    private HttpServletRequest request;

    /**
     * 拦截sql
     *
     * @param invocation
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

        // 通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
        // 优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                new DefaultReflectorFactory());

        // 先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

        // id为执行的mapper方法的全路径名，如com.cq.UserMapper.insertUser， 便于后续使用反射
        String id = mappedStatement.getId();
        // sql语句类型 SELECT、DELETE、INSERT、UPDATE
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        // 数据库连接信息
//        Configuration configuration = mappedStatement.getConfiguration();
//        ComboPooledDataSource dataSource = (ComboPooledDataSource)configuration.getEnvironment().getDataSource();
//        dataSource.getJdbcUrl();

        BoundSql boundSql = statementHandler.getBoundSql();
        // 获取到原始sql语句
        String sql = boundSql.getSql().toLowerCase();
        log.info("SQL：{}", sql);


        // 增强sql
        // 通过反射，拦截方法上带有自定义@InterceptAnnotation注解的方法，并增强sql
        String mSql = sqlAnnotationEnhance(id, sqlCommandType, sql);

        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);
        log.info("增强后的SQL：{}", mSql); // 打印：增强后的SQL：select * from scenario_storage limit 2
        return invocation.proceed();
    }

    /**
     * 通过反射，拦截方法上带有自定义@InterceptAnnotation注解的方法，并增强sql
     *
     * @param id             方法全路径
     * @param sqlCommandType sql类型
     * @param sql            所执行的sql语句
     */
    private String sqlAnnotationEnhance(String id, String sqlCommandType, String sql) throws ClassNotFoundException {
        // 通过类全路径获取Class对象
        Class<?> classType = Class.forName(id.substring(0, id.lastIndexOf(".")));
        // 获取当前所拦截的方法名称
        String mName = id.substring(id.lastIndexOf(".") + 1);
        // 遍历类中所有方法名称，并if匹配上当前所拦截的方法
        for (Method method : classType.getDeclaredMethods())
            if (mName.equals(method.getName())) {
                // 判断方法上是否带有自定义@InterceptAnnotation注解
                InterceptAnnotation interceptorAnnotation = method.getAnnotation(InterceptAnnotation.class);
                if (interceptorAnnotation.flag()) {
                    if ("SELECT".equals(sqlCommandType)) {
                        // 增强sql
                        //return sql + " limit 2";

                        // 根据用户Id增强sql
                        String msql = enhanceSqlByUserId(sql);
                        return msql;
                    }
                }
            }
        return sql;
    }

    /**
     * 根据用户id增强sql
     *
     * @param sql 原生sql
     * @return 增强后的sql
     */
    private String enhanceSqlByUserId(String sql) {
        // 从请求头中获取用户id
        String userId = request.getHeader("user-id");
        if (userId == null) {
            log.error("用户标识为空，无法进行增强sql");
            throw new RuntimeException("user-id用户标识为空");
        }
        log.info("根据用户id：{}，开始增强sql", userId);

        // 根据原生sql+用户id进行sql语句增强
        // String sql = "select * from scenario_storage order by id desc";
        // select * from scenario_storage inner join scenario_storage_user on scenario_storage.id = scenario_storage_user.scenario_storage_id

        // 去除sql中多余的空格，默认空一位即可
        String formatSql = Pattern.compile("\\s+").matcher(sql).replaceAll(" ");
        log.info(formatSql); // select * from scenario_storage order by id desc

        // 拆分sql语句
        String substring = formatSql.substring(formatSql.indexOf("from") + 5);
        // 表名 scenario_storage
        String fromName = null;
        // 表名前的字符串 select * from
        String fromBegin = null;
        // 表名后的字符串 order by id desc
        String fromlast = null;
        if (!substring.contains(" ")) {
            fromName = substring;
            fromBegin = formatSql.substring(0, formatSql.lastIndexOf(fromName));
            fromlast = "";
        } else {
            fromName = substring.substring(0, substring.indexOf(" "));
            fromBegin = formatSql.substring(0, formatSql.indexOf("from") + 4);
            fromlast = substring.substring(substring.indexOf(" ") + 1);
        }
        log.info(fromBegin + "--" + fromName + "--" + fromlast);


        // 中间表名(表名规则：fromName + _user)
        String intermediateTable = fromName + "_user";

        // 修改where条件后的语句，添加中间表查询条件
        if (!fromlast.equals("")) {
            boolean isWhere = fromlast.contains("where");

            if (isWhere) {// where
                String addWhere = "where 1 = 1 and " + intermediateTable + ".user_id" + " = " + userId + " and ";
                fromlast = addWhere + fromlast.substring(fromlast.indexOf("where") + 5);
            }else if(fromlast.length() > 2){ // limit \ order by \ group ...
                String addWhere = "where 1 = 1 and " + intermediateTable + ".user_id" + " = " + userId + " ";
                fromlast = addWhere + fromlast;
            }
        }

        // 拼接增强sql语句
        StringBuffer enhanceSql = new StringBuffer();
        enhanceSql.append(fromBegin).append(" ")
                .append(fromName).append(" ")
                .append("INNER JOIN").append(" ")
                .append(intermediateTable).append(" ")
                .append("ON").append(" ")
                .append(fromName + ".id").append(" ")
                .append("=").append(" ")
                .append(intermediateTable + "." + fromName + "_id").append(" ")
                .append(fromlast);

        return enhanceSql.toString();
    }

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

    @Override
    public void setProperties(Properties properties) {
    }


    public static void main(String[] args) {
        String sql = "select * from scenario_storage order by id desc";
        // select * from scenario_storage inner join scenario_storage_user on scenario_storage.id = scenario_storage_user.scenario_storage_id

        // 去除sql中多余的空格，默认空一位即可
        String formatSql = Pattern.compile("\\s+").matcher(sql).replaceAll(" ");
        log.info(formatSql); // select * from scenario_storage order by id desc

        // 拆分sql语句
        String substring = formatSql.substring(formatSql.indexOf("from") + 5);
        // 表名
        String fromName = substring.substring(0, substring.indexOf(" ")); //scenario_storage
        // 表名前的字符串
        String fromBegin = formatSql.substring(0, formatSql.indexOf("from") + 4); //select * from
        // 表名后的字符串
        String fromlast = substring.substring(substring.indexOf(" ") + 1); //order by id desc
        log.info(fromBegin + "--" + fromName + "--" + fromlast);

        // 中间表名
        String intermediateTable = fromName + "_user";

        // 拼接增强sql语句
        StringBuffer sb = new StringBuffer();
        sb.append(fromBegin).append(" ")
                .append(fromName).append(" ")
                .append("inner join").append(" ")
                .append("tableUser").append(" ")
                .append("on").append(" ")
                .append(fromName + ".id").append(" ")
                .append("=").append(" ")
                .append(intermediateTable + "." + fromName + "_id").append(" ")
                .append(fromlast);

        log.info("增强sql语句：" + sb.toString());
    }
}

实体类

import lombok.Data;

/** 场景表字段映射 */
@Data
public class ScenarioStorageEntity {

    private int id;
    private String name;

}

Controller

@RestController
public class IntercaperController {

    @Autowired
    private IntercaperMapper intercaperMapper;

    @RequestMapping("/getScenarioList")
    public Object getScenarioList(){
        List<ScenarioStorageEntity> scenarioStorageEntities = intercaperMapper.queryScenarioStorageAll();
        scenarioStorageEntities.forEach(entity-> System.out.println(entity.toString()));
        return scenarioStorageEntities;
    }

}

Mapper

public interface IntercaperMapper {

    @InterceptAnnotation
    @Select("select * from scenario_storage  where 1=1   order by id desc")
    List<ScenarioStorageEntity> queryScenarioStorageAll();

}

执行结果：

增强前SQL：select * from scenario_storage where name like '仿真任务%' order by id desc

增强后SQL：select * from scenario_storage INNER JOIN scenario_storage_user ON scenario_storage.id = scenario_storage_user.scenario_storage_id where 1 = 1 and scenario_storage_user.user_id = 2 and name like '仿真任务%' order by id desc

祁_z

关注

0
点赞
踩
16

收藏

觉得还不错? 一键收藏
打赏
3
评论
细粒度权限，用户只能看到自己相关的数据（通过mysql拦截器实现）

目录表结构：定义规则：实现自定义拦截注解拦截器增强sql实体类ControllerMapper执行结果：场景：在权限中，用户只能看到自己创建的数据（场景管理数据scenario_storage）或者是其它用户分配过来的数据.表结构：CREATE TABLE `scenario_storage` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '场景主键ID', `name` varchar(5
复制链接

扫一扫