前言

项目实践中，基本都会有权限的需求，权限需求分为两大块：1、功能权限；2、数据权限。而数据权限又可在行和列上细分为两块，即数据范围权限：用户能看到哪些行的记录；数据字段权限：用户能看到这些行对应的哪些字段。本文以字段权限为例做一个demo展示。

方案

字段权限方案也有很多种，这里采用配置无权限字段，在sql查询前，对sql进行拦截过滤，剔除无权限字段。

框架

这里采用mybatis作为ORM框架。

配置

配置pom依赖

   <!-- mybatis -->
	        <dependency>
			    <groupId>org.mybatis.spring.boot</groupId>
			    <artifactId>mybatis-spring-boot-starter</artifactId>
			    <version>2.0.0</version>
			</dependency>
         <!-- 解析sql用的 -->
 			<dependency>
			    <groupId>com.github.jsqlparser</groupId>
			    <artifactId>jsqlparser</artifactId>
			    <version>1.2</version>
			</dependency>

yml配置

spring: 
  proifles: dev
  #据源配置
  datasource: 
    name: test
    url: jdbc:mysql://xxx:3306/xxx?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
    username: root
    password: xxx
 
# 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver


# 注意：一定要对应mapper映射xml文件的所在路径
mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
#注意：对应实体类的路径
  type-aliases-package: com.xxx.model 
  #mybatis 配置路径
  config-location: classpath:mybatis/mybatis-config.xml
  

plugin配置

配置mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
     <plugins>
    	<!-- 自定义实现 -->
	     <plugin interceptor="com.xxx.DataAuthorInterceptor">
	    </plugin>
    </plugins>
   
</configuration>

插件实现

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
//指定连接的方法
@Intercepts(@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
		RowBounds.class, ResultHandler.class }))
public class DataAuthorInterceptor implements Interceptor {
	
	private Properties properties;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		final Object[] args = invocation.getArgs();
		MappedStatement ms = (MappedStatement)args[0];
		Object parameterObject = args[1];
		BoundSql boundSql = ms.getBoundSql(parameterObject);
		//获取初始sql
		String originSql = boundSql.getSql();
		//修改sql
		String changeSql = parseSql(originSql);
		Field field = boundSql.getClass().getDeclaredField("sql");
		field.setAccessible(true);
		field.set(boundSql, changeSql);
		return invocation.proceed();
	}
	
	//解析SQL，根据数据权限，去除没有权限的字段
	private String parseSql(String sql) {
		List<String> blockList = getBlockCols();
		StringBuffer newSql = new StringBuffer();
		try {
			//解析sql 获得结构化statement
			Statements s = CCJSqlParserUtil.parseStatements(sql);
			//对每一个statement
			for(Statement st : s.getStatements()) {
				if(null != st) {
					//查询sql处理
					if(st instanceof Select) {
						SelectBody selectBody = ((Select) st).getSelectBody();
						if(selectBody instanceof PlainSelect) {
							Iterator<SelectItem> it = ((PlainSelect) selectBody).getSelectItems().iterator();
							//遍历查询字段
							while(it.hasNext()) {
								SelectItem si = it.next();
								if(si instanceof SelectExpressionItem) {
									for(String str : blockList) {
										//查询字段同 权限隐藏字段匹配 则从查询中移除
										if(si.toString().contains(str)) {
											it.remove();
										}
									}
								}
							}
						}
						
						//return ((Select) st).getSelectBody().toString();
					}
					newSql.append(st + ";");
				}
			}
			
		} catch (JSQLParserException e) {
			e.printStackTrace();
		}
		
		return newSql.toString();
	}
	
	
	//解析sql结构体
	private List<String> getBlockCols(){
		List<String> l = new ArrayList<String>();
		l.add("a");
		l.add("b1");
		return l;
	}

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

	@Override
	public void setProperties(Properties properties) {
		this.properties = properties;
	}
	
	
	public static void main(String[] args) {
		String sql = "select a, b, c from table1";
		String sql2 = "select a, b, c from (select e as a, f as b, g as c from table1)";
		String sql3 = "select a as a1, b b1, c from table3 as t3, (select e as a, f as b from table1) , (select g as c from table2)";
		String sql4 = "select c.vin8, (select auto_brand from vin_base b where b.vin8 = c.vin8)\n" + 
				"from vin_base c  where c.vin8 = '72753413' ";
		String sql5 = "select a, b from table1 union select c, d from table2;";
		String changeSql = new DataAuthorInterceptor().parseSql(sql5);
		System.out.println("changeSql>>>>>>>>" + changeSql);
		
	
	}

}

到这里，完成了对sql的拦截修改。

总结

1、配置：pom.xml依赖配置、yaml应用配置、插件配置、插件实现。
2、以上示例仅对一般查询及子查询做了处理，对与union查询，需要再实现。
拦截处理，是一种很好的设计思路，可以对相同处理方式进行组件化，并在应用中，避免侵入式，从而做到润物无声。

本文地址：https://blog.csdn.net/weixin_42005602/article/details/107151625