sql横向拼接时会缺少行_数据权限设计研究行数据权限

最新推荐文章于 2023-07-25 09:06:25 发布

Ms 陈

最新推荐文章于 2023-07-25 09:06:25 发布

阅读量108

点赞数

文章标签： sql横向拼接时会缺少行

本文链接：https://blog.csdn.net/weixin_36057741/article/details/113320617

版权

关于权限设计

一般来说，权限模块对于每一个系统而言都是最基础的模块，根据项目需求和功能的不同，设计方案也有许多。但从大的方面来说，可以将权限分为两大类型：功能权限和数据权限

功能权限

主要控制不同的资源主体(用户、角色、组织等)有操作不同的资源的权限。比如常见的不同的角色能访问不同的页面(菜单权限)，以及具有操作同一页面的不同功能(按钮权限)等等。对于java开发而言，功能权限的开发相对来说要简单很多，有很多现成的框架可以实现。我推荐用shiro，因为简单易用，而且能实现按钮级别的控制。

数据权限

主要控制不同的资源主体(用户、角色、组织等)有查看不同的数据信息的权限。数据权限又分为数据行权限和数据列权限，本篇文章主要研究一下数据行权限的控制。

前提

数据权限一般和业务的关系非常紧密，可能不同的业务有不同的设计方案，所以很难有一种统一而使用简单的设计方案。我的想法是：基于角色-部门的控制方式。即拥有某个角色的人，能看见当前角色所包含的部门中的数据。为了更好的设计数据权限，我总结了一下几种数据。

数据分类

公开数据：字面意思，就是公开的数据，不需要控制数据权限。

部门数据：属于某个部门的数据，只有部门的人员可以查看。

私有数据：用户自己的数据，只能自己查看。

几种场景

某条数据属于多个部门的情况。

某领导可以跨部门查看数据。

可以查看子部门的数据。

私有数据可以分享给别人，部门，或者公开。

设计方案与思路

百度上一堆关于数据权限的设计方案，基本上都是基于用户-角色-部门这个来设计，我的思路也和这个差不多，用户与数据角色挂钩，数据角色与部门挂钩，这就比直接角色与部门挂钩要相对灵活一些。虽然某个用户只能属于一个部门，但是有可能出现上面提到的第2中场景，跨部门的情况。

我的设计思路是提供一个方法，写业务的人员需要将查询的表名传给这个方法，然后我返回一段sql,这段sql只是在原来的表上进行数据权限过滤，返回的数据字段和原表一模一样，然后业务代码编写者再把这个sql作为参数传递到DAO层，拼接到FROM后面或者JOIN后面即可。这样无论是单表查询还是多表查询，都可以实现数据权限控制。

还有一种思路是就是用mybatis拦截器去拦截sql，然后对sql进行改造拼接，但是这样我需要去拦截每一条select的sql,可能会对性能有影响。

为了少改原有的业务表，同时统一对系统中的表进行数据权限控制，我设计了一张映射表，来映射数据表，部门，用户之间的关系。

映射表

映射表字段如下

主键字段为字符串纯属个人习惯。有了这张映射表，我们就能根据映射表中的部门ID和用户ID是否为空来进行数据权限的识别，同时也能修改数据的所属权限(公开，部门，私有)。

因为有私有数据分享给其他人或者部门，单条数据属于多个部门的情况，所以数据表与映射表应该是一对多的关系。

提供过滤sql的方法

代码如下

