[大数据]数据权限 基于mybatis plus插件

数据权限 基于mybatis plus插件

方案

关键：表create_by字段，需要做数据权限的某张表，根据当前登录用户的数据权限（例如ALL、ME、CUR_DEPT、CUR_DEPT_WITH_SUB_DEPT、CUSTOM）获取部门——>用户id，然后查询某张表时附加条件and create_by in [userIds]

说明

1、mp 拦截器中注入mapper，需要使用@Lazy懒加载，否则出现循环引用
2、mp数据权限拦截器可能与分页拦截器发生冲突（比如pagehelper），一是使用mp自己的inner pageinterceptor，二是通过重写sql，即在InExpression的right上附加sql（而不是通过mapper查询获取结果，然后作为右侧表达式）
3、补充2，解决插件冲突，例如pagehelper会针对第一条sql进行分页，如果在数据权限拦截器里使用mapper，结果可想而知，暂时没找到解决方案，也没测试mp自己的分页插件效果（预估这个插件是可以实现效果的——拦截器插入顺序先数据权限后分页即可，但是由于需求的项目mapper啥的比较杂，就不考虑继续测试效果了）

sql

create table ds_dept (
    id bigint(20) not null auto_increment,
    parent_id bigint(20) default 0,
    ancestor varchar(120) default '0',
    name varchar(40) not null,

    create_by bigint(20),
    update_by bigint(20),
    create_time datetime,
    update_time datetime,

    primary key (id)
) engine = innodb default charset = 'utf8';

create table ds_role (
    id bigint(20) not null auto_increment,
    name varchar(40) not null,

    create_by bigint(20),
    update_by bigint(20),
    create_time datetime,
    update_time datetime,

    primary key (id)
) engine = innodb default charset = 'utf8';


create table ds_role_dept (
    role_id bigint(20) not null,
    dept_id bigint(40) not null,
    primary key (role_id, dept_id)
) engine = innodb default charset = 'utf8';

create table ds_user (
    id bigint(20) not null auto_increment,
    dept_id bigint(20),
    role_id bigint(20),
    nick_name varchar(40) not null,

    is_admin tinyint default 0 comment '1超管用户，0普通用户',

    create_by bigint(20),
    update_by bigint(20),
    create_time datetime,
    update_time datetime,

    primary key (id)
) engine = innodb default charset = 'utf8';

insert into ds_dept values
(1, 0, '0,1', 'xx', 1, 1, now(), now()),
(11, 1, '0,1,11', '部门1', 1, 1, now(), now()),
(12, 1, '0,1,12', '部门2', 1, 1, now(), now()),
(13, 1, '0,1,13', '部门3', 1, 1, now(), now());

insert into ds_role values
(1, 'role-1', 1, 1, now(), now()),
(2, 'role-2', 1, 1, now(), now()),
(3, 'role-3', 1, 1, now(), now());

insert into ds_role_dept values
(1, 1), (2, 11), (3,11), (3, 12), (3, 13);

insert into ds_user values (1, 1, null, 'huzk', 1, 1, 1, now(), now());

业务代码

domain

mapper

service

controller

mp config类

@Configuration
@EnableTransactionManagement
@MapperScan({"com.example.demo.mapper"})
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(@Autowired InnerInterceptor dsDataPermissionInterceptor) {
        // 添加数据权限插件
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(dsDataPermissionInterceptor);
        System.out.println("ds inter:" + dsDataPermissionInterceptor.toString());
        return interceptor;
    }
}

data permission

1. 枚举

public enum DataScope {
    ALL(1, "全部数据"),
    ME(2, "create_by = me的数据"),
    CUR_DEPT(3, "create_by in (cur dept user ids)"),
    CUR_SUB_DEPT(4, "create_by in (cur dept and sub depts user ids"),
    CUSTOM(5, "create by in (role-dept user ids)"),
    ;

    private int code;

    private String message;

    private DataScope(int code, String message) {
        this.code = code;
        this.message = message;
    }
}

2. 拦截器

@Component
@Slf4j
public class DsDataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private static final List<String> FILTER_SQL = new ArrayList<>();

    static {
        FILTER_SQL.add("com.example.demo.mapper.DeptMapper.selectList");
        FILTER_SQL.add("com.example.demo.mapper.UserMapper.selectList");
    }

    @Autowired
    private DsDataPermissionHandler dataPermissionHandler;

    @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);
        }
    }

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

3. handler

@Component
@Slf4j
public class DsDataPermissionHandler {
    private static final EqualsTo NOT_EQ = new EqualsTo(new Column("1"), new Column("0"));

    @Autowired
    @Lazy
    UserMapper userMapper;

    @Autowired
    @Lazy
    DeptMapper deptMapper;

    public Expression getSqlSegment(PlainSelect plainSelect, String whereSegment) {
        DataScope dataScope = DataScope.CUR_SUB_DEPT;

        Expression where = plainSelect.getWhere();

        Table fromItem = (Table) plainSelect.getFromItem();
        Alias fromItemAlias = fromItem.getAlias();
        String mainTableName = fromItemAlias == null ? fromItem.getName() : fromItemAlias.getName();

        log.info("开始处理数据权限");
        return dispatch(dataScope, where, mainTableName);
    }

    private Expression dispatch(DataScope dataScope, Expression where, String mainTableName) {
        Expression expression = null;
        switch (dataScope) {
            case ALL:
                expression = handleAll(where);
                break;
            case ME:
                expression = handleMe(where, mainTableName);
                break;
            case CUR_DEPT:
                expression = handleCurDept(where, mainTableName);
                break;
            case CUR_SUB_DEPT:
                expression = handleCurSubDept(where, mainTableName);
                break;
            case CUSTOM:
                expression = handleCustom(where);
                break;
            default:
                break;
        }

        log.info("where: {}, ex: {}", where, expression);
        if (where == null) {
            return expression;
        }
        return new AndExpression(where, expression);
    }

    private Expression handleAll(Expression where) {
        return where;
    }

    private Expression handleMe(Expression where, String mainTableName) {
        Long userId = 1L;

        EqualsTo selfEqualsTo = new EqualsTo();
        selfEqualsTo.setLeftExpression(new Column(mainTableName + ".create_by"));
        selfEqualsTo.setRightExpression(new LongValue(userId));

        return selfEqualsTo;
    }

    private Expression handleCurDept(Expression where, String mainTableName) {
        Long deptId = 1L;
        List<Long> userIds = userMapper.selectIdsByDeptId(deptId);
        if (userIds.isEmpty()) {
            return NOT_EQ;
        }

        List<Expression> longUserIds = userIds.stream()
                .map(LongValue::new)
                .collect(Collectors.toList());
        InExpression inExpression = new InExpression();
        inExpression.setLeftExpression(new Column(mainTableName + ".create_by"));
        inExpression.setRightItemsList(new ExpressionList(longUserIds));

        return inExpression;
    }

    private Expression handleCurSubDept(Expression where, String mainTableName) {
        Long deptId = 1L;
        String ancestor = "0" + "," + deptId;
        List<Long> deptIds = deptMapper.selectIdsByAncestor(ancestor);
        if (deptIds.isEmpty()) {
            return NOT_EQ;
        }
        List<Long> userIds = userMapper.selectIdsByDeptIds(deptIds);

        List<Expression> longUserIds = userIds.stream()
                .map(LongValue::new)
                .collect(Collectors.toList());
        InExpression inExpression = new InExpression();
        inExpression.setLeftExpression(new Column(mainTableName + ".create_by"));
        inExpression.setRightItemsList(new ExpressionList(longUserIds));

        return inExpression;
    }

    private Expression handleCustom(Expression where) {
        return where;
    }
}

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