本文共 11823 字,大约阅读时间需要 39 分钟。
数据权限是很多系统常见的功能,实现的方式也是很多的,最近在做项目的时候,自己基于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拦截器的内容可以参考:
下面是我的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("")){ ListresourceList = 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@Inheritedpublic @interface DataAuth { /** * 1.权限资源code,权限资源定义页面设置的,指明这次查询需要使用哪条资源sql * 2.如果定义了资源sql,却没有分配给当前登陆的用户,则不会返回任何数据 * 3.输入不存在的资源code,也不会返回任何数据 * 4.定义的sql写的有问题的,会产生不可预知的错误 * @return */ String resourceCode() default ""; /** * orgAuth * 组织权限,Y代表开启,N和不写代表不开启 * @return */ String orgAuth() default ""; /** * authOrgId * 权限组织字段,orgAuth=Y的时候必须写,否则无法获取哪个表的哪个字段是组织字段 * 值为:"权限业务表的别名.组织字段名" * 如:原sql为"select * from 权限业务表 auth, 业务表A ya where auth.org_id=123", * 则该注解应填值为:"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{ @DataAuth(orgAuth="Y",authOrgId="lta.org_id") List testOrgAuth(); @DataAuth(resourceCode="test_auth") List testDefineAuth(); @DataAuth(resourceCode="test_auth",orgAuth="Y",authOrgId="lta.org_id") List testOrgAndDefineAuth(); }
转载地址:http://frzoi.baihongyu.com/