/**
 * 数据权限sql拼接*
 * @author chunhui.tan
 * @创建时间 2019年1月9日 下午4:30:09
 */@Componentpublic class DataSqlFilter {
    @Autowiredprivate TsysUserRoleService tsysUserRoleService;@Autowiredprivate TsysRoleDeptService tsysRoleDeptService;@Autowiredprivate TsysDeptService tsysDeptService;/**
     * 部门与数据表映射表的表名*/public final String MAPPING_TABLE = "DATA_MAPPING";/**
     * @param tableName 表名* @param pkNmae    主键名* @param isPrivate 是否只获取私有* @param subDept   是否拥有子部门数据权限* @return*/public String getDataSql(String tableName, String pkNmae,Boolean isPrivate, Boolean subDept) {// 校验参数checkParam(tableName, pkNmae, isPrivate, subDept);// 判断当前表是否开启数据权限if (!isNeedPermissions(tableName)) {return null;}// 获取当前用户TsysUserEntity user = ShiroUtils.getUserEntity();// 获取部门数据所需要的sqlString deptSql = getFilterSql(user, subDept);StringBuilder dataSql = new StringBuilder();dataSql.append("( SELECT DISTINCT S.* FROM ").append(tableName)
                .append(" S LEFT JOIN ").append(MAPPING_TABLE)
                .append(" T ON S.").append(pkNmae).append
                (" = T.T_ID AND T.TABLE_NAME= ").
                append("'").append(tableName)
                .append("'");
        if (isPrivate) {// 只获取私有数据dataSql.append(" WHERE (T.U_ID = ").append("'").
                    append(user.getEmId()).append("'")
                    .append(" AND (T.D_ID='' OR T.D_ID IS NULL))");} else {// 正常数据：私有数据+部门数据+公开数据dataSql.append(" WHERE T.D_ID IN ").append(deptSql).
                    append(" OR (T.U_ID = ").append("'")
                    .append(user.getEmId()).append("'").
                    append(" AND (T.D_ID='' OR T.D_ID IS NULL))")
                    .append(" OR ((T.D_ID='' OR T.D_ID IS NULL) " +"AND (T.U_ID='' OR T.U_ID IS NULL))");}
        dataSql.append(")");
        return dataSql.toString();}/**
     * 判断当前表是否开启数据权限*
     * @param tableName
     * @return*/private Boolean isNeedPermissions(String tableName) {// TODO 这里可以通过表名查询配置或者表来判断改表是否开启了数据权限return true;}/**
     * 获取部门数据情况下的过滤条件SQL
     *
     * @param user
     * @param subDept
     */private String getFilterSql(TsysUserEntity user, Boolean subDept) {// 部门ID列表Set deptIdList = new HashSet<>();// 用户角色对应的部门ID列表List roleIdList = tsysUserRoleService.
                queryRoleIdList(user.getEmId());
        if (roleIdList.size() > 0) {
            List userDeptIdList = tsysRoleDeptService
                    .queryDeptIdList(roleIdList.toArray(newString[roleIdList.size()]));deptIdList.addAll(userDeptIdList);}// 用户子部门ID列表if (subDept) {
            List subDeptIdList = tsysDeptService.
                    getSubDeptIdList(user.getEmDeptId());deptIdList.addAll(subDeptIdList);}
        List result = deptIdList.stream().map(i -> {return "'" + i + "'";}).collect(Collectors.toList());StringBuilder sqlFilter = new StringBuilder();sqlFilter.append("(").append(StringUtils.
                join(result, ",")).append(")");
        return sqlFilter.toString();}/**
     * 参数校验*
     * @param tableName
     * @param pkNmae
     * @param isPrivate
     * @param subDept
     */private void checkParam(String tableName, String pkNmae,Boolean isPrivate, Boolean subDept) {//TODO 进行sql注入的校验if (StringUtils.isBlank(tableName) ||
                StringUtils.isBlank(pkNmae) ||null == isPrivate || null == subDept) {throw new XcrmsException("数据权限-缺少参数");}
    }
}

测试

当前系统使用shiro做的权限，角色分成两种类型，一种是功能角色，一种是数据角色。功能角色与菜单按钮挂钩，数据角色与部门挂钩。接下来用PostMan接口测试方式来测试获取到的sql。

只获取私有数据

@Autowired(required = true)private DataSqlFilter dataSqlFilter;/**
 * test
 *
 * @param id
 * @return*/@GetMapping("/getDataSql")public Result getDataSql() {
        String dataSql = dataSqlFilter.
        getDataSql("PRODUCT", "P_ID", Boolean.TRUE, Boolean.FALSE);System.out.println(dataSql);
        return ResultUtil.success(dataSql);

}

结果用Navicat处理一下：

获取普通数据所谓普通数据就是用户正常能看见的数据：私有数据+部门数据+公开数据。将参数isPrivate设置为false即可

@Autowired(required = true)private DataSqlFilter dataSqlFilter;/**
 * test
 *
 * @param id
 * @return*/@GetMapping("/getDataSql")public Result getDataSql() {
        String dataSql = dataSqlFilter.getDataSql
        ("PRODUCT", "P_ID", Boolean.FALSE, Boolean.FALSE);System.out.println(dataSql);
        return ResultUtil.success(dataSql);}

结果用Navicat处理一下：

实际应用

查询

业务开发人员在查询是只需要调用这个方法获取到sql后，然后作为参数传入DAO层，在mybatis的xml文件中拼接即可(图片中的${dataSql}应为#{dataSql})，如下：

新增

新增业务数据时，业务需要知道这个数据时那种数据类型，然后新增数据后，需要新增一条映射记录。提供过滤sql的类中可以提供统一的新增映射数据的方法。还没写，大概如下：

/**
 * 新增映射数据*
 * @param pk_value  数据表数据主键值* @param tableName 数据表表名* @param type      数据类型 0 私有 1 公开 2 部门*/public void addMapping(String pk_value,String tableName, Integer type) {// TODO 可以通过shiro获取到当前登录的用户,
        // 然后获取到用户的部门，然后根据type来新增映射关系}

修改

若只是修改数据，则不关映射表的事。

删除

删除时需要注意，因为某条数据可能属于多个部门或者多个个人，那么当删除掉这条数据后，那么映射表中就存在多条T_ID相同和TABLE_NAME相同的数据，删除的时候应该通过T_ID和TABLE_NAME来删除映射表中的所有数据。

修改数据的私有，公开，部门属性

即修改映射表，按道理说，一般只会存在数据的所有人能修改，但是以防万一，业务开发人员需要判断当前数据的U_ID是否与当前用户的用户ID一致，不一致不能修改。当然，公开数据时不需要进行这一步校验的。以下修改方法都可以在DataSqlFilter类中统一提供

私有改为部门

即用户将私有数据分享给指定的部门

修改方式：修改映射表中的D_ID为用户指定的部门ID

私有改为公开

即用户将私有数据全部公开

修改方式：置空映射表中的U_ID和D_ID

部门改为公开

即将部门所拥有的数据公开

修改方式：置空映射表中的U_ID和D_ID

其他变更

其他变更只要对照上面那张数据类型表即可进行修改。

总结

有人可能会说为什么不在原来的业务表上面加上部门ID和用户ID，从而不用映射表？

但是这样会有一些问题，一是实际开发过程中，往往由于需求的变化，很难确定哪些表需要加部门ID和用户ID。二是这样无法满足一些特殊需求，比如：个人数据分享出去给其他人或者部门，单条数据属于多个部门。

总结下来用以上方案来控制数据权限的优缺点如下

优点：

1.统一提供过滤sql，维护映射表，修改数据类型的方法，而且不用修改业务数据表，对原来的代码入侵最小化。

2.不论是单表查询还是多表都能支持。

3.比较灵活，针对不同需求，可以灵活调整过滤sql

缺点：

1.因为在对原表进行过滤时需要连接映射表，假如是多表联查，每张表都要连接映射表，可能对查询性能有影响。

2.因为每条业务数据都会对应一条映射数据，那么意味着映射表将会有很多数据，在过滤的时候会影响性能，当然这里可以用分模块设计多个映射表的方法来解决。

以上只是一个设计思路，还没用于实际项目，如有不足，欢迎斧正，谢谢。

————————————————

原文链接：https://blog.csdn.net/qq_38846242/article/details/86529896

Ms 陈

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql横向拼接时会缺少行_数据权限设计研究行数据权限

关于权限设计一般来说，权限模块对于每一个系统而言都是最基础的模块，根据项目需求和功能的不同，设计方案也有许多。但从大的方面来说，可以将权限分为两大类型：功能权限和数据权限功能权限主要控制不同的资源主体(用户、角色、组织等)有操作不同的资源的权限。比如常见的不同的角色能访问不同的页面(菜单权限)，以及具有操作同一页面的不同功能(按钮权限)等等。对于java开发而言，功能权限的开发相对来说要...
复制链接

扫一扫

sql横向拼接时会缺少行_数据权限设计研究行数据权限

提供过滤sql的方法

测试

实际应用

查询

新增

“相关推荐”对你有帮助么？