一、需求

我们在做项目的时候，通常会根据不同的账号登录进去，展示的菜单和列表不同，这是因为我们在后端根据定义的角色权限，来筛选不同的数据。我们来看看我们Before和After是如何做的。

二、Before

在以前我发现项目中是通过数据库来控制权限的。例如现在有这样的需求不同的账号根据部门权限，看到指定部门权限的数据。

1、通过Controller查询当前账户所在部门及其下属部门，传入mybaits，进行筛选

  @RequiresPermissions("facility:plan:list")
  @PostMapping("/list")
  @ResponseBody
  public TableDataInfo list(Plan plan) {
      // 查询当前账户所在部门及其下属部门
      SysUser sysUser = ShiroUtils.getSysUser();
      long deptId = sysUser.getDeptId()==101? 100:sysUser.getDeptId();
      SysDept dept = deptService.selectDeptById(deptId);
      List<SysDept> deptList = deptService.selectChildrenDeptById(deptId);
      deptList.add(dept);
      startPage();
      List<Plan> list = planService.selectPlanList(plan,deptList);
      return getDataTable(list);
  }

2、通过sql语句将部门id作为参数，来过滤数据

<select id="selectPlanList" parameterType="Plan" resultMap="PlanResult">
    <include refid="selectPlanVo"/>
    <where>
        <if test="plan.name != null  and plan.name != '' "> and name like #{plan.name}</if>
        and unitId in
        <foreach collection="deptList" item="dept" index="index" open="(" close=")" separator=",">
            #{dept.deptId}
        </foreach>
    </where>
</select>

三、After

通过上面我们会发现，在每一个需要用到部门权限的地方，都要去数据库查一下，然后在用到的地方进行过滤，这不仅增加数据库的访问次数，还会带来代码上的冗余。

1、自定义注解@DataScope，参数中传入关联部门表的别名

    /**
     * 查询车辆列表
     *
     * @param optCar 车辆档案
     * @return
     */
    @Override
    @DataScope(deptAlias = "d")
    public List<OptCar> selectCarList(OptCar optCar)
    {
        return optCarMapper.selectCarList(optCar);
    }

2、定义的实体类中需要继承BaseEntity，因为需要传入 params 参数。

@Data
public class OptCar extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /** 主键 */
    private Integer id;
    /** 车辆编号 */
    @Excel(name = "车辆编号")
    private String carCode;
    /** 车牌号 */
    @Excel(name = "车牌号")
    private String carNum;
    /** 车辆类型 */
    @Excel(name = "车辆类型")
    private String carType;
    /** 地址 */
    @Excel(name = "地址")
    private String address;
    /** 负责人id */
    private Integer userId;
    /** 负责人名称 */
    @Excel(name = "负责人名称")
    private String userName;
    /** 负责人电话 */
    @Excel(name = "负责人电话")
    private String phone;
    /** 组织id */
    private Integer unitId;
    /** 组织name */
    private String deptName;
    /** 经度 */
    private String lng;
    /** 纬度 */
    private String lat;
    /**保险到期日*/
    @JsonFormat(pattern = "yyyy-MM-dd")
    private String insureTime; // 保险到期日
    /**年检到期日*/
    @JsonFormat(pattern = "yyyy-MM-dd")
    private String checkTime; // 年检到期日
    /**保险公司名*/
    private String insureCompany; // 保险公司名
    /**保险公司号码*/
    private String insureNum; // 保险公司号码

    private String objectId;

}

3、Mybatis进行查询时，关联部门表，并取别名和注解传入的参数相同。然后在where子句中传入param.dataScope参数

<select id="selectCarList" resultType="com.dne.operation.car.domain.OptCar">
    SELECT a.id as id,
           a.car_code as carCode,
           a.car_num as carNum,
           a.car_type as carType,
           a.address as address,
           a.user_id as userId,
           a.user_name as userName,
           a.phone as phone,
           a.unit_id as unitId,
           a.lng as lng,
           a.lat as lat,
           a.insureTime as insureTime,
           a.checkTime as checkTime,
           a.insureCompany as insureCompany,
           a.insureNum as insureNum,
           a.object_id as object,
           d.dept_name as deptName
    FROM op_car a LEFT JOIN sys_dept d ON a.unit_id = d.dept_id
    <where>
        <if test="id != null"> AND id = #{optCar.id}</if>
        <if test="carCode != null and carCode != ''"> AND a.car_code LIKE CONCAT('%',#{carCode},'%')</if>
        <if test="carNum != null and carNum != ''"> AND a.car_num LIKE CONCAT('%',#{carNum},'%')</if>
        <if test="carType != null  and carType != ''"> AND a.car_type = #{carType}</if>
        <if test="address != null  and address != ''"> AND a.address = #{address}</if>
        <if test="userId != null and userId != ''"> AND a.user_id = #{userId}</if>
        <if test="userName != null and userName != ''"> AND a.user_name = #{userName}</if>
        <if test="phone != null  and phone != ''"> AND a.phone = #{phone}</if>
        <if test="lng != null  and lng != ''"> AND a.lng = #{lng}</if>
        <if test="lat != null  and lat != ''"> AND a.lat = #{lat}</if>
        <if test="insureTime != null  and insureTime != ''"> AND a.insureTime = #{insureTime}</if>
        <if test="checkTime != null  and checkTime != ''"> AND a.checkTime = #{checkTime}</if>
        <if test="insureCompany != null  and insureCompany != ''"> AND a.insureCompany = #{insureCompany}</if>
        <if test="insureNum != null  and insureNum != ''"> AND a.insureNum = #{insureNum}</if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </where>
</select>

四、@DataScope注解详解

我们来点开@DataScope，可以看到他的参数有部门表的别名、用户表的别名、权限字符。当然权限字符我们可以通过其他注解来控制，这里就不说了。

/**
 * 数据权限过滤注解
 *
 * @author system
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope
{
    /**
     * 部门表的别名
     */
    public String deptAlias() default "";

    /**
     * 用户表的别名
     */
    public String userAlias() default "";

    /**
     * 权限字符（用于多个角色匹配符合要求的权限）默认根据权限注解@RequiresPermissions获取，多个权限用逗号分隔开来
     */
    public String permission() default "";
}

DataScope底层是通过aop来实现的

/**
 * 数据过滤处理
 *
 * @author system
 */
@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_SELF = "5";

    /**
     * 数据权限过滤关键字
     */
    public static final String DATA_SCOPE = "dataScope";

    @Before("@annotation(controllerDataScope)")
    public void doBefore(JoinPoint point, DataScope controllerDataScope) throws Throwable
    {
        clearDataScope(point);
        handleDataScope(point, controllerDataScope);
    }

    protected void handleDataScope(final JoinPoint joinPoint, DataScope controllerDataScope)
    {
        // 获取当前的用户
        LoginUser loginUser = SecurityUtils.getLoginUser();
        if (StringUtils.isNotNull(loginUser))
        {
            SysUser currentUser = loginUser.getSysUser();
            // 如果是超级管理员，则不过滤数据
            if (StringUtils.isNotNull(currentUser) && !currentUser.isAdmin())
            {
                String permission = StringUtils.defaultIfEmpty(controllerDataScope.permission(), SecurityContextHolder.getPermission());
                dataScopeFilter(joinPoint, currentUser, controllerDataScope.deptAlias(),
                        controllerDataScope.userAlias(), permission);
            }
        }
    }

    /**
     * 数据范围过滤
     *
     * @param joinPoint 切点
     * @param user 用户
     * @param deptAlias 部门别名
     * @param userAlias 用户别名
     * @param permission 权限字符
     */
    public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, String deptAlias, String userAlias, String permission)
    {
        StringBuilder sqlString = new StringBuilder();
        List<String> conditions = new ArrayList<String>();

        for (SysRole role : user.getRoles())
        {
            String dataScope = role.getDataScope();
            if (!DATA_SCOPE_CUSTOM.equals(dataScope) && conditions.contains(dataScope))
            {
                continue;
            }
            if (StringUtils.isNotEmpty(permission) && StringUtils.isNotEmpty(role.getPermissions())
                    && !StringUtils.containsAny(role.getPermissions(), Convert.toStrArray(permission)))
            {
                continue;
            }
            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 = {} ) ", deptAlias,
                        role.getRoleId()));
            }
            else if (DATA_SCOPE_DEPT.equals(dataScope))
            {
                sqlString.append(StringUtils.format(" OR {}.dept_id = {} ", deptAlias, user.getDeptId()));
            }
            else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope))
            {
                sqlString.append(StringUtils.format(
                        " OR {}.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = {} or find_in_set( {} , ancestors ) )",
                        deptAlias, user.getDeptId(), user.getDeptId()));
            }
            else if (DATA_SCOPE_SELF.equals(dataScope))
            {
                if (StringUtils.isNotBlank(userAlias))
                {
                    sqlString.append(StringUtils.format(" OR {}.user_id = {} ", userAlias, user.getUserId()));
                }
                else
                {
                    // 数据权限为仅本人且没有userAlias别名不查询任何数据
                    sqlString.append(StringUtils.format(" OR {}.dept_id = 0 ", deptAlias));
                }
            }
            conditions.add(dataScope);
        }

        if (StringUtils.isNotBlank(sqlString.toString()))
        {
            Object params = joinPoint.getArgs()[0];
            if (StringUtils.isNotNull(params) && params instanceof BaseEntity)
            {
                BaseEntity baseEntity = (BaseEntity) params;
                baseEntity.getParams().put(DATA_SCOPE, " AND (" + sqlString.substring(4) + ")");
            }
        }
    }

    /**
     * 拼接权限sql前先清空params.dataScope参数防止注入
     */
    private void clearDataScope(final JoinPoint joinPoint)
    {
        Object params = joinPoint.getArgs()[0];
        if (StringUtils.isNotNull(params) && params instanceof BaseEntity)
        {
            BaseEntity baseEntity = (BaseEntity) params;
            baseEntity.getParams().put(DATA_SCOPE, "");
        }
    }
}