基于mybatis拦截器实现数据权限

数据权限是很多系统常见的功能，实现的方式也是很多的，最近在做项目的时候，自己基于mybatis拦截器做了一个数据权限的功能。

**功能设计

a)  需要做数据权限功能的表加上一个权限id字段。

权限id可以不仅仅是组织，还可以是其他自定义的字段，用来做数据权限，如：

主键Id	字段1	字段2	字段3	权限id
1	xxx	xxx	xxx	A001
2	xxx	xxx	xxx	A002
3	xx	xxx	xxx	A003

 

b)  1.分配权限id给数据角色；2.分配角色给员工， 这样就实现了权限的分配。

c)   员工-岗位-组织的关系如下，可以加一个开关，可以选择是否同时选用默认组织的权限数据。      

d)  取到步骤b)和c)的权限id列表后，组装到sql里面进行如下查询：

select   *  from  table  where  权限id   in  (‘A001’,’A002’,’A003’)

e) 为了减少代码的耦合性，采取注解的方式，在mapper.java的查询方法上面加注解：

**前台页面参考

测试表：

CREATE TABLE `lcp_test_auth` (
  `test_auth_id` bigint(20) NOT NULL COMMENT '主键ID',
  `attr1` varchar(64) NOT NULL COMMENT '字段1',
  `org_id` bigint(20) NOT NULL COMMENT '组织id',
  `warehouse_id` bigint(20) NOT NULL COMMENT '仓库id',
  `created_by` bigint(20) DEFAULT NULL,
  `creation_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_updated_by` bigint(20) DEFAULT NULL,
  `last_update_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `object_version_number` bigint(20) DEFAULT '1',
  `request_id` bigint(20) DEFAULT NULL,
  `program_id` bigint(20) DEFAULT NULL,
  `last_update_login` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`test_auth_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据权限测试表';

 

 

**后台功能实现

a) mybatis拦截器的xml配置，先在xml中配置好plugins，也就是自定义拦截器的类

 

b) 编写自己的拦截器类，我在拦截器里面需要做的事情是：

1.得到将要执行的原来的sql；

2.从request里面得到当前登录的用户的信息；

3.根据当前用户信息查询其拥有的权限数据；

4.根据权限数据拼接sql并修改原sql；

5.sql执行，完。

关于mybatis拦截器的内容可以参考：https://blog.csdn.net/xiao_jun_0820/article/details/70308253

下面是我的mybatis拦截类，其中DataAuth 是我的注解类，AuthSqlSource是我的sqlSource类，仅作为参考(直接copy肯定是跑不起来的，嘿嘿)

package com.fsl.lcp.interceptor;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import com.fsl.lcp.annotation.DataAuth;
import com.fsl.lcp.auth.dto.AuthorityResource;
import com.fsl.lcp.auth.service.IAuthorityResourceService;
import com.fsl.lcp.auth.service.IDataRoleResourceService;
import com.fsl.lcp.auth.sqlsource.AuthSqlSource;
import com.hand.hap.core.components.ApplicationContextHelper;

import java.lang.reflect.Method;
import java.util.List;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;

/**
 * mybatis数据权限拦截器
 * @author tanqian
 * 2018年11月29日
 */
@Intercepts({@Signature(method = "query", type = Executor.class,
	args = { MappedStatement.class, Object.class,RowBounds.class, ResultHandler.class })})
public class AuthLcpInterceptor implements Interceptor {
	
	private static final Logger log = LoggerFactory.getLogger(AuthLcpInterceptor.class);
	
	private ApplicationContext beanFactory;
	
	private IAuthorityResourceService iAuthorityResourceService;
	
