数据权限实现

╰半橙微兮° 2023-01-22 04:54 2阅读 0赞

> 权限框架可以根据用户所属角色决定有权限看到的菜单资源权限。  
> 同一个资源下的同一个菜单的数据权限需要单独处理。

> 案例：一部门的张三和二部门的李四都是普通用户角色，普通用户都有用户管理的查询权限，但是，一部门的张三只能看到一部门以及一部门下面的数据的权限。

> 效果图：  
> 测试部门的test001用户之后能看到自己部门的数据，需求部门的数据看不到  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70]  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 1]

### 文章目录 ###

*   *   *   *   *  一、数据权限模型
                 *   *  1. 实现原理
                     *  2. 实现流程
                     *  3. 权限标识对象
                     *  4. 查询SQL拦截器
                     *  5. 查询SQL特殊处理
                 *  二、数据权限使用
                 *   *  2.1. 控制层
                     *  2.2. service
                     *  2.3. mapper
                     *  2.4. 获取部门集合
                     *  2.5.
                 *  三、实战演练
                 *   *  3.1. 获取当前用户的所属部门
                     *  3.2. 获取当前用户的所属部门以及子部门集合
                     *  3.3. 调用逻辑service层执行查询逻辑
                     *  3.4. 调用mapper执行查询逻辑
                     *  3.5. 在查询数据库之前拦截处理
                     *  3.6. 未处理的原sql
                     *  3.7. 处理后的sql

##### 一、数据权限模型 #####

###### 1. 实现原理 ######

1.创建一个需要执行数据权限的标志
    2.根据数据权限标识对原查询SQL，在查询数据库之前进行特殊处理
    3.利用拦截器在使用mapper在查询数据库时进行拦截处理
      1>获取所有的查询SQL
      2>获取查询sql中的的数据权限标识
        无：放行
        有：动态拼接数据权限sql

###### 2. 实现流程 ######

1.创建一个需要执行数据权限的标志对象DataScope
    2.除超级管理员外，进行数据权限处理
    3.获取当前用户的所处部门ID,根据部门ID获取当前部门以及子部门的ID集合
    4.创建数据范围的拦截器DataScopeInterceptor
    5.再判断根据数据权限标识对原查询SQL，利用拦截器在使用mapper在查询数据库时进行拦截处理
      1>获取所有的查询SQL
      2>获取查询sql中的的数据权限标识
        无：放行
        有：动态拼接数据权限sql
        简言之：将源sql看做一个查询整体，最后将结果集按照部门ids集合进行模糊区配筛选出（当前部门以及子部门的权限范围）

###### 3. 权限标识对象 ######

package cn.stylefeng.roses.core.datascope;
    
    import java.util.List;
    
    /**
     * 数据范围
     *
     * @author fengshuonan
     * @date 2017-07-23 22:19
     */
    public class DataScope { 
    
        /**
         * 限制范围的字段名称
         */
        private String scopeName = "deptid";
    
        /**
         * 具体的数据范围
         */
        private List<Long> deptIds;
    
        public DataScope() { 
        }
    
        public DataScope(List<Long> deptIds) { 
            this.deptIds = deptIds;
        }
    
        public DataScope(String scopeName, List<Long> deptIds) { 
            this.scopeName = scopeName;
            this.deptIds = deptIds;
        }
    
        public List<Long> getDeptIds() { 
            return deptIds;
        }
    
        public void setDeptIds(List<Long> deptIds) { 
            this.deptIds = deptIds;
        }
    
        public String getScopeName() { 
            return scopeName;
        }
    
        public void setScopeName(String scopeName) { 
            this.scopeName = scopeName;
        }
    }

###### 4. 查询SQL拦截器 ######

###### 5. 查询SQL特殊处理 ######

package cn.stylefeng.roses.core.datascope;
    
    
    import cn.hutool.core.collection.CollectionUtil;
    import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
    import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.mapping.BoundSql;
    import org.apache.ibatis.mapping.MappedStatement;
    import org.apache.ibatis.mapping.SqlCommandType;
    import org.apache.ibatis.plugin.*;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.reflection.SystemMetaObject;
    
    import java.sql.Connection;
    import java.util.List;
    import java.util.Map;
    import java.util.Properties;
    
    /**
     * 数据范围的拦截器
     *
     * @author fengshuonan
     * @date 2017-07-23 21:26
     */
    @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class})})
    public class DataScopeInterceptor implements Interceptor { 
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable { 
            StatementHandler statementHandler = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
            MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
    
            if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) { 
                return invocation.proceed();
            }
    
            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
            String originalSql = boundSql.getSql();
            Object parameterObject = boundSql.getParameterObject();
    
            //查找参数中包含DataScope类型的参数
            DataScope dataScope = findDataScopeObject(parameterObject);
    
            if (dataScope == null) { 
                return invocation.proceed();
            } else { 
                String scopeName = dataScope.getScopeName();
                List<Long> deptIds = dataScope.getDeptIds();
                String join = CollectionUtil.join(deptIds, ",");
                originalSql = "select * from (" + originalSql + ") temp_data_scope where temp_data_scope." + scopeName + " in (" + join + ")";
                metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
                return invocation.proceed();
            }
        }
    
        /**
         * 查找参数是否包括DataScope对象
         */
        public DataScope findDataScopeObject(Object parameterObj) { 
            if (parameterObj instanceof DataScope) { 
                return (DataScope) parameterObj;
            } else if (parameterObj instanceof Map) { 
                for (Object val : ((Map<?, ?>) parameterObj).values()) { 
                    if (val instanceof DataScope) { 
                        return (DataScope) val;
                    }
                }
            }
            return null;
        }
    
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) { 
    
        }
    }

