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

Source: Internet
Author: User

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

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.