Data Permissions – Field Permissions [Practice – Explain in detail how to implement it based on relevant business] (based on the Ruoyi framework)

Look at this theory
https://blog.csdn.net/weixin_41842550/article/details/119890216

Write the directory title here

- Implement data permissions according to department structure and user data
- - 1. Required basic data
  - - 1 System Management–Department Management–Add the following structure
    - 2 System Management–Role Management–Add two roles
    - 3 System Management–User Management–Add 7 users
  - 2. Screenshots and code implementation are as follows
  - - 1 Create a table
    - 2 System BaseEntity
    - 3 entity files
    - 4 xml files
    - 5 mapper files
    - 6 service and impl files
    - 7 controller file
  - 3. Verify data permissions
  - - 1 The basic data of the department are as follows
    - 2 basic data of ask table
    - 3 Data without data permission
    - 4 Zhang San’s data
    - 5 Data of department A manager
    - 6 Department A-Employee 1 data
- Extension-field permission ideas
- - - 1 Field information table
    - 2. Link to the role
    - 3 Dynamic splicing

Implement data permissions according to department structure and user data

Ordinary employees can only see their own leave data, while leaders can see their own and all employees in their department’s leave data.

Here we take leave as an example. In fact, any business data that meets the department’s data permissions can be implemented according to this case
This article uses the computer’s local database mysql, starts java locally, and starts vue locally. Use token to distinguish whether data permissions have been reached, and call interface testing through postman

I have modified the port context of the project

1 Required basic data

1 System Management – Department Management – Add the following structure

Here go to the database sys_dept table and take a look at dept_id. We will use it later when we fabricate data.
Ancestors is the ancestor dept_id separated by multiple commas. With this field, there is no need to search recursively, and the efficiency is greatly improved

2 System Management – Role Management – Add Two Roles

4: Data permissions for this department and below; 5: Only personal data permissions; [Both of these are data permissions reserved by the system]

Data permissions use the data_scope field
The data_scope field does not support page modification, you need to manually modify the database yourself
The role key is the menu button permission, and it is not recommended to mix it with the data permission. Write whatever you want here

3 System Management – User Management – Add 7 users

Zhang San – R&D Department – Director: You can see the data of everyone in the department and below
Manager of Department A: Can see the data of everyone in Department A and below, but cannot see the data of Director-Zhang San (select Department A for the department; select the data permissions of this department and below for the role)
Department A employee 1: Select department A; the role only has personal data permissions
Department A employee 2: Select department A; the role only has personal data permissions
Department B manager: Can see the data of everyone in department B and below, but cannot see the data of director Zhang San (select department B for department; select this department and the following data permissions for role)
Department B employee 1: Select department B; the role only has personal data permissions
Department B employee 2: Select department B; the role only has personal data permissions

When adding, select the corresponding department and role
The new password setting here is simpler. You will need to log in and get a token to verify after a while. The screenshot is modified.

2 Screenshots and code implementation are as follows

For convenience of demonstration, I put it in SysConfigController and a series of service and mapper files.

1 Create table

CREATE TABLE `ask` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL COMMENT 'leave title',
  `dept_id` int(11) DEFAULT NULL COMMENT 'department id',
  `user_id` int(11) DEFAULT NULL COMMENT 'userid',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

The same applies to other businesses. Just add dept_id and user_id to realize personal data permissions and department data permissions

2 System BaseEntity

3 entity file

Here you need to inherit BaseEntity. The sql fragment of data permission is in the params attribute in BaseEntity.

package com.ruoyi.system.domain;

import com.ruoyi.common.core.domain.BaseEntity;
import lombok.Data;

@Data
public class AskEntity extends BaseEntity {<!-- -->
    private Long id;
    private String title;
    private Long deptId;
    private Long userId;
}

4 xml file

 <select id="selectAskList" parameterType="com.ruoyi.system.domain.AskEntity" resultType="com.ruoyi.system.domain.AskEntity">
        select * from ask where 1=1 ${<!-- -->params.dataScope}
    </select>

5 mapper file

The package path that needs to be imported

import com.ruoyi.system.domain.AskEntity;

 List<AskEntity> selectAskList(AskEntity askEntity);

6 service and impl files

 List<AskEntity> selectAskList(AskEntity askEntity);

 @Override
    public List<AskEntity> selectAskList(AskEntity askEntity) {<!-- -->
        return configMapper.selectAskList(askEntity);
    }

7 controller file

 @GetMapping("/selectAskList")
    public TableDataInfo list(AskEntity askEntity)
    {<!-- -->
        startPage();
        List<AskEntity> list = configService.selectAskList(askEntity);
        return getDataTable(list);
    }

3 Verify data permissions

Add the corresponding data. I will write the dept_id and user_id directly in the database. Theoretically, it is necessary to set the dept_id when adding certain business data.

import com.ruoyi.common.utils.SecurityUtils;

The code obtains the deptId, provided that the user list has been configured with departments.
Long deptId = SecurityUtils.getLoginUser().getDeptId();
Long userId = SecurityUtils.getLoginUser().getUserId();

1 The basic data of the department is as follows

dept_id dept_name
103 R&D Department
110 Department A
111 Department B

2 Ask table basic data

INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (1, 'R&D Department-Director-Zhang San asks for leave', 103, 3) ;
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (2, 'Department A-Manager asks for leave', 110, 4);
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (3, 'Department B-Manager asks for leave', 111, 8);
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (4, 'Department A-Employee 1 asked for leave', 110, 5);
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (5, 'Department A-Employee 2 asked for leave', 110, 6);
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (6, 'Department B-Employee 1 asked for leave', 111, 10);
INSERT INTO `ruoyi`.`ask` (`id`, `title`, `dept_id`, `user_id`) VALUES (7, 'Department B-Employee 2 asks for leave', 111, 9);

3 Data without data permission

Anyone can find an interface. The Authorization in the request header is the token.

Postman transfer interface, copy the above token
There are 7 pieces of data without adding data permissions

4 Zhang San’s data

Here is a slight change to the previous code. The alias ask is added to the table in xml.

 <select id="selectAskList" parameterType="com.ruoyi.system.domain.AskEntity"
            resultType="com.ruoyi.system.domain.AskEntity">
        select * from ask as ask
        where 1=1
            ${params.dataScope}
    </select>

Annotations for data permissions have been added to ServiceImpl.

@DataScope(deptAlias = "ask", userAlias = "ask")

Query data using Zhang San’s token

The printed sql is as follows

SELECT
*
FROM
ask AS ask
WHERE
1 = 1
AND (
\t\t
ask.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 103 OR find_in_set( 103, ancestors ) )
\t\t
)

Mainly I checked the department table
dept_id = 103 [dept_id103 represents the id of the R&D department, which is its own data]
find_in_set( 103, ancestors ) [The ancestor dept_id has the 103 R&D department, that is, all sub-departments under the 103 department]The ancestor id here is very cleverly designed, and there is really no need to recurse You can achieve downward query of a tree

5 Data of department A manager

Same as above

The sql is as follows. In fact, the dept_id is different.

SELECT
*
FROM
ask AS ask
WHERE
1 = 1
AND (
ask.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = 110 OR find_in_set( 110, ancestors ) )
)

6 Data of Department A-Employee 1

Same as above

select * from ask as ask where 1=1 AND (ask.user_id = 5 )

This sql is relatively simple. I just checked a user_id for my own.

The same applies to the data of department B-employee 2. Just change the user_id.

Extension-field permission ideas

Based on the basis of data permissions, it is very simple to implement field permissions.

For example, managers can view 10 fields, and ordinary employees can view 5 fields.

1 Field information table

First, there must be a field information table to maintain the field information of the table that requires field permissions.

CREATE TABLE `sys_field` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(255) DEFAULT NULL COMMENT 'Table name in English',
  `table_remark` varchar(255) DEFAULT NULL COMMENT 'Table remark Chinese',
  `field_list` varchar(2000) DEFAULT NULL COMMENT 'field information',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

It is best to put an array in field_list, which contains multiple objects, and each object is a piece of field information. This information can be made into a page for adding, deleting, modifying and checking, or it can be maintained by developers themselves

[{<!-- -->"field":"title","remark":"leave title"},{<!-- -->"field\ ":"center","remark":"Leave content"},{<!-- -->"field":"f3","remark":" Field 3"},{<!-- -->"field":"f4","remark":"Field 4"}]

2 Hook up with the character

A new field field_str is added to the role table to store the fields that need to be queried. It is best to use a set of field string information for a role.

When adding a new role, you can add a multi-select box or a multi-select drop-down box for selecting fields. The specific value is the value of the field field of multiple objects in the field_list just now. After selection, the field_str stored in the character table is separated by multiple commas.

3 Dynamic splicing

Or use AOP to dynamically assemble fields and store them in the map?
Determine which queries require dynamic fields through annotations
Just take it out of the mapper.xml file.