demand

In order to enhance the security of the program, it is necessary to selectively determine whether to enhance SQL when the user accesses the database after the permission judgment, so as to achieve the purpose of restricting the access of users with low-level permission to the data.

Based on service requirements, role permissions are limited based on data scope. For example, the roles are as follows:

Departments are as follows:

Ideas:

1. You can mimic pageHelper. startPage to declare which operations need to be scoped
2. Mybatis Plus custom Interceptor, used for each interception query SQL statement, with data scope permission SQL conditions
3. Pagehelper. startPage = pagehelper. startPage = pagehelper. startPage = pagehelper. startPage = pagehelper. startPage = pagehelper. startPage = pagehelper. startPage
4. Considering that Mybatis interceptor can intercept the whole process of SQL execution, because we can consider the SQL execution before rewriting, so as to achieve the purpose of data line permissions.

Steps:

1. The statement datescope

protected static final ThreadLocal<String> threadLocal = new ThreadLocal(); Public static void startDataScope(){threadlocal.set (SecurityConstants.DATA_SCOPE); /** * Public static void startDataScope(){threadLocal.set(SecurityConstants. } public static String getDataScope(){return threadLocal.get(); } /** * public static void cleanDataScope(){threadlocal.remove (); }Copy the code

2. Define Mybatis Plus custom interceptor

@date 2022-04-01 17:03 */ @intercepts ({@intercepts (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 MybatisDataPermissionIntercept implements Interceptor { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Public static final String DATA_SCOPE_ALL = "1"; Public static final String DATA_SCOPE_CUSTOM = "2"; public static final String DATA_SCOPE_CUSTOM = "2"; Public static final String DATA_SCOPE_DEPT = "3"; Public static final String DATA_SCOPE_DEPT_AND_CHILD = "4"; public static final String DATA_SCOPE_DEPT_AND_CHILD = "4"; Public static final String DATA_SCOPE_SELF = "5"; @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) {boundSql = Ms. GetBoundSql (parameter); cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql); } else {// cacheKey = (cacheKey) args[4]; boundSql = (BoundSql) args[5]; String SQL = boundSQL.getsQL (); Log.info (" original SQL: {}", SQL); / / judge whether there is permission information inside the thread String dataScope. = the SecurityUtils getDataScope (); Data_scope. equals(dataScope)){// Enhance SQL Select Select = (Select) parserManager.parse(new) StringReader(sql)); SelectBody selectBody = select.getSelectBody(); if (selectBody instanceof PlainSelect) { this.setWhere((PlainSelect) selectBody); } else if (selectBody instanceof SetOperationList) { SetOperationList setOperationList = (SetOperationList) selectBody; List<SelectBody> selectBodyList = setOperationList.getSelects(); selectBodyList.forEach((s) -> { this.setWhere((PlainSelect) s); }); } String dataPermissionSql = select.toString(); Log.info (" enhanced SQL: {}", dataPermissionSql); BoundSql dataPermissionBoundSql = new BoundSql(ms.getConfiguration(), dataPermissionSql, boundSql.getParameterMappings(), parameter); return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, dataPermissionBoundSql); } / / note: The following method can be called multiple times according to its own logic, in the paging plug-in, Return Executor.query (ms, parameter, rowBounds, resultHandler, cacheKey, boundSql); } the finally {/ / remove permissions from the thread parameters. The SecurityUtils cleanDataScope (); } } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } protected void setWhere(PlainSelect plainSelect) { Expression sqlSegment = this.getSqlSegment(plainSelect.getWhere()); if (null ! = sqlSegment) { plainSelect.setWhere(sqlSegment); } } @SneakyThrows public Expression getSqlSegment(Expression where) { JSONObject loginUser = getLoginUser(); if (loginUser == null){ return where; } Integer deptId = loginUser.getInteger("deptId"); String userId = loginUser.getString("userId"); JSONArray roles = loginUser.getJSONArray("roles"); StringBuilder sqlString = new StringBuilder(); for (Object role : roles) { JSONObject roleJson = JSONObject.parseObject(role.toString()); String dataScopeNum = roleJson.getString(SecurityConstants.DATA_SCOPE); Integer roleId = roleJson.getInteger("roleId"); If (data_scope_all.equals (dataScopeNum)) {// All data permissions sqlString = new StringBuilder(); break; } else if (DATA_SCOPE_CUSTOM.equals(dataScopeNum)) { sqlString.append(" OR `sys_dept`.dept_id IN ( SELECT dept_id FROM `sys_role_dept` WHERE role_id = '") .append(roleId) .append("' ) "); } else if (DATA_SCOPE_DEPT.equals(dataScopeNum)) { sqlString.append(" OR `sys_dept`.dept_id = '").append(deptId).append("' "); } else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScopeNum)) { sqlString.append(" OR `sys_dept`.dept_id IN ( SELECT dept_id FROM `sys_dept` WHERE dept_id = '") .append(deptId) .append("' or find_in_set( '") .append(deptId) .append("' , ancestors ) ) "); }else if (data_scope_self. equals(dataScopeNum)) {//TODO has a problem. Sqlstring.append (" OR 'sys_user'. User_id = '").append(userId).append("' "); } } if (StringUtils.isNotBlank(sqlString.toString())) { if (where == null){ where = new HexValue(" 1 = 1 "); } sqlString.insert(0," AND ("); sqlString.append(")"); sqlString.delete(7, 9); / / determine whether paging, paging is completed Remove permissions identification return CCJSqlParserUtil. ParseCondExpression (where + sqlString. ToString ()); }else { return where; }}}Copy the code

3. Modify MybatisDataPermissionIntercept behind PageHelper plug-ins execution

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\ Com. ZHC. Cloud. Mybatis. Intercept. MybatisInterceptorAutoConfiguration / * * * @ author ZHC * * @ @ the description data access plug-in configuration for the date 2022-04-01 17:01 */ @AutoConfigureAfter(PageHelperAutoConfiguration.class) @Configuration public class MybatisInterceptorAutoConfiguration implements InitializingBean{ @Autowired private List<SqlSessionFactory> sqlSessionFactoryList; @override @postconstruct public void afterPropertiesSet() throws Exception {// Create a custom Mybatis interceptor, Added to the chain of the back MybatisDataPermissionIntercept mybatisInterceptor = new MybatisDataPermissionIntercept (); for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) { org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration(); / / add configuration. AddInterceptor (mybatisInterceptor); }}}Copy the code

Source code address :github.com/zhc-1999/zh…