Management of user permissions in the background database design, management of user permissions in the background

Management of user permissions in the background database design, management of user permissions in the background

From prism school-online IT education www.prismcollege.com

1. Create a basic table

Create three tables: User, Role, and Module. each row of a User corresponds to a User information. each row of a Role stores a Role information, including the Role id and name, each row in the Module stores a Module information, including the Module id and corresponding name.

The SQL statement for creating three tables (MySql environment) is as follows:

1) create a user table with the following table structure:

2) create a role table with the following structure:

3) create a module table with the following structure:

Create table 'of _ user '(
'User _ id' varchar (100 )',
'Username' varchar (255 ),
'Password' varchar (255 ),
'Name' varchar (255 ),
'Role _ id' varchar (100 ),
'Number' varchar (100 ),
Primary key ('user _ id ')
) ENGINE = InnoDB default charset = utf8;

Create table 'of _ role '(
'Role _ id' varchar (50 ),
'Role _ name' varchar (100 ),
Primary key ('Role _ id ')
) ENGINE = InnoDB default charset = utf8;

Create table 'of _ menu '(
'Menu _ id' int (11) not null,
'Menu _ name' varchar (254 ),
'Menu _ url' varchar (254 ),
'Parent _ id' varchar (20 ),
'Menu _ order' varchar (80 ),
'Menu _ ICON 'varchar (30 ),
Primary key ('menu _ id ')
) ENGINE = InnoDB default charset = utf8;

Create table 'of _ user_group '(
'Id' varchar (50 ),
'User _ id' varchar (50 ),
'Group _ id' varchar (50 ),
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;

2. Insert initial data

1) define a module. Suppose there are the following modules. query report B. account Management c. information Publishing d. list of registered users e. recruitment Information List of several modules, insert each module into the module table.

The module content after insertion is as follows:

2) define the role. Initially, define the role a. Super administrator B. Administrator c. Information publisher d. Job Seeker and other roles. The inserted role content is as follows:

3) define users. The initial users are as follows: liubei, zhugeliang, zhangfei, and lvbu.

The inserted user content is as follows:

 

3. Create an association table

1) create an association table user_role between the user and the role. One user corresponds to one or more role. One role can correspond to multiple users and set the liubei role to administrator, zhugeliang is admin and distributor, zhangfei is distributor, and lvbu is jobseeker. The relationship between the user zhugeliang and the role is one-to-many, and the relationship between the role distributor and the user is one-to-many.

The user_role table structure after creation is as follows:

The ing between users and roles is saved as follows:

2) create an association ing table role_module between the role and module. The administrator can view the journal_sheet module. The admin can view all modules except journal_sheet: info_distribute, account_manage, user_list, job_list, distributor can view the info_distribute module, and jobseeker can view the job_list module.

The structure of the created role_module table is as follows:

The ing between role and module is saved as follows:

Create table 'of _ user_role '(
'User _ id' int (50 ),
'Role _ id' int (50 ),
Primary key ('user _ id ')
) ENGINE = InnoDB default charset = urf-8;

Create table 'of _ role_menu '(
'Role _ id' int (50 ),
'Menu _ id' int (50)
) ENGINE = InnoDB default charset = utf8;

So far, user permissions have been designed through the database.

For more information, please join the QQ group:3686148499368614849368614849368614849