	private IDataRoleResourceService iDataRoleResourceService;
	
	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties) {
	}

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		//初始化bean
		this.loadService();
		
		MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
		// 获取方法上的数据权限注解，如果没有注解，则直接通过
		DataAuth dataAuth = getPermissionByDelegate(mappedStatement);
		if (dataAuth == null) {
			return invocation.proceed();
		}
		// 获取request信息，得到当前登录用户信息
		RequestAttributes req = RequestContextHolder.getRequestAttributes();
		if (req == null) {
			return invocation.proceed();
		}
		//处理request
		HttpServletRequest request = ((ServletRequestAttributes) req).getRequest();
		//如果request里面的用户/员工信息为空，则直接抛异常
		if(request.getSession().getAttribute("employeeId") == null || request.getSession().getAttribute("userId") == null){
			throw new RuntimeException("此查询为权限数据，必须是拥有员工和用户属性的账号登录才可以查询！");
		}
		//employeeId
		Long employeeId = Long.valueOf(request.getSession().getAttribute("employeeId").toString());
		//userId
		Long userId = Long.valueOf(request.getSession().getAttribute("userId").toString());
		//处理组织权限数据，并返回组织权限sql
		String orgAuthSql = this.dealOrgAuth(dataAuth,employeeId);
		//处理数据权限数据，并返回数据权限sql
		String authSql = this.dealResourceSql(dataAuth,userId);
	
		//如果两种sql都为空，那直接返回
		if("".equals(orgAuthSql.trim()) && "".equals(authSql.trim())){
			return invocation.proceed();
		}
		log.info("待拼接sql：组织权限sql："+orgAuthSql + "，数据权限sql：" + authSql);
		//原sql
		String sql = mappedStatement.getBoundSql(invocation.getArgs()[1]).getSql();
		//处理sql拼接
		this.permissionSql(sql,orgAuthSql,authSql,invocation);
		
		return invocation.proceed();

	}

	/**
	 * 处理数据权限数据，并返回数据权限sql
	 * @param dataAuth
	 * @param userId
	 * @return
	 */
	private String dealResourceSql(DataAuth dataAuth, Long userId) {
		String authSql = "";
		//数据权限注解
		String resourceCode = dataAuth.resourceCode();
		if(!resourceCode.equals("")){
			List<AuthorityResource> resourceList = iAuthorityResourceService.selectAuthSqlByUserIdAndResourceCode(userId, resourceCode);
			if(resourceList.size() > 0){
				authSql = resourceList.get(0).getAuthorityResourceSql();
			}
		}
		return authSql;
	}

	/**
	 * 处理组织权限数据，并返回组织权限sql
	 * @param dataAuth
	 * @param employeeId 
	 * @return
	 */
	private String dealOrgAuth(DataAuth dataAuth, Long employeeId) {
		String orgAuthSql = "";
		//组织权限注解
		String orgAuth = dataAuth.orgAuth();
		//组织字段注解
		String authOrgId = dataAuth.authOrgId();
		if(orgAuth.equals("Y")){
			String orgIdList = iDataRoleResourceService.getDefaultChildUnitList(employeeId);
			orgAuthSql = orgIdList == null ? "" : authOrgId + " in (" + orgIdList + ")";
		}
		return orgAuthSql;
	}

	/**
	 * 获取数据权限注解信息
	 * 
	 * @param mappedStatement
	 * @return
	 */
	private DataAuth getPermissionByDelegate(MappedStatement mappedStatement) {
		DataAuth dataAuth = null;
		try {
			String id = mappedStatement.getId();
			String className = id.substring(0, id.lastIndexOf("."));
			String methodName = id.substring(id.lastIndexOf(".") + 1, id.length());
			final Class<?> cls = Class.forName(className);
			final Method[] method = cls.getMethods();
			for (Method me : method) {
				if (me.getName().equals(methodName) && me.isAnnotationPresent(DataAuth.class)) {
					dataAuth = me.getAnnotation(DataAuth.class);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataAuth;
	}

	/**
	 * 权限sql包装
	 * @param sql 原sql
	 * @param authSql 数据权限sql 
	 * @param orgAuthSql 组织权限sql
	 * @param invocation
	 */
	private void permissionSql(String sql,String orgAuthSql, String authSql, Invocation invocation) {
		final Object[] args = invocation.getArgs();
		MappedStatement statement = (MappedStatement) args[0];
		Object parameterObject = args[1];
		BoundSql boundSql = statement.getBoundSql(parameterObject);
		MappedStatement newStatement = newMappedStatement(statement, new AuthSqlSource(boundSql));
		MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
		//sql拼接
		if("".equals(authSql.trim()) && (!"".equals(orgAuthSql.trim()))){
			if(sql.toUpperCase().contains("WHERE")){
				sql = sql + " AND " + orgAuthSql;
			}else{
				sql = sql + " WHERE " + orgAuthSql;
			}
		}else{
			if("".equals(orgAuthSql.trim())){
				sql = sql + " " + authSql;
			}else{
				sql = sql + " "+ authSql + " AND " + orgAuthSql;
			}
			
		}
		//sql替换
		msObject.setValue("sqlSource.boundSql.sql", sql);
		args[0] = newStatement;
	}
	
	/**
	 * MappedStatement包装
	 * @param ms
	 * @param newSqlSource
	 * @return
	 */
	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) {
			StringBuilder keyProperties = new StringBuilder();
			for (String keyProperty : ms.getKeyProperties()) {
				keyProperties.append(keyProperty).append(",");
			}
			keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
			builder.keyProperty(keyProperties.toString());
		}
		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();
	}
	
	
    /**
     * 加载注入的bean
     */
    private void loadService() {
        if (null == beanFactory) {
            beanFactory = ApplicationContextHelper.getApplicationContext();
            if(null == beanFactory){
            	return;
            }
        }
        if (iAuthorityResourceService == null) {
        	iAuthorityResourceService = beanFactory.getBean(IAuthorityResourceService.class);
        }
        if (iDataRoleResourceService == null) {
        	iDataRoleResourceService = beanFactory.getBean(IDataRoleResourceService.class);
        }
        
    }
	
	 
}

DataAuth注解类：

package com.fsl.lcp.annotation;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 数据权限实现的注解
 * @author tanqian
 * @date 2018年11月21日
 */
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataAuth {
	/**
	 * 1.权限资源code，权限资源定义页面设置的，指明这次查询需要使用哪条资源sql </br>
	 * 2.如果定义了资源sql，却没有分配给当前登陆的用户，则不会返回任何数据</br>
	 * 3.输入不存在的资源code，也不会返回任何数据</br>
	 * 4.定义的sql写的有问题的，会产生不可预知的错误
	 * @return
	 */
	String resourceCode() default "";
	/**
	 * orgAuth </br>
	 * 组织权限，Y代表开启，N和不写代表不开启
	 * @return
	 */
	String orgAuth() default "";
	/**
	 * authOrgId </br>
	 * 权限组织字段，orgAuth=Y的时候必须写，否则无法获取哪个表的哪个字段是组织字段</br>
	 * 值为："权限业务表的别名.组织字段名"</br>
	 * 如：原sql为"select * from 权限业务表  auth, 业务表A ya where auth.org_id=123",</br>
	 * 则该注解应填值为："auth.org_id"
	 * @return
	 */
	String authOrgId() default "";
}

AuthSqlSource类，我是因为基于我们自己的hap框架，必须继承我们框架的PageSqlSource类，自己写的话，可以直接实现SqlSource接口 ：

package com.fsl.lcp.auth.sqlsource;

import org.apache.ibatis.mapping.BoundSql;

import com.github.pagehelper.sqlsource.PageSqlSource;
/**
 * 拦截器需要的SqlSource，必须继承自hap修改后的PageSqlSource
 * @author tanqian
 * 2018年11月29日
 */
public 	class AuthSqlSource extends PageSqlSource {
	private BoundSql boundSql;

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

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

	@Override
	protected BoundSql getDefaultBoundSql(Object parameterObject) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected BoundSql getCountBoundSql(Object parameterObject) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected BoundSql getPageBoundSql(Object parameterObject) {
		// TODO Auto-generated method stub
		return null;
	}
}

c)现在就可以进行测试了，写个最简单的controller-service-mapper来测试就行，我仅贴出mapper.java的代码了：

package com.fsl.lcp.test.mapper;

import com.hand.hap.mybatis.common.Mapper;

import java.util.List;

import com.fsl.lcp.annotation.DataAuth;
import com.fsl.lcp.test.dto.TestAuth;

public interface TestAuthMapper extends Mapper<TestAuth>{


	@DataAuth(orgAuth="Y",authOrgId="lta.org_id")
	List<TestAuth> testOrgAuth();
	
	@DataAuth(resourceCode="test_auth")
	List<TestAuth> testDefineAuth();

	@DataAuth(resourceCode="test_auth",orgAuth="Y",authOrgId="lta.org_id")
	List<TestAuth> testOrgAndDefineAuth();
	


}