User, role, rights database design

Rights Management

Rights management, mainly the relationship between people and permissions, but if people directly and permissions to deal with, then the assignment of permissions, permissions revocation and change of permissions will be very troublesome, so introduced, role, assign permissions to the role, and then assign roles to the user.

This design mainly involves 6 sheets,

User tables (to store all information about a user)

Permission table (to store all permissions)

Role table (for storing all roles)

Association tables for users and roles (association of users and roles)

Association tables for roles and permissions (associations of roles and permissions)

Menu table (which is associated with permissions, mainly for practical use)

User table

CREATE TABLE [dbo]. [Users] (    [UserID] [int.] IDENTITY () not NULL,    [UserName] [nvarchar] (primary key,--account    [Password] [nvarchar] ( [Userdspname] [nvarchar] (+), [    Sex] [char] (1),    [Birthday] [datetime],    [Phone] [nvarchar] (20)  ,    [Email] [nvarchar] (+),    [EmployeeID] [nvarchar] (+), [    Activity] [bit],--available    [usertype] [char] (2),    [Style] [ NVARCHAR] (50))

Permission table:

CREATE TABLE [dbo]. [Permission] (    [PermissionID] int identity,    [Description] [nvarchar] (50)--permission name)

Role table:

CREATE TABLE [dbo]. [Roles] (    [Roleid] [int] IDENTITY,    [Description] [nvarchar] (200)--Role name)

Association tables for users and roles:

CREATE TABLE [dbo]. [Userroles] (    [UserID] [int] NOT NULL,--user ID    [roleid] [int] NOT NULL,--permission ID CONSTRAINT [pk_userroles] PRIMARY KEY CLUSTERED (    [UserID] ASC,    [Roleid] ASC) with (Ignore_dup_key = OFF) on [PRIMARY]) on [PRIMARY]

Associated tables for roles and permissions:

CREATE TABLE [dbo]. [Rolepermissions] (    [Roleid] int not  null,--role ID    [Permissionid]int NOT NULL,--permission ID CONSTRAINT [pk_rolepermissions] PRIMARY KEY CLUSTERED (    [Roleid] ASC,    [PermissionID] ASC) with (Ignore_dup_key = OFF) on [PRIMARY]) on [PRIMARY]

Menu table:

CREATE TABLE [dbo]. [Menu] (    [ID] [int] IDENTITY () not NULL,    [textch] [nvarchar] (+)  null,--menu in Chinese    [texten] [nvarchar] (200  null,--The English name of the menu    [parentid] [int] NULL,--parent node    [OrderID] [int] null,--sort under the same parent node    [URL] [nvarchar] ( ,--menu for Permissions    [PermissionID] [int] NULL,--permission ID    [IMAGEURL] [nvarchar] (null--menu picture link) on [PRIMARY]

User, role, rights database design