One, foreword
During this period, I have been busy all the time. I don’t know what I am doing every day, but I feel more and more lazy. Every day when I come back from work, I don’t want to do anything.
Just got home at 9:00 p.m., I sat on the couch and thought what the hell am I going to do? Why haven’t I been blogging? Is my life all about work?
After more than ten minutes of consideration, I still obediently open the computer, have a look at Ruoyi’s code, learn about data permissions related content, the following record, my learning process and related ideas.
Second, basic principles
For data permissions, if you do not look at the implementation of some projects, it may not be easy to think about, let’s first talk about what is the number of permissions:
Data access
Different response information based on the user’s position, role, etc. (here is my personal understanding)
Here’s an example:
Requirement: Access to my blog posts (my user ID is 101)
select
* from blog b
left join user_blog ub on ub.blog_id = b.id
where ub.user_id = 101
Copy the codeFor some system, we should only be allowed to access their authorized content, such as: ordinary employees can only access to their data information, the department manager can access the information of the whole department, finance department head can access financial information, we do not want to ordinary employees can get directly to the financial information, that is to say, professional people do professional thing
Basic principles of implementation
So that’s the basic concept
Now, we need to think about, how exactly do we need to do this? For the basic RBAC model, we can divide it into User, Role, Menu, User_Role, Role_Menu
The user is bound to a role, and the role is bound to the permission menu. In this way, you can determine whether the user has access permission to a certain function based on the role
But, what is mentioned above is whether a user can access a certain resource. What we mean here is that users can access resource information, But they need to respond to different information according to different people, rather than not being able to access
Such as:
The first option is for ordinary users to access administrator data. This is not allowed
The second type: ordinary users access the personnel information of the whole department, this can be accessed, but the server will respond to the information of an ordinary user, but not all the data response
So let’s get to the point:
We need to create four tables: User, Role, Dept, User_Role, Role_Dept. The details are as follows:
The data_scope in the Role table specifies the extent to which different roles have access to data. The data_scope in the Role table specifies the extent to which different roles can access data.
/* All data permissions */
select u.*,d.*
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
where u.del_flag = '0'
/* Custom permissions are based on the relationship between sys_ROLE_DEPT and sys_ROLE_DEPT */
select u.*,d.*
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
where u.del_flag = '0'
//The most important place to lookor d.dept_id IN (
SELECT
dept_id
FROM sys_role_dept
WHERE role_id = 1 )
/* Department data permissions */
select u.*,d.*
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
where u.del_flag = '0'
//The most important place to lookor d.dept_id = 1
Copy the codeSQL = select * from ‘where’ where ‘where’ where ‘where’ where ‘where’ where ‘
** The first type: ** all permissions
** (u.del_flag = ‘0’) **, that is, we can get all the data except the data that has been deleted
** The second type: ** custom permissions
If the role and dept have custom permissions, you can access the data of the Financial and accounting department. The following conditions are added:
or d.dept_id IN (
SELECT
dept_id
FROM sys_role_dept
WHERE role_id = 1 )
Copy the codeThe third mode is to obtain the data permission of the department
In this case, only the data of the department belongs to can be obtained, and it does not need other association relations. As we can see in the figure, dept_id already exists in the User table, so we only need to use the user.getDeptid () method to obtain it when querying
or d.dept_id = 1
Copy the codeWith all that said, the last thing we need to think about is: How should the code be implemented?
We can’t “write dead” the where statement, it should be a variable, so we need to concatenate the SQL condition, to complete
-
Determine the role associated with the user and obtain the data_scope of the role
If data_scope = 1 then query all non-deleted data without concatenation
If data_scope = 2 then concatenate and get the custom data
or d.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = 1 ) Copy the code
3, if data_scope then splicing, get the department data
or d.dept_id = 1
Copy the codeWrite code
(1) Database
User
create table sys_user (
user_id bigint(20) not null auto_increment comment 'user ID',
dept_id bigint(20) default null comment 'department ID',
login_name varchar(30) not null comment 'Login account',
user_name varchar(30) default ' ' comment 'User nickname',
user_type varchar(2) default '00' comment 'User type (00 System user 01 Registered user)',
email varchar(50) default ' ' comment 'User Mailbox',
phonenumber varchar(11) default ' ' comment 'Mobile phone number',
sex char(1) default '0' comment 'User gender (0 male, 1 female, 2 unknown)',
avatar varchar(100) default ' ' comment 'Head path',
password varchar(50) default ' ' comment 'password',
salt varchar(20) default ' ' comment 'Salt encryption',
status char(1) default '0' comment 'Account status (0 normal 1 disabled)',
del_flag char(1) default '0' comment 'Delete flag (0 means exist and 2 means delete)',
login_ip varchar(50) default ' ' comment 'Last login IP',
login_date datetime comment 'Last Landing time',
create_by varchar(64) default ' ' comment 'Creator',
create_time datetime comment 'Creation time',
update_by varchar(64) default ' ' comment 'Updater',
update_time datetime comment 'Update time',
remark varchar(500) default null comment 'note'.primary key (user_id)
) engine=innodb auto_increment=100 comment = 'User information Table';
insert into sys_user values(1.100.'admin'.'Gofly'.'00'.'[email protected]'.'15888888888'.'1'.' '.'29c67a30398638269fe600f73a054934'.'111111'.'0'.'0'.'127.0.0.1'.'the 2018-03-16-33-11 00'.'admin'.'the 2018-03-16-33-11 00'.'ry'.'the 2018-03-16-33-11 00'.'Administrator');
insert into sys_user values(2.101.'kjb'.'kjb'.'00'.'[email protected]'.'15888888888'.'1'.' '.'29c67a30398638269fe600f73a054934'.'111111'.'0'.'0'.'127.0.0.1'.'the 2018-03-16-33-11 00'.'admin'.'the 2018-03-16-33-11 00'.'ry'.'the 2018-03-16-33-11 00'.'Department Head');
Copy the codeRole
create table sys_role (
role_id bigint(20) not null auto_increment comment 'character ID',
role_name varchar(30) not null comment 'Role name',
role_key varchar(100) not null comment 'Role permission string',
role_sort int(4) not null comment 'Display order',
data_scope char(1) default '1' comment Data scope (1: all data permission 2: customized data permission 3: department data permission 4: Department data permission and the following data permission),
status char(1) not null comment 'Role status (0 normal 1 disabled)',
del_flag char(1) default '0' comment 'Delete flag (0 means exist and 2 means delete)',
create_by varchar(64) default ' ' comment 'Creator',
create_time datetime comment 'Creation time',
update_by varchar(64) default ' ' comment 'Updater',
update_time datetime comment 'Update time',
remark varchar(500) default null comment 'note'.primary key (role_id)
) engine=innodb auto_increment=100 comment = 'Role Information Table';
insert into sys_role values('1'.'Super Administrator'.'admin'.1.1.'0'.'0'.'admin'.'the 2018-03-16-33-11 00'.'ry'.'the 2018-03-16-33-11 00'.'Super Administrator');
insert into sys_role values('2'.'Common Character'.'common'.2.3.'0'.'0'.'admin'.'the 2018-03-16-33-11 00'.'ry'.'the 2018-03-16-33-11 00'.'Common Character');
Copy the codeDept
create table sys_dept (
dept_id bigint(20) not null auto_increment comment 'department id',
parent_id bigint(20) default 0 comment 'Parent department ID',
ancestors varchar(50) default ' ' comment 'Ancestor level list',
dept_name varchar(30) default ' ' comment 'Department Name',
order_num int(4) default 0 comment 'Display order',
leader varchar(20) default null comment 'In charge',
phone varchar(11) default null comment 'Contact Number',
email varchar(50) default null comment 'email',
status char(1) default '0' comment 'Department status (0 normal 1 disabled)',
del_flag char(1) default '0' comment 'Delete flag (0 means exist and 2 means delete)',
create_by varchar(64) default ' ' comment 'Creator',
create_time datetime comment 'Creation time',
update_by varchar(64) default ' ' comment 'Updater',
update_time datetime comment 'Update time'.primary key (dept_id)
) engine=innodb auto_increment=200 comment = 'Table of Departments';
insert into sys_dept values(100.0.'0'.'Gofly technology'.0.'Gofly'.'15888888888'.'[email protected]'.'0'.'0'.'admin'.'the 2018-03-16-33-11 00'.'gofly'.'the 2018-03-16-33-11 00');
insert into sys_dept values(101.100.'0'.'Science and Technology Department'.0.'Gofly'.'15888888888'.'[email protected]'.'0'.'0'.'admin'.'the 2018-03-16-33-11 00'.'gofly'.'the 2018-03-16-33-11 00');
Copy the codeuser_role
create table sys_user_role (
user_id bigint(20) not null comment 'user ID',
role_id bigint(20) not null comment 'character ID'.primary key(user_id, role_id)
) engine=innodb comment = 'User and Role Association Table';
insert into sys_user_role values ('1'.'1');
insert into sys_user_role values ('2'.'2');
Copy the coderole_dept
create table sys_role_dept (
role_id bigint(20) not null comment 'character ID',
dept_id bigint(20) not null comment 'department ID'.primary key(role_id, dept_id)
) engine=innodb comment = 'Role and Department association Table';
insert into sys_role_dept values ('1'.'100');
insert into sys_role_dept values ('2'.'101');
Copy the code(2) Entities
As we mentioned above, our SQL statements cannot be “writable”, and we need to set different conditions for different user roles, so we need to define a BaseEntity for storing SQL conditions. Other entity classes only need to inherit from this BaseEntity
public class BaseEntity implements Serializable {
private Map<String,Object> params;
public Map<String, Object> getParams(a) {
if(params == null){
params = new HashMap<>();
}
return params;
}
public void setParams(Map<String, Object> params) {
this.params = params; }}Copy the codeFor example, we get departmental data
getParams().put("dataScope"." or d.dept_id = 1")
Copy the codeIn mybatis XML
${params.dataScope}
select u.*,d.*
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
where u.del_flag = '0'
//${params.datascope}Copy the codeUser
@Data
public class SysUser extends BaseEntity implements Serializable {
/** * User ID */
private Long userId;
/** * Department ID */
private Long deptId;
/** * Login account */
private String loginName;
/** * User nickname */
private String userName;
/** * User type (00 system user 01 Registered user) */
private String userType;
/** * User email */
private String email;
/** * Mobile phone number */
private String phonenumber;
/** * User gender (0 male, 1 female, 2 unknown) */
private String sex;
/** * Head path */
private String avatar;
/** * Password */
private String password;
/** * Salt encryption */
private String salt;
/** * Account status (0 normal 1 Disabled) */
private String status;
/** * Delete flag (0 indicates existence and 2 indicates deletion) */
private String delFlag;
/** * Last login IP */
private String loginIp;
/** * Last login time */
private Date loginDate;
/** * Creator */
private String createBy;
/** * Creation time */
private Date createTime;
/** * Updater */
private String updateBy;
/** * Update time */
private Date updateTime;
/** * Remarks */
private String remark;
private static final long serialVersionUID = 1L;
}
Copy the codeDept
@Data
public class SysDept implements Serializable {
/** * Department ID */
private Long deptId;
/** * Id of parent department */
private Long parentId;
/** * Ancestor list */
private String ancestors;
/** * Department name */
private String deptName;
/** * Display order */
private Integer orderNum;
/** * Responsible person */
private String leader;
/**
* 联系电话
*/
private String phone;
/** * Email */
private String email;
/** * Department status (0 normal 1 Disabled) */
private String status;
/** * Delete flag (0 indicates existence and 2 indicates deletion) */
private String delFlag;
/** * Creator */
private String createBy;
/** * Creation time */
private Date createTime;
/** * Updater */
private String updateBy;
/** * Update time */
private Date updateTime;
private static final long serialVersionUID = 1L;
}
Copy the codeRole
@Data
public class SysRole implements Serializable {
/** * Role ID */
private Long roleId;
/** * Role name */
private String roleName;
/** * Role permission string */
private String roleKey;
/** * Display order */
private Integer roleSort;
/** * Data scope (1: all data permission 2: customized data permission 3: data permission of the department 4: data permission of the department and the following) */
private String dataScope;
/** * Role status (0 normal 1 Disabled) */
private String status;
/** * Delete flag (0 indicates existence and 2 indicates deletion) */
private String delFlag;
/** * Creator */
private String createBy;
/** * Creation time */
private Date createTime;
/** * Updater */
private String updateBy;
/** * Update time */
private Date updateTime;
/** * Remarks */
private String remark;
private static final long serialVersionUID = 1L;
}
Copy the codeDept_Role
@Data
public class SysRoleDeptKey implements Serializable {
/** * Role ID */
private Long roleId;
/** * Department ID */
private Long deptId;
private static final long serialVersionUID = 1L;
}
Copy the codeRole_User
@Data
public class SysUserRoleKey implements Serializable {
/** * User ID */
private Long userId;
/** * Role ID */
private Long roleId;
private static final long serialVersionUID = 1L;
}
Copy the codeAfter looking at this entity, you might be wondering why only SysUser inherits BaseEntity, since we’re just testing to get a list of users, so only he inherits BaseEntity
(3) Annotations
When we set data permissions, we need custom annotations, and when you think about annotations, you definitely think about AOP, so let’s customize annotations, and then use AOP for related operations
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope {
/** * Department alias *@return* /
public String deptAlias(a) default "";
/** * User alias *@return* /
public String userAlias(a) default "";
}
Copy the code(4) the AOP
This is the hero of the evening, and the focus of all the operations, because it will implement the SQL concatenation work
@Aspect
@Component
public class DataScopeAspectJ {
@Autowired
private IUserService iUserService;
@Autowired
private IRoleService iRoleService;
/** * All data permissions */
public static final String DATA_SCOPE_ALL = "1";
/** * Customize data permissions */
public static final String DATA_SCOPE_CUSTOM = "2";
/** * Department data permissions */
public static final String DATA_SCOPE_DEPT = "3";
/** * Department and the following data authority */
public static final String DATA_SCOPE_DEPT_AND_CHILD = "4";
/** * Only my data permission */
public static final String DATA_SCOPE_SELF = "5";
/** * Data permission filtering keyword */
public static final String DATA_SCOPE = "dataScope";
@Pointcut("@annotation(com.yangzinan.datascopeproject.annotation.DataScope)")
public void dataScopePointCut(a){}@Before("dataScopePointCut()")
public void doBefore(JoinPoint joinPoint){
doHandler(joinPoint);
}
public void doHandler(JoinPoint joinPoint){
/** * Get comments */
DataScope dataScope = getAnnotationLog(joinPoint);
if(dataScope == null) {return ;
}
// We simulate getting user information from Shiro
SysUser sysUser = iUserService.selectUserById(1L);
if(sysUser ! =null) {// Data permission processingdataScopeHandler(sysUser,joinPoint,dataScope.deptAlias(),dataScope.userAlias()); }}/** * Data permission processing *@param user
* @param joinPoint
* @param deptAlias
* @param userAlias
*/
public void dataScopeHandler(SysUser user,JoinPoint joinPoint,String deptAlias,String userAlias){
// Used to concatenate strings
StringBuilder stringBuilder = new StringBuilder();
// Obtain all roles of the user based on the user ID
List<SysRole> sysRoles = iRoleService.selectRolesByUserId(user.getUserId());
for(SysRole role : sysRoles){
//DATA_SCOPE_ALL = 1
if(role.getDataScope().equals(DATA_SCOPE_ALL)){
stringBuilder = new StringBuilder();
break;
}
//DATA_SCOPE_ALL = 2
else if(role.getDataScope().equals(DATA_SCOPE_CUSTOM)){
stringBuilder.append(String.format(
" OR %s.dept_id IN ( SELECT dept_id FROM sys_role_dept WHERE role_id = %s ) ", deptAlias,
role.getRoleId()));
}
//DATA_SCOPE_ALL = 3 Obtain department data
else if(role.getDataScope().equals(DATA_SCOPE_DEPT)){
stringBuilder.append(String.format(
" OR %s.dept_id = %s ", deptAlias, user.getDeptId()));
}
//DATA_SCOPE_ALL = 4
else if(role.getDataScope().equals(DATA_SCOPE_DEPT_AND_CHILD)){
stringBuilder.append(String.format(
" OR %s.dept_id IN ( SELECT dept_id FROM sys_dept WHERE dept_id = %s or find_in_set( %s , ancestors ) )", deptAlias, user.getDeptId(), user.getDeptId())); }}/** * If StringBuilder is not empty, there is a concatenated SQL above, put it in the Map of BaseEntity */
if (StringUtils.isNotBlank(stringBuilder.toString()))
{
Object params = joinPoint.getArgs()[0];
if(! org.springframework.util.StringUtils.isEmpty(params) && paramsinstanceof BaseEntity)
{
BaseEntity baseEntity = (BaseEntity) params;
baseEntity.getParams().put(DATA_SCOPE, " AND (" + stringBuilder.substring(4) + ")"); }}}/** * Get comments *@param joinPoint
* @return* /
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; }}Copy the code(5) Mybatis XML
<select id="selectUsers" parameterType="com.yangzinan.datascopeproject.entity.SysUser" resultMap="BaseResultMap">
select u.*,d.*
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
where u.del_flag = '0'
<! -- Data permissions -->
${params.dataScope}
</select>
Copy the code(6) Mapper
@Mapper
public interface SysUserMapper {
int deleteByPrimaryKey(Long userId);
int insert(SysUser record);
int insertSelective(SysUser record);
SysUser selectByPrimaryKey(Long userId);
int updateByPrimaryKeySelective(SysUser record);
int updateByPrimaryKey(SysUser record);
List<SysUser> selectUserList(a);
/ * * /
List<SysUser> selectUsers(SysUser sysUser);
}
Copy the code(7) Service
It’s important to note that we’re adding custom annotations here
@Service
public class UserServiceImpl implements IUserService{
@Autowired
private SysUserMapper sysUserMapper;
@Override
public List<SysUser> selectUserList(a) {
return sysUserMapper.selectUserList();
}
@Override
public SysUser selectUserById(Long userId) {
return sysUserMapper.selectByPrimaryKey(userId);
}
/ * * /
@DataScope(deptAlias = "d", userAlias = "u")
@Override
public List<SysUser> selectUsers(SysUser sysUser) {
returnsysUserMapper.selectUsers(sysUser); }}Copy the codeThe Controller (8)
@RestController
public class UserController {
@Autowired
private IUserService userService;
@GetMapping("list")
public List<SysUser> getUserList(a){
SysUser sysUser = new SysUser();
returnuserService.selectUsers(sysUser); }}Copy the code(9) Testing
First, the administrator obtains the data
We mainly modify the following aspects of AOP:
- If the user ID is 1L, the administrator has the data permission to obtain information about all users
- If the user ID is 2L: common user, the data permission is to obtain department user information
// Get user information from Shiro (administrator)
SysUser sysUser = iUserService.selectUserById(1L);
Copy the code[{
"params": {},
"userId": 1."deptId": 100."loginName": "admin"."userName": "Gofly"."userType": "00"."email": "[email protected]"."phonenumber": "15888888888"."sex": "1"."avatar": ""."password": "29c67a30398638269fe600f73a054934"."salt": "111111"."status": "0"."delFlag": "0"."loginIp": "127.0.0.1"."loginDate": "The 2018-03-16 T11:33:00. 000 + 00:00"."createBy": "admin"."createTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."updateBy": "ry"."updateTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."remark": "Administrator"
}, {
"params": {},
"userId": 2."deptId": 101."loginName": "kjb"."userName": "kjb"."userType": "00"."email": "[email protected]"."phonenumber": "15888888888"."sex": "1"."avatar": ""."password": "29c67a30398638269fe600f73a054934"."salt": "111111"."status": "0"."delFlag": "0"."loginIp": "127.0.0.1"."loginDate": "The 2018-03-16 T11:33:00. 000 + 00:00"."createBy": "admin"."createTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."updateBy": "ry"."updateTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."remark": "Department head"
}]
Copy the codeSecond: the common user gets the data
Modify the following:
// Get user information from Shiro (administrator)
SysUser sysUser = iUserService.selectUserById(1L);
Copy the code[{
"params": {},
"userId": 2."deptId": 101."loginName": "kjb"."userName": "kjb"."userType": "00"."email": "[email protected]"."phonenumber": "15888888888"."sex": "1"."avatar": ""."password": "29c67a30398638269fe600f73a054934"."salt": "111111"."status": "0"."delFlag": "0"."loginIp": "127.0.0.1"."loginDate": "The 2018-03-16 T11:33:00. 000 + 00:00"."createBy": "admin"."createTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."updateBy": "ry"."updateTime": "The 2018-03-16 T11:33:00. 000 + 00:00"."remark": "Department head"
}]
Copy the codeThat’s all for today. Wait for tomorrow to get up, I will be good to revise and improve the content, if the wrong place, please forgive me!
Good night 🌛