每个系统几乎都会有权限管理这一模块,我也为许多个大中小项目搭建过权限管理的模块,总结了一些经验和设计的思想,现在分享给大家,欢迎大家指出不足和一起交流。下面我列出数据库权限模块的表设计,为了做最大的分享,下面的SQL语句和代码都是能直接运行。BEGIN TRANSACTIONCR ...
每个系统几乎都会有权限管理这一模块,我也为许多个大中小项目搭建过权限管理的模块,总结了一些经验和设计的思想,现在分享给大家,欢迎大家指出不足和一起交流。
下面我列出数据库权限模块的表设计,为了做最大的分享,下面的SQL语句和代码都是能直接运行。
BEGIN TRANSACTIONCREATE TABLE [dbo].[UserAccount]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [UserName] [nvarchar](50) NOT NULL, [Password] [varchar](150) NOT NULL, [Email] [varchar](100) NOT NULL,[IsOnline] [bit] NOT NULL, CONSTRAINT [PK_UserAccount] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[Permission]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [Name] [nvarchar](250) NOT NULL, [Description] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[UserAccountXPermission]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [UserAccountID] [bigint] NOT NULL, [PermissionID] [bigint] NOT NULL, CONSTRAINT [PK_UserAccountXPermission] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[UserAccountXPermission] WITH CHECK ADD CONSTRAINT [FK_UserAccountXPermission_Permission] FOREIGN KEY([PermissionID])REFERENCES [dbo].[Permission] ([ID])GOALTER TABLE [dbo].[UserAccountXPermission] CHECK CONSTRAINT [FK_UserAccountXPermission_Permission]GOALTER TABLE [dbo].[UserAccountXPermission] WITH CHECK ADD CONSTRAINT [FK_UserAccountXPermission_UserAccount] FOREIGN KEY([UserAccountID])REFERENCES [dbo].[UserAccount] ([ID])GOALTER TABLE [dbo].[UserAccountXPermission] CHECK CONSTRAINT [FK_UserAccountXPermission_UserAccount]GOCREATE TABLE [dbo].[Role]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [Name] [nvarchar](250) NOT NULL, [Description] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[RoleXPermission]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, [PermissionID] [bigint] NOT NULL, CONSTRAINT [PK_RoleXPermission] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[RoleXPermission] WITH CHECK ADD CONSTRAINT [FK_RoleXPermission_Permission] FOREIGN KEY([PermissionID])REFERENCES [dbo].[Permission] ([ID])GOALTER TABLE [dbo].[RoleXPermission] CHECK CONSTRAINT [FK_RoleXPermission_Permission]GOALTER TABLE [dbo].[RoleXPermission] WITH CHECK ADD CONSTRAINT [FK_RoleXPermission_Role] FOREIGN KEY([RoleID])REFERENCES [dbo].[Role] ([ID])GOALTER TABLE [dbo].[RoleXPermission] CHECK CONSTRAINT [FK_RoleXPermission_Role]GOCREATE TABLE [dbo].[RoleXUserAccount]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [UserAccountID] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, CONSTRAINT [PK_RoleXUserAccount] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[RoleXUserAccount] WITH CHECK ADD CONSTRAINT [FK_RoleXUserAccount_Role] FOREIGN KEY([RoleID])REFERENCES [dbo].[Role] ([ID])GOALTER TABLE [dbo].[RoleXUserAccount] CHECK CONSTRAINT [FK_RoleXUserAccount_Role]GOALTER TABLE [dbo].[RoleXUserAccount] WITH CHECK ADD CONSTRAINT [FK_RoleXUserAccount_UserAccount] FOREIGN KEY([UserAccountID])REFERENCES [dbo].[UserAccount] ([ID])GOALTER TABLE [dbo].[RoleXUserAccount] CHECK CONSTRAINT [FK_RoleXUserAccount_UserAccount]GOCREATE TABLE [dbo].[Log]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [LogTime] [datetime] NOT NULL, [LogSource] [nvarchar](350) NOT NULL, [LogMessage] [ntext] NOT NULL, [LogLevel] [int] NOT NULL, CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[EmailConfirmation]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL, [CreatedBy] [bigint] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [LastModifiedBy] [bigint] NOT NULL, [UserAccountID] [bigint] NOT NULL, [Code] [nvarchar](250) NOT NULL, [Type] [int] NOT NULL, CONSTRAINT [PK_EmailConfirmation] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[EmailConfirmation] WITH CHECK ADD CONSTRAINT [FK_EmailConfirmation_UserAccount] FOREIGN KEY([UserAccountID])REFERENCES [dbo].[UserAccount] ([ID])GOALTER TABLE [dbo].[EmailConfirmation] CHECK CONSTRAINT [FK_EmailConfirmation_UserAccount]GOCOMMIT TRANSACTION
原标题:ASP.NET大中小型系统权限管理设计
关键词:ASP.NET
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。