Previous articles in this issue have taught you a lot about the use of Spring Security and the RBAC role-based permission control model. However, although many friends have understood the RBAC control model, there are still many problems hindering their further development. Such as:

  • How should the table structure of the RBAC model be created?
  • Specific to a page, a button permission is how to control?
  • What core fields should be included in the user table to work with the login authentication table?
  • How do these fields relate to requirements for login authentication or permission assignment?

So this article hopes to share these problems with you.

Review the RBAC authority model

  • The relationship between users and roles is many-to-many. A user has multiple roles, and a role contains multiple users
  • Roles and permissions are many-to-many. A role has multiple permissions, and one permission can belong to multiple roles

Above:

  • User is a User table that stores basic User information
  • Role is a Role table that stores information about roles
  • Menu is a permission list. Menus and their properties are included in the storage system
  • A UserRole is a table of relationships between users and roles
  • RoleMenu is a relational table of roles and permissions

This article explains how to control access only to the menu level, that is, to control access to pages. If you want to control access to the button level of a page, refer to the same implementation of Menu and RoleMenu patterns. Or add a field to the menu table to distinguish whether the entry is a menu item or a button.

For the sake of justification, we refer to an excellent open source project: Ruoyi Background Management system.

Two, organization department management

2.1. Demand analysis

The reason for starting with departmental management is that departmental management does not make withdrawals in our RBAC authority model above. But the department as an entity is still an important part of the back-end management system. There are usually the following requirements:

  • The department should represent a hierarchy of supervisors and subordinates (red box above). In a relational database. This requires the use of department ids and parent department ids to form a tree structure. This knowledge is necessary for SQL learning, if you do not know, please learn by yourself.
  • If there is a one-to-many relationship between an organization and a user, an org_id is added to the user table to identify the organization to which the user belongs. The principle is: the physical relationship is maintained on the side of the many. For example: is it easier for teachers to remember their students, or for students to remember their teachers?
  • This is also possible if the organization has a many-to-many relationship with its users. For example, someone is the director of production and the director of technology in a certain unit. So he belongs to the technical department. It also belongs to the production department. There are two solutions to this situation: put the person at the company level, not the department level. The other is to create a many-to-many relationship between the User and Org organizations from the database structure.
  • Organization information includes basic information, such as organization name, organization status, display order, and creation time
  • In addition, to have the basic organization of the increase, deletion, change and check function

2.2 CreateSQL for the Organization department table

The following SQL uses MySQL as an example:

CREATE TABLE 'sys_org' (' id 'INT(11) NOT NULL AUTO_INCREMENT,' org_pid 'INT(11) NOT NULL COMMENT' unsigned ', 'org_pids' VARCHAR(64) NOT NULL COMMENT' id of parent node ', 'is_leaf' TINYINT(4) NOT NULL COMMENT '0: 'org_name' VARCHAR(32) NOT NULL COMMENT 'iD ',' address 'VARCHAR(64) NULL DEFAULT NULL COMMENT' iD ', 'phone' VARCHAR(13) NULL DEFAULT NULL COMMENT 'phone ',' email 'VARCHAR(32) NULL DEFAULT NULL COMMENT' email ', 'sort' TINYINT(4) NULL DEFAULT NULL COMMENT '解 决 ',' level 'TINYINT(4) NOT NULL COMMENT' 解 决 ', 'status' TINYINT(4) NOT NULL COMMENT '0: enabled,1: disabled ', PRIMARY KEY (' id ')) COMMENT=' InnoDB 'COLLATE='utf8_general_ci' ENGINE=InnoDB;Copy the code

Note: mysql does not have the start with Connect by tree data summary SQL in Oracle. Therefore, special fields need to be added to facilitate the management of the upper-layer tree relationship between organizations. For example, orgPIDS: the ids of all upper-layer organizations in the organization are separated by commas (,), that is, the ids of the upper-layer organizations are included. Isleaf is a leaf node; Level level of the organization (1,2,3).

3. Menu permission management

3.1 Requirement Analysis

  • As you can see from the figure above, the menu is still a tree structure, so the database table must have the ID and MENU_PID fields
  • Required fields: URL of menu jump, enable or not, menu sort, menu icon vector icon, etc
  • The most important thing is that the menu should have a permission flag and be unique. You can usually use the url path that the menu jumps to as a permission marker. This flag is an important symbol for the permission management framework to identify whether a user has the permission to view a page
  • Need to have the basic function of menu add, delete, change and check
  • If you want to put menu permissions and button hyperlink permissions in the same table, you can add a field. The user indicates whether the permission record is menu access or button access.

3.2 CreateSQL for menu permission tables

CREATE TABLE 'sys_menu' (' id 'INT(11) NOT NULL AUTO_INCREMENT,' menu_pid 'INT(11) NOT NULL COMMENT' parent menu ', 'menu_pids' VARCHAR(64) NOT NULL COMMENT' current menu ', 'is_leaf' TINYINT(4) NOT NULL COMMENT '0: VARCHAR(64) NOT NULL COMMENT '表 名 ',' url 'VARCHAR(64) NOT NULL COMMENT' 表 名 ', `icon` VARCHAR(45) NULL DEFAULT NULL, `icon_color` VARCHAR(16) NULL DEFAULT NULL, 'sort' TINYINT(4) NULL DEFAULT NULL COMMENT 'sort ',' level 'TINYINT(4) NOT NULL COMMENT' menu ', 'status' TINYINT(4) NOT NULL COMMENT '0: enabled,1: disabled ', PRIMARY KEY (' id ') COMMENT=' system menu table 'COLLATE='utf8_general_ci' ENGINE=InnoDB;Copy the code

Role management

The figure above shows the page for modifying roles and assigning permissions

4.1. Demand analysis

  • The management of the role itself needs to pay attention to very few points, is simple add, delete, change and check. It’s all about how roles are assigned.
  • The role table contains basic information about the role ID, role name, remarks, and sort order
  • Assign rights to roles: Select menu rights or operation rights based on roles and delete the SYSroleAll records of the role in the menu table, insert the newly selected permission data into sys one by oneroleThe menu list.
  • sysroleMenu structure is very simple, record roleId with the menuId, a role with a specific permission is a record.
  • A role should have a globally unique identity because a role is itself a privilege. You can determine whether an operation performed by a user is valid by judging roles.
  • Common requirements: Assign roles to users on the user management page instead of adding users to roles.

4.2. CreateSQL of the role table and the role menu permission association table

CREATE TABLE 'sys_role' (' id 'INT(11) NOT NULL AUTO_INCREMENT,' role_id 'VARCHAR(16) NOT NULL COMMENT' id ', 'role_name' VARCHAR(16) NOT NULL COMMENT 'role id ',' ROLE_flag 'VARCHAR(64) NULL DEFAULT NULL COMMENT' role ID ', 'sort' INT(11) NULL DEFAULT NULL COMMENT 'sort ', PRIMARY KEY (' id ')) COMMENT=' InnoDB 'COLLATE='utf8_general_ci' ENGINE=InnoDB;Copy the code

CREATE TABLE 'sys_ROLE_MENU' (' id 'INT(11) NOT NULL AUTO_INCREMENT,' role_id 'VARCHAR(16) NOT NULL COMMENT' id ', 'menu_id' INT(11) NOT NULL COMMENT 'menu ID', PRIMARY KEY (' id ')) COMMENT=' InnoDB 'COLLATE='utf8_general_ci' ENGINE=InnoDB;Copy the code

5. User management

5.1. Demand analysis

  • In the figure above, click the left organization menu tree node to show all the people under the organization (system users). In the one-to-many relationship between organizations and users, you need to add the org_id field to the user table to query all users in an organization.
  • The user table contains the user name and encrypted password of the user. The password can be changed or reset.
  • Role assignment: Assigning roles to users is the same as assigning rights to roles. So you can refer to.
  • Realize the function of adding, deleting, modifying and checking the basic information of users

5.2. Sys_user CreateSQL of the user information table and user role relationship table

CREATE TABLE `sys_user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `org_id` INT(11) NOT NULL, 'username' VARCHAR(64) NULL DEFAULT NULL COMMENT 'username ',' password 'VARCHAR(64) NULL DEFAULT NULL COMMENT' password ', 'enabled' INT(11) NULL DEFAULT '1' COMMENT 'Whether user accounts are available ',' locked 'INT(11) NULL DEFAULT '0' COMMENT' Whether user accounts are locked ', 'lockrelease_time' TIMESTAMP NULL 'user account lock expiration time ',' expired_time 'TIMESTAMP NULL' user account expiration time ', 'create_time' TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'create_time ', PRIMARY KEY (' id ')) COMMENT=' InnoDB ';Copy the code

CREATE TABLE `sys_user_role` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `role_id` VARCHAR(16) NULL DEFAULT NULL,
    `user_id` VARCHAR(18) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;Copy the code

Hidden requirements are reflected in the user’s information sheet. For example, the relationship between multiple login locking and the lock expiration time. Setting rules of account validity period, etc.

Of course, more information may be added to the user table according to different services, such as user profile pictures and so on. However, in the development of large business systems, the user table used in the business module and the user table used in the permission management module are not usually one, but are stored separately according to some unique fields. The advantage of this is that frequently changing business requirements do not affect the infrequently changing permissions model.

We look forward to your attention

  • I recommend the blogger’s series of documents: “Hand on Hand to teach you to learn SpringBoot Series – Chapter 16, Section 97”.
  • This article is reprinted with a credit (must be accompanied by a link, not only the text) : Antetokounmpo blog.