##### 二、数据权限使用 #####

###### 2.1. 控制层 ######

/**
         * 查询管理员列表
         *
         * @author gblfy
         * @Date 2020/12/24 22:43
         */
        @RequestMapping("/list")
        @Permission
        @ResponseBody
        public Object list(@RequestParam(required = false) String name,
                           @RequestParam(required = false) String timeLimit,
                           @RequestParam(required = false) Long deptId) { 
    
            //拼接查询条件
            String beginTime = "";
            String endTime = "";
    
            if (ToolUtil.isNotEmpty(timeLimit)) { 
                String[] split = timeLimit.split(" - ");
                beginTime = split[0];
                endTime = split[1];
            }
    
            if (ShiroKit.isAdmin()) { 
                Page<Map<String, Object>> users = userService.selectUsers(null, name, beginTime, endTime, deptId);
                Page wrapped = new UserWrapper(users).wrap();
                return LayuiPageFactory.createPageInfo(wrapped);
            } else { 
                DataScope dataScope = new DataScope(ShiroKit.getDeptDataScope());
                Page<Map<String, Object>> users = userService.selectUsers(dataScope, name, beginTime, endTime, deptId);
                Page wrapped = new UserWrapper(users).wrap();
                return LayuiPageFactory.createPageInfo(wrapped);
            }
        }

###### 2.2. service ######

/**
         * 根据条件查询用户列表
         *
         * @author gblfy
         * @Date 2020/12/24 22:45
         */
        public Page<Map<String, Object>> selectUsers(DataScope dataScope, String name, String beginTime, String endTime, Long deptId) { 
            Page page = LayuiPageFactory.defaultPage();
            return this.baseMapper.selectUsers(page, dataScope, name, beginTime, endTime, deptId);
        }

###### 2.3. mapper ######

/**
         * 根据条件查询用户列表
         */
        Page<Map<String, Object>> selectUsers(@Param("page") Page page, @Param("dataScope") DataScope dataScope, @Param("name") String name, @Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("deptId") Long deptId);

<select id="selectUsers" resultType="map">
            select
            <include refid="Base_Column_List"/>
            from sys_user
            where status != 'DELETED'
            <if test="name != null and name != ''">
                and (phone like CONCAT('%',#{name},'%')
                or account like CONCAT('%',#{name},'%')
                or name like CONCAT('%',#{name},'%'))
            </if>
            <if test="deptId != null and deptId != 0">
                and (dept_id = #{deptId} or dept_id in ( select dept_id from sys_dept where pids like CONCAT('%$[', #{deptId}, '$]%') escape '$' ))
            </if>
            <if test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
                and (create_time between CONCAT(#{beginTime},' 00:00:00') and CONCAT(#{endTime},' 23:59:59'))
            </if>
        </select>

###### 2.4. 获取部门集合 ######

/**
         * 获取当前用户的部门数据范围的集合
         */
        public static List<Long> getDeptDataScope() { 
            Long deptId = getUser().getDeptId();
            List<Long> subDeptIds = ConstantFactory.me().getSubDeptId(deptId);
            subDeptIds.add(deptId);
            return subDeptIds;
        }
    
       /**
         * 获取子部门id
         */
     @Override
        public List<Long> getSubDeptId(Long deptId) { 
            ArrayList<Long> deptIds = new ArrayList<>();
    
            if (deptId == null) { 
                return deptIds;
            } else { 
                List<Dept> depts = this.deptMapper.likePids(deptId);
                if (depts != null && depts.size() > 0) { 
                    for (Dept dept : depts) { 
                        deptIds.add(dept.getDeptId());
                    }
                }
    
                return deptIds;
            }
        }

###### 2.5. ######

##### 三、实战演练 #####

###### 3.1. 获取当前用户的所属部门 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]

###### 3.2. 获取当前用户的所属部门以及子部门集合 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 4]

###### 3.3. 调用逻辑service层执行查询逻辑 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 5]

###### 3.4. 调用mapper执行查询逻辑 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 6]

###### 3.5. 在查询数据库之前拦截处理 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 7]

###### 3.6. 未处理的原sql ######

select
             
            user_id AS "userId", avatar AS "avatar", account AS "account", salt AS "salt", name AS "name", birthday AS "birthday", sex AS "sex", email AS "email", phone AS "phone", role_id AS "roleId", dept_id AS "deptId", status AS "status", create_time AS "createTime", create_user AS "createUser", update_time AS "updateTime", update_user AS "updateUser", version AS "version"
         
            from sys_user
            where status != 'DELETED'

###### 3.7. 处理后的sql ######

select * from (select
             
            user_id AS "userId", avatar AS "avatar", account AS "account", salt AS "salt", name AS "name", birthday AS "birthday", sex AS "sex", email AS "email", phone AS "phone", role_id AS "roleId", dept_id AS "deptId", status AS "status", create_time AS "createTime", create_user AS "createUser", update_time AS "updateTime", update_user AS "updateUser", version AS "version"
         
            from sys_user
            where status != 'DELETED') temp_data_scope where temp_data_scope.deptid in (26)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70]: /images/20221020/2637540158b040feb30aa16f9ca5bc5e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 1]: /images/20221020/7d5373616d82418e8e9e062e0bf5e8be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]: /images/20221020/901c04911b2748c7b199627263206db0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 3]: /images/20221020/a9e499fd54bf4e3697a136b68e39dbdd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 4]: /images/20221020/36198fdc4b9c45d985f600e4f09d26b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 5]: /images/20221020/a268d5ab0d2441779f6c2aba10b02d32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 6]: /images/20221020/6afdeadd5e2541c998fcdcd2cbeff13e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 7]: /images/20221020/585e1798cff9422cb13b2c8b80afe287.png