Mabitis 实现数据权限
业务背景:
不同部门，不同岗位的员工查看不同数据不同：即 总经理可以查看所有员工的数据，而员工不可以看到总经理的数据。
为了满足这种情况需要对数据新增和查询做处理：
新增数据的时候记录哪个部门啥子岗位的哪个新增的数据。
用户登录获取自己可以查看哪个部门哪个岗位的数据即数据权限。
获取权限后查询数据，通过数据中记录的部门、岗位、用户id作对比，如果用户的权限包含其中一个既可以查询到该条数据。
实现流程:
数据生成:使用mybatis拦截器记录新增数据的信息。信息包括（用户名称、用户id、用户岗位、用户部门）
权限存储:登录系统存储用户的权限信息
数据查询:当查询的接口有数据权限注解且数据权限功能开启时，权限控制才能运行。
mabitis新增拦截器
实现 Interceptor抽象类 的intercept抽象方法，拦截Executor类的update方法
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class InsertInterceptor implements Interceptor {
private static final String COLUMN_NAME = "dataauthjson";
@Override
public Object intercept(Invocation invocation) throws Throwable {
String processSql = ExecutorPluginUtils.getSqlByInvocation(invocation);
// 执行自定义修改sql操作
// 获取sql
String sql2Reset = processSql;
//忽略sql中包含on conflict的情况
Statement statement = CCJSqlParserUtil.parse(processSql);
//MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
try {
LoginUser userinfo = null;
try {
//获取用户信息
userinfo = ShiroUtil.getUserEntity();
} catch (UnavailableSecurityManagerException e) {
//启动定时任务报错
//e.printStackTrace();
return invocation.proceed();
}
if (userinfo == null) {//没有登录直接放行
// logger.debug("没有登录离开新增拦截器");
return invocation.proceed();
}
//用户信息
DataPermissionJsonClass dataPermissionJsonClass = new DataPermissionJsonClass(userinfo.getId(), userinfo.getUserName(), ShiroUtil.getDepartmentId(), ShiroUtil.getPositionId());
String insertSql;
if (statement instanceof Insert) {
//新增拦截器开始新增替换
Insert insertStatement = (Insert) statement;
//新增字段
List<Column> columns = insertStatement.getColumns();
//sql条件
ItemsList itemsList = insertStatement.getItemsList();
if (itemsList instanceof MultiExpressionList) {
//字段设置 多个values
MultiExpressionList itemsList1 = (MultiExpressionList) itemsList;
List<ExpressionList> exprList = itemsList1.getExprList();
//权限sql
insertSql = "'" + GsonUtil.gsonString(dataPermissionJsonClass) + "'";
for (ExpressionList expressionList : exprList) {
expressionList.getExpressions().add(new Column(insertSql));
}
Column column = new Column();
column.setColumnName(COLUMN_NAME);
columns.add(column);
//添加datauthjson字段
insertStatement.setColumns(columns);
//值设置
insertStatement.setColumns(columns);
insertStatement.setItemsList(itemsList1);
sql2Reset = insertStatement.toString();
} else {
//单个values
ExpressionList itemsList1 = (ExpressionList) itemsList;
boolean flag = true;
for (Column column : columns) {
if (column.getColumnName().equals(COLUMN_NAME)) {
flag = false;
break;
}
}
//无dataauthjson字段
if (flag) {
//添加dataauthjson字段并赋权限数据
columns.add(new Column(COLUMN_NAME));
List<Expression> list = new ArrayList<>();
list.addAll(itemsList1.getExpressions());
insertSql = "'" + GsonUtil.gsonString(dataPermissionJsonClass) + "'";
list.add(new Column(insertSql));
itemsList1.setExpressions(list);
} else {
//给入参赋值
Object parameter = ExecutorPluginUtils.getParameterByInvocation(invocation);
Class<?> aClass = parameter.getClass();
Field declaredField = aClass.getDeclaredField(COLUMN_NAME);
declaredField.setAccessible(true);
insertSql = GsonUtil.gsonString(dataPermissionJsonClass);
//给dataauthjson字段设置值
declaredField.set(parameter, insertSql);
declaredField.setAccessible(false);
}
insertStatement.setItemsList(itemsList);
insertStatement.setColumns(columns);
sql2Reset = insertStatement.toString();
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 替换要执行的sql
ExecutorPluginUtils.resetSql2Invocation(invocation, sql2Reset);
logger.debug("离开新增拦截器");
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
数据查询
系统启动时读取配置获取是否包含数据权限
对接口中有DataPermissionAspect注解进行数据权限拦截
数据权限aop注解
系统启动读取权限配置，开启数据权限的设置为1，未开启的设置未0.
@Aspect
@Component
@Lazy
public class DataPermissionAspect {
public static final ThreadLocal Data_Perm_Local_Thread = new ThreadLocal();
private static Integer flag =0 ;//0没有开启数据权限
static {//获取数据库配置 判断是否开启数据权限
CopyrightMapper copyrightMapper = SpringUtil.getBean(CopyrightMapper.class);
final String permissionConfiKey= "*****";
final String permissionConfigValue = "1";
CopyrightExample example = new CopyrightExample();
example.createCriteria().andCpKeyEqualTo(permissionConfiKey);
List<Copyright> copyrights = copyrightMapper.selectByExample(example);
if(copyrights==null||copyrights.size()==0){
throw new RuntimeException("请联系管理员添加权限配置");
}
if (copyrights.get(0).getCpValue().equals(permissionConfigValue)) {
flag = 1;//为1表示数据权限开启
}
}
@Before("@annotation(com.everyouthtech.oa.foundation.permission.DataPermissionEnabled)")
public void doBefore(JoinPoint point) throws Throwable {
//准备执行切面
Data_Perm_Local_Thread.remove();
String methodName = point.getSignature().getName();
Data_Perm_Local_Thread.set(flag);
// logger.debug("DataPermAspect before 当前方法为：" + methodName);
}
@After("@annotation(com.everyouthtech.oa.foundation.permission.DataPermissionEnabled)")
public void doAfter(JoinPoint point) throws Throwable {
//清空数据权限拼接SQL
// logger.debug("清空线程数据");
Data_Perm_Local_Thread.remove();
}
}
查询拦截器
实现Interceptor抽象类 拦截StatementHandler 中的prepare方法,判断方法中是否含有注解是否开启数据权限，对开启权限的接口查询条件添加查询条件权限。
@SuppressWarnings({"rawtypes", "unchecked"})
@Intercepts(
{
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
}
)
public class DataPermissionInterceptor implements Interceptor {
private static final Logger logger = LoggerFactory.getLogger(DataPermissionInterceptor.class);
@Override
public Object intercept(Invocation invocation) throws Throwable {
// logger.debug("进入查询拦截器");
// 如果未启用数据权限注解，直接放行
Object o = DataPermissionAspect.Data_Perm_Local_Thread.get();
if(o == null){
// logger.debug("无注解离开查询拦截器");
return invocation.proceed();
}
Integer flag =(Integer) o;
if(flag == 0){
//logger.debug("未开启权限拦截离开截器");
return invocation.proceed();
}
// 获取StatementHandler处理器，默认是RoutingStatementHandler
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement ms = (MappedStatement) metaObject.getValue("parameterHandler.mappedStatement");
// 如果不是SELECT操作，直接过滤
if (!SqlCommandType.SELECT.equals(ms.getSqlCommandType())) {
//logger.debug("不是查询 离开查询拦截器");
return invocation.proceed();
}
BoundSql boundSql = (BoundSql) metaObject.getValue("parameterHandler.boundSql");
// 拿到执行的SQL语句
String originalSql = boundSql.getSql();
// SQL语句的参数
//不是分页查询直接过滤
if (!(iscontainsLimit( originalSql)||isCount(ms))) {
// logger.debug("不是分页或者统计离开查询拦截器");
return invocation.proceed();
}
logger.debug("数据权限处理前的sql:"+originalSql);
// 这里对执行SQL进行自定义处理...
String finalSql = this.handleSql2(boundSql, originalSql,flag);
if (StringUtil.isEmpty(finalSql)) {
return invocation.proceed();
}
logger.debug("数据权限处理过后的SQL: " + finalSql);
// 装载改写后的sql
metaObject.setValue("parameterHandler.boundSql.sql", finalSql);
//logger.debug("离开查询拦截器");
return invocation.proceed();
}
public boolean iscontainsLimit(String originalSql){
String s1 = originalSql.toUpperCase();
if(s1.contains(" LIMIT")){
return true;
}
return false;
}
public boolean isCount(MappedStatement ms){
if (ms.getId().contains("countByExample")) {
return true;
}
return false;
}
/**
* 获取sql表名(有别名获取别名，无别获取表名)
* @param originalSql
* @return
* @throws JSQLParserException
*/
private String getMainTable(String originalSql) throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) parserManager.parse(new StringReader(originalSql));
SelectBody selectBody = select.getSelectBody();
if (selectBody instanceof PlainSelect) {
return this.getTableName((PlainSelect) selectBody);
} else if (selectBody instanceof SetOperationList) {
//SetOperationList setOperationList = (SetOperationList) selectBody;
//List<SelectBody> selectBodyList = setOperationList.getSelects();
//SelectBody selectBody1 = selectBodyList.get(0);
return this.getTableName((PlainSelect) selectBody);
}else {
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
return tablesNamesFinder.getTableList(select).get(0);
}
}
private String handleSql2(BoundSql boundSql, String firstSql ,Integer flag) throws JSQLParserException {
//获取用户的权限
SelectSqlClass selectSqlClass = ShiroUtil.getSelectSqlClass();
Integer sqlType = selectSqlClass.getSqlType();
String sqlString = selectSqlClass.getSqlString();
String newSql;
// 获取用户的权限
String condition ;
if(sqlType== PermissionConstant.SQL_TYPE_FINISH){//是否为完整的sql
condition = sqlString;
}
else {
String tablename ="";
tablename = getMainTable(firstSql);
if(!"".equals(tablename)){
tablename+=".";
}
//填入表名
condition = sqlString.replaceAll("%s",tablename);
}
condition = "( "+condition+" )";
newSql = addWhereCondition(firstSql, condition);
return newSql;
}
@SneakyThrows(Exception.class)
protected String getTableName(PlainSelect plainSelect) {
Table fromItem = (Table) plainSelect.getFromItem();
// 有别名用别名，无别名用表名，防止字段冲突报错
Alias fromItemAlias = fromItem.getAlias();
return fromItemAlias == null ? fromItem.getName() : fromItemAlias.getName();
}
/**
* 判断是否分页
*
* @param parameter
* @return
*/
private boolean ifPage(Object parameter) {
if (parameter == null) return false;
if (isBaseType(parameter)) return false;
JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(parameter));
return jsonObject.containsKey("First_PagHeelper") || jsonObject.containsKey("Second_PageHelper");
}
/**
* 判断object是否为基本类型
*
* @param object
* @return
*/
public static boolean isBaseType(Object object) {
Class className = object.getClass();
if (className.equals(Integer.class) ||
className.equals(Byte.class) ||
className.equals(Long.class) ||
className.equals(Double.class) ||
className.equals(Float.class) ||
className.equals(Character.class) ||
className.equals(Short.class) ||
className.equals(Boolean.class)) {
return true;
}
return false;
}
/**
* 在原有的sql中增加新的where条件
*
* @param sql 原sql
* @param conditionsql 新的and条件
* @return 新的sql
*/
private String addWhereCondition(String sql, String conditionsql) {
try {
StringBuilder condition = new StringBuilder(conditionsql);
Select select = (Select) CCJSqlParserUtil.parse(sql);
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
final Expression where = plainSelect.getWhere();
final Expression envCondition = CCJSqlParserUtil.parseCondExpression(conditionsql);
if (Objects.isNull(where)) {
plainSelect.setWhere(envCondition);
} else {
if (condition.length() > 2) {
//where条件之前存在，需要重新进行拼接
condition.append(" and ( " + where.toString() + " )");
}
Expression expression = CCJSqlParserUtil
.parseCondExpression(condition.toString());
plainSelect.setWhere(expression);
}
return plainSelect.toString();
} catch (JSQLParserException e) {
throw new RuntimeException(e);
}
}
/***
* 定义拦截的类 Executor、ParameterHandler、StatementHandler、ResultSetHandler当中的一个
* @param target 需要拦截的类
* @return
*/
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
/**
* 属性相关操作
* 设置和自定义属性值
*
* @param properties 属性值
*/
@Override
public void setProperties(Properties properties) {
}
}
工具类
public class ExecutorPluginUtils {
/**
* 获取sql语句
* @param invocation
* @return
*/
public static String getSqlByInvocation(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
return boundSql.getSql();
}
public static Object getParameterByInvocation(Invocation invocation) {
final Object[] args = invocation.getArgs();
Object parameterObject = args[1];
return parameterObject;
}
/**
* 包装sql后，重置到invocation中
* @param invocation
* @param sql
* @throws SQLException
*/
public static void resetSql2Invocation(Invocation invocation, String sql) throws SQLException {
final Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(),new DefaultReflectorFactory());
msObject.setValue("sqlSource.boundSql.sql", sql);
args[0] = newStatement;
}
private static 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();
}
/**
* 是否标记为区域字段
* @return
*/
public static boolean isAreaTag( MappedStatement mappedStatement) throws ClassNotFoundException {
String id = mappedStatement.getId();
Class<?> classType = Class.forName(id.substring(0,mappedStatement.getId().lastIndexOf(".")));
//获取对应拦截方法名
String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1);
boolean ignore = false;
for(Method method : classType.getDeclaredMethods()){
if(method.isAnnotationPresent(DataPermissionEnabled.class) && mName.equals(method.getName()) ) {
ignore = true;
}
}
if (classType.isAnnotationPresent(DataPermissionEnabled.class) && !ignore) {
return true;
}
return false;
}
/**
* 是否标记为区域字段
* @return
*/
public static boolean isAreaTagIngore( MappedStatement mappedStatement) throws ClassNotFoundException {
String id = mappedStatement.getId();
Class<?> classType = Class.forName(id.substring(0,mappedStatement.getId().lastIndexOf(".")));
//获取对应拦截方法名
String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1);
boolean ignore = false;
for(Method method : classType.getDeclaredMethods()){
if(method.isAnnotationPresent(DataPermissionEnabled .class) && mName.equals(method.getName()) ) {
ignore = true;
}
}
return ignore;
}
public static String getOperateType(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
SqlCommandType commondType = ms.getSqlCommandType();
if (commondType.compareTo(SqlCommandType.SELECT) == 0) {
return "select";
}
if (commondType.compareTo(SqlCommandType.INSERT) == 0) {
return "insert";
}
if (commondType.compareTo(SqlCommandType.UPDATE) == 0) {
return "update";
}
if (commondType.compareTo(SqlCommandType.DELETE) == 0) {
return "delete";
}
return null;
}
// 定义一个内部辅助类，作用是包装sq
static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
权限存储类
public class DataPermissionCollection implements Serializable {
private Long dataPermissionType;
private List departmentIds;
private List positionIds;
}
sql数据存储类
public class SelectSqlClass implements Serializable {
private Integer sqlType;
private String sqlString;
private String continsDpParentIdSql;
}
权限存值
public SelectSqlClass getSqlClass(DataPermissionCollection dataPermissionCollection, Long userId) {
//封装sql
SelectSqlClass selectSqlClass = new SelectSqlClass();
String userSql;
String positionSql;
String departSql;
String continstDpSql;
boolean dbflag = dbConfig.equals("dm.jdbc.driver.DmDriver");
if (dbflag) {
//达梦数据库查询条件
userSql = "JSON_VALUE(%sdataauthjson,'$.createdby') =" + userId;
positionSql = "JSON_VALUE(%sdataauthjson,'$.createdby_positionIds') in '%s'";
departSql = "JSON_VALUE(%sdataauthjson,'$.createdby_deptIds') in '%s'";
continstDpSql = "JSON_VALUE(%sdataauthjson,'$.createdby_deptIds') = ";
} else {
//人大金仓、mysql
departSql = "JSON_EXTRACT(%sdataauthjson,'$.createdby_deptIds') in %s ";
userSql = "JSON_EXTRACT(%sdataauthjson,'$.createdby') =" + userId;
continstDpSql = "JSON_EXTRACT(%sdataauthjson,'$.createdby_deptIds') = ";
}
List<Long> departmentIds = dataPermissionCollection.getDepartmentIds();
List<Long> positionIds = dataPermissionCollection.getPositionIds();
//无数据权限
if (dataPermissionCollection == null || dataPermissionCollection.getDataPermissionType() == null || dataPermissionCollection.getDataPermissionType() == PermissionConstant.DATA_PERMISSION_TYPE_NONE) {
selectSqlClass.setSqlString("1=2");
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_FINISH);
}
//所有数据权限
else if (dataPermissionCollection.getDataPermissionType() == PermissionConstant.DATA_PERMISSION_TYPE_ALL) {
selectSqlClass.setSqlString("1=1");
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_FINISH);
}
//有自己有（部门或岗位)
else if (dataPermissionCollection.getDataPermissionType() == PermissionConstant.DATA_PERMISSION_TYPE_OWNER_DEPARTMENT_POSITION) {
String addsql = userSql;
if (departmentIds != null && departmentIds.size() > 0) {
String sqlappend;
sqlappend = String.format(departSql, "%s", toInsql(departmentIds));
addsql = addsql + " or " + sqlappend;
}
if (positionIds != null && positionIds.size() > 0) {
String sqlappend;
sqlappend = String.format(positionSql, "%s", toInsql(positionIds));
addsql = addsql + " or " + sqlappend;
}
selectSqlClass.setSqlString(addsql);
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_REPLACE);
}
//只有自己的权限
else if (dataPermissionCollection.getDataPermissionType() == PermissionConstant.DATA_PERMISSION_TYPE_OWNER) {
selectSqlClass.setSqlString(userSql);
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_REPLACE);
}
//只有部门或岗位
else if (dataPermissionCollection.getDataPermissionType() == PermissionConstant.DATA_PERMISSION_TYPE_DEPARTMENT_POSITION) {
String addsql = "";
if (departmentIds != null && departmentIds.size() > 0) {
String sqlappend;
sqlappend = String.format(departSql, "%s", toInsql(departmentIds));
addsql = sqlappend;
}
if (positionIds != null && positionIds.size() > 0) {
String sqlappend;
sqlappend = String.format(positionSql, "%s", toInsql(positionIds));
if (!"".equals(addsql)) {
addsql = addsql + " or " + sqlappend;
} else {
addsql += sqlappend;
}
}
if ("".equals(addsql)) {
addsql = "1=2";
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_FINISH);
}
selectSqlClass.setSqlString(addsql);
selectSqlClass.setSqlType(PermissionConstant.SQL_TYPE_REPLACE);
}
try {
UserInfo userInfo = userInfoMapper.selectByPrimaryKey(userId);
DepartmentExample example = new DepartmentExample();
example.createCriteria().andIdEqualTo(userInfo.getId());
List<Department> departments = departmentMapper.selectByExample(example);
long l = departments == null || departments.size() == 0 ? -1 : departments.get(0).getParentId();
selectSqlClass.setContinsDpParentIdSql(selectSqlClass.getSqlString() + "or" + l);
} catch (Exception e) {
e.printStackTrace();
}
return selectSqlClass;
}
总结
使用新增和查询分离的方式实现数据权限可灵活的配置权限是否生效。
核心思想为sql替换。
优化方案：aop处理时为本地线程设置值时，由设置单一的flag改为使用map集合。该方式可存储更多数据，使用一个key判断是否开启数据权限;再使用一个key记录切点出现的次数，前置通知时次数加一，后置通知时次数减一，通过次数值判断是否添加了注解。
943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
