1️⃣、项目使用场景:
根据不同角色的登录用户,查看不同的列表数据
2️⃣、思路
因为使用的是ruoyi若依框架,所以第一个想到的就是使用@DataScope注解,用来控制数据权限的
3️⃣、具体使用方法
使用起来很简单:
1、在你的实现类的方法上加上该注解 @DataScope(deptAlias = “d”)
@Override
@DataScope(deptAlias = "d")
public List<BackupQueueManage> selectBackupQueueManageList(BackupQueueManage backupQueueManage) {
List<BackupQueueManage> backupQueueManages = backupQueueManageMapper.selectBackupQueueManageList(backupQueueManage);
for (BackupQueueManage manage : backupQueueManages) {
String linkScriptId = manage.getLinkScriptId();
if(StringUtils.isBlank(linkScriptId)){
linkScriptId = configService.selectConfigByKey(DB2_BACKUP_SCRIPT);
}
......
}
return backupQueueManages;
}
12345678910111213
2、在mybatis的xml文件中加上 ${params.dataScope}
<sql id="selectBackupQueueManageVo">
select queue_id, queue_name from backup_queue_manage d
</sql>
<
select id="selectBackupQueueManageList" parameterType="BackupQueueManage" resultMap="BackupQueueManageResult">
<include refid="selectBackupQueueManageVo"/>
<where>
<if test="queueName != null and queueName != ''"> and queue_name like concat('%', #{queueName}, '%')</if>
<if test="scheduleType != null and scheduleType != ''"> and schedule_type = #{scheduleType}</if>
<if test="executorTime != null and executorTime != ''"> and executor_time = #{executorTime}</if>
<if test="dbIds != null and dbIds != ''"> and db_ids = #{dbIds}</if>
<if test="jobId != null "> and job_id = #{jobId}</if>
<if test="isOpen != null and isOpen != ''"> and is_open = #{isOpen}</if>
<if test="dbType != null and dbType != ''"> and db_type = #{dbType}</if>
<!-- 数据范围过滤 -->
${params.dataScope}
</where>
</select>
1234567891011121314151617181920
4️⃣、遇到的问题
问题1、我们访问页面的时候,发现如下报错信息
Error querying database. Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery'
The error may exist in URL [jar:file:/opt/hidata/hidbm-vue/paas.jar!/BOOT-INF/classes!/mapper/BackupQueueManageMapper.xml] ###
The error may involve com.hidata.devops.selfops.mapper.BackupQueueManageMapper.selectBackupQueueManageList-Inline ### The error occurred while setting parameters
SQL: SELECT count(0) FROM backup_queue_manage WHERE (d.dept_id IN (SELECT dept_id FROM sys_dept WHERE dept_id = 100 OR find_in_set(NULL, ancestors)))
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery' ; bad SQL grammar [];
nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'd.dept_id' in 'IN/ALL/ANY subquery'
123456
原因是因为:我们的表中没有加dept_id字段
问题2、报错信息如下:
Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN
( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4 The error may exist in URL
[jar:file:/opt/hidata/hidbm-vue/paas.jar!/BOOT-INF/classes!/mapper/BackupQueueManageMapper.xml] The error may involve
com.hidata.devops.selfops.mapper.BackupQueueManageMapper.selectBackupQueueManageList-Inline The error occurred while
setting parameters SQL: select count(0) from ( select queue_id, queue_name, schedule_type, executor_time, db_ids, job_id, create_time, is_open,link_script_id
,db_type, create_by, update_time, update_by from backup_queue_manage WHERE (.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ancestors ) )) ) tmp_count Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 101 or find_in_set( 101 , ance' at line 4
通过报错信息,我们发现这么一句 WHERE (.dept_id ,说明sql语句中没有加别名,只要加上即可:select queue_id, queue_name from backup_queue_manage d
5️⃣、若依前台配置用户对应的角色权限
(1)打开角色管理菜单,创建角色,选择该角色可以查看的菜单权限
(2)、分配该角色对应的数据权限
(3)、新增用户,并关联你的角色
6️⃣、若依@DataScope注解底层代码实现
(1)mysql表sys_role中,有一个data_scope字段,该字段为角色对应的数据权限标识
(2)dataScope表示的含义
/**
* 全部数据权限
*/
public static final String DATA_SCOPE_ALL = "1";
/**
* 自定数据权限
*/
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";
(3)后台根据登录用户角色的dataScope动态的拼接sql语句,来进行数据范围的过滤
public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, String alias)
{
StringBuilder sqlString = new StringBuilder();
for (SysRole role : user.getRoles())
{
String dataScope = role.getDataScope();
if (DATA_SCOPE_ALL.equals(dataScope))
{
sqlString = new StringBuilder();
break;
}
else if (DATA_SCOPE_CUSTOM.equals(dataScope))
{
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", alias,
role.getRoleId()));
}
else if (DATA_SCOPE_DEPT.equals(dataScope))
{
sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", alias, user.getDeptId()));
}
else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope))
{
String deptChild = user.getDept().getParentId() + "," + user.getDeptId();
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or ancestors LIKE '%{}%' )",
alias, user.getDeptId(), deptChild));
}
}
if (StringUtils.isNotBlank(sqlString.toString()))
{
BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
baseEntity.getParams().put(DATA_SCOPE, " AND (" + sqlString.substring(4) + ")");
}
}
(4)、整体逻辑,是采用面向切面编程的思想(AOP),通过注解式开发,来达到数据过滤的作用,关键核心代码在DataScopeAspect类中,具体如下:
package com.ruoyi.framework.aspectj;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import com.ruoyi.common.annotation.DataScope;
import com.ruoyi.common.core.domain.BaseEntity;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.framework.util.ShiroUtils;
import com.ruoyi.system.domain.SysRole;
import com.ruoyi.system.domain.SysUser;
/**
* 数据过滤处理
*
* @author ruoyi
*/
@Aspect
@Component
public class DataScopeAspect
{
/**
* 全部数据权限
*/
public static final String DATA_SCOPE_ALL = "1";
/**
* 自定数据权限
*/
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 = "dataScope";
// 配置织入点
@Pointcut("@annotation(com.ruoyi.common.annotation.DataScope)")
public void dataScopePointCut()
{
}
@Before("dataScopePointCut()")
public void doBefore(JoinPoint point) throws Throwable
{
handleDataScope(point);
}
protected void handleDataScope(final JoinPoint joinPoint)
{
// 获得注解
DataScope controllerDataScope = getAnnotationLog(joinPoint);
if (controllerDataScope == null)
{
return;
}
// 获取当前的用户
SysUser currentUser = ShiroUtils.getSysUser();
if (currentUser != null)
{
// 如果是超级管理员,则不过滤数据
if (!currentUser.isAdmin())
{
dataScopeFilter(joinPoint, currentUser, controllerDataScope.tableAlias());
}
}
}
/**
* 数据范围过滤
*
* @param joinPoint 切点
* @param user 用户
* @param alias 别名
*/
public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, String alias)
{
StringBuilder sqlString = new StringBuilder();
for (SysRole role : user.getRoles())
{
String dataScope = role.getDataScope();
if (DATA_SCOPE_ALL.equals(dataScope))
{
sqlString = new StringBuilder();
break;
}
else if (DATA_SCOPE_CUSTOM.equals(dataScope))
{
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = {} ) ", alias,
role.getRoleId()));
}
else if (DATA_SCOPE_DEPT.equals(dataScope))
{
sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", alias, user.getDeptId()));
}
else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope))
{
String deptChild = user.getDept().getParentId() + "," + user.getDeptId();
sqlString.append(StringUtils.format(
" OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or ancestors LIKE '%{}%' )",
alias, user.getDeptId(), deptChild));
}
}
if (StringUtils.isNotBlank(sqlString.toString()))
{
BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
baseEntity.getParams().put(DATA_SCOPE, " AND (" + sqlString.substring(4) + ")");
}
}
/**
* 是否存在注解,如果存在就获取
*/
private DataScope getAnnotationLog(JoinPoint joinPoint)
{
Signature signature = joinPoint.getSignature();
MethodSignature methodSignature = (MethodSignature) signature;
Method method = methodSignature.getMethod();
if (method != null)
{
return method.getAnnotation(DataScope.class);
}
return null;
}
}