Permission Database Design (SQL Server)

Personal summary, for reference only !!! Suitable for small websites !!!
Five database tables are used to create roles and permissions:
1. User information table
Create table employee
(
Userid varchar (50) not null, -- User ID
Username varchar (100), -- User Name
Userpassword varchar (100), -- Password
..
..
..
..
)
Alter table employee -- primary key
Add constraint pk_employee_userid primary key (userid)
2. Role table
Create table role
(
Roleid varchar (50) not null, -- Role Id
Rolename varchar (100), -- role name
)
Alter table tole -- primary key
Add constraint pk_role_roleid primary key (roleid)
3. Permission table
Create table popedom
(
Popedomid int identity (1, 1) not null, -- permission Id
Popedomname varchar (100), -- permission name
Popedomfatherid int, -- permission parent ID
Popedomurl varchar (100) -- connection path of the tree
..
..
)
Er table popedom -- primary key
Add constraint PK_popedom primary key (popedomid)

Add data as shown in Figure
Insert into popedom values ('My desk ', 0 ,'')
Insert into popedom values ('email address', 1, '../mail/EmaiolManage. aspx ')
(The principle of adding data is that the popedomfatherid of the first-level contact is 0. If it is a contact under (my desk), their popedomfatherid is the primary key of the (my desk)

4. user-role relationship table
Create table user_role
(
Connectionid int identity (1, 1) not null, -- Link ID
Userid varchar (50) not null, -- administrator table ID
Roleid varchar (50) not null -- Role Id
)
Alter table user_role -- primary key
Add constraint PK_admin_role primary key (connectionid)
5. Role-Permission relationship table
Create table role_popedom -- Role and permission table
(
Connectionid int identity (1, 1), -- Link Id
Roleid varchar (50) not null, -- Role ID
Popedomid int not null, -- permission Id
Popedom int -- permission (1 is available, 2 is unavailable)
)
Alter table role_popedom -- primary key
Add constraint PK_role_popedom primary key (connectionid) -- primary key

1. the user information table is the basic user information table.
2. Role tables are the roles used in the system, such as system administrators and general managers ......
3. The permission table is built on a tree structure, which is generally managed by the background system administrator.
4. the user-role relationship table establishes a relationship between the user and the role.
5. The relationship table between roles and permissions establishes a relationship between roles and permissions.