这节我们说下权限系统的特点,本系统采用的是MVC4+EF5+IOC 接口编程的架构,其中的权限树用的是DWTree,功能上做到灵活，授权操控细致，权限可以细到按钮级别
，为了部署简单,导致设计和编码上也是比较复杂
该系统主要功能如下：
- 自定义操作动作(如增加、删除、修改、审核等，不再是以前见过的那种粗粒度的 按模块分配权限，或者稍微先进点的规定死某几个操作了)。
- 无限级功能模块管理，自定义模块排序，可以更好地对整个系统中所有模块进行 分类管理。
- 灵活地为各模块分配操作，即每个模块有哪些操作需要被控制(将步骤1中添加的 操作按需分配给各模块)。
- 对所有用户基础信息进行管理，实行有效期机制，过期自动失效，在有效期间亦 可强制停止用户使用。
- 自定义角色，可以起个听起来通俗易懂的名字。
- 任意角色可任意组合系统权限功能点(比如某个模块里他只能修改数据而不能添加 或删除等其它操作)
- 一个用户可有多个角色(多身份)，一个角色也可以被多个用户拥有(同身份)（多对多），灵活授权。
- 按角色给用户授权，当授权对象数量庞大的时候就可大显身手了(比如给公司全体 员工授权)。
- 按用户分配角色，当某个用户以多种身份出现在系统中的时候，希望分配多个角 色。
- 菜单的显示根据权限来显示，如果一个用户拥有模块的一个以上操作码权限，则菜单被显示，否则不读取
从数据库结构来看，一共设计了七个表(table)，表名及功能分别如下：
1.模块管理（SysModule）
系统菜单的管理,支持无限级别树,表我们在之前的文章已经建好了
2.用户表（SysUser）
系统会员,登录系统的唯一凭证
3.角色组（SysRole）
对角色组基本信息进行管理。用户可以自定义成各种各样的角色组，然后对用户授权
4.模块操作码（SysModuleOperate）
操作码表的父表是模块表，一下模块下面有多个操作码，比如增加，删除，审核，修改等.
5.授权表（SysRoleSysUser）
用户和角色组的对应表，一个角色拥有多个用户，一个用户可以拥有多个角色
6.角色权限表(SysRight)
将角色与系统中的权限点关联起来，也就是完成授权的动作。
7.被付权限操作码表(SysRightOperate)
保存有权限的角色的操作码，当有一个操作码被赋予权限时，将激活对应的菜单
我们通过一张物理模型图看出表与表之前的关系
现在通过物理模型转化为SQL语句
CREATE TABLE [dbo].[SysModule]( [Id] [varchar](50) NOT NULL, [Name] [varchar](200) NOT NULL, [EnglishName] [varchar](200) NULL, [ParentId] [varchar](50) NULL, [Url] [varchar](200) NULL, [Iconic] [varchar](200) NULL, [Sort] [int] NULL, [Remark] [varchar](4000) NULL, [State] [bit] NULL, [CreatePerson] [varchar](200) NULL, [CreateTime] [datetime] NULL, [IsLast] [bit] NOT NULL, [Version] [timestamp] NULL, CONSTRAINT [PK_SysModule] 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] GO CREATE TABLE [dbo].[SysModuleOperate]( [Id] [varchar](200) NOT NULL, [Name] [varchar](200) NOT NULL, [KeyCode] [varchar](200) NOT NULL, [ModuleId] [varchar](50) NOT NULL, [IsValid] [bit] NOT NULL, [Sort] [int] NOT NULL, CONSTRAINT [PK_SysModuleOperate] 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] GO CREATE TABLE [dbo].[SysRole]( [Id] [varchar](50) NOT NULL, [Name] [varchar](200) NOT NULL, [Description] [varchar](4000) NOT NULL, [CreateTime] [datetime] NOT NULL, [CreatePerson] [varchar](200) NOT NULL, CONSTRAINT [PK_SysRole] 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] GO CREATE TABLE [dbo].[SysUser]( [Id] [varchar](50) NOT NULL, [UserName] [varchar](200) NOT NULL, [Password] [varchar](200) NOT NULL, [TrueName] [varchar](200) NULL, [Card] [varchar](50) NULL, [MobileNumber] [varchar](200) NULL, [PhoneNumber] [varchar](200) NULL, [QQ] [varchar](50) NULL, [EmailAddress] [varchar](200) NULL, [OtherContact] [varchar](200) NULL, [Province] [varchar](200) NULL, [City] [varchar](200) NULL, [Village] [varchar](200) NULL, [Address] [varchar](200) NULL, [State] [bit] NULL, [CreateTime] [datetime] NULL, [CreatePerson] [varchar](200) NULL, [Sex] [varchar](10) NULL, [Birthday] [datetime] NULL, [JoinDate] [datetime] NULL, [Marital] [varchar](10) NULL, [Political] [varchar](50) NULL, [Nationality] [varchar](20) NULL, [Native] [varchar](20) NULL, [School] [varchar](50) NULL, [Professional] [varchar](100) NULL, [Degree] [varchar](20) NULL, [DepId] [varchar](50) NOT NULL, [PosId] [varchar](50) NOT NULL, [Expertise] [varchar](3000) NULL, [JobState] [varchar](20) NULL, [Photo] [varchar](200) NULL, [Attach] [varchar](200) NULL, CONSTRAINT [PK_SysUser] 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] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'身份证' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'MobileNumber' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'婚姻' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Marital' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'党派' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Political' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'民族' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Nationality' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'籍贯' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Native' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'毕业学校' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'School' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'就读专业' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Professional' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Degree' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'DepId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'职位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'PosId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'个人简介' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Expertise' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'在职状况' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'JobState' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Photo' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SysUser', @level2type=N'COLUMN',@level2name=N'Attach' GO CREATE TABLE [dbo].[SysRoleSysUser]( [SysUserId] [varchar](50) NOT NULL, [SysRoleId] [varchar](50) NOT NULL, CONSTRAINT [PK_SysRoleSysUser] PRIMARY KEY CLUSTERED ( [SysUserId] ASC, [SysRoleId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[SysRight]( [Id] [varchar](200) NOT NULL, [ModuleId] [varchar](50) NOT NULL, [RoleId] [varchar](50) NOT NULL, [Rightflag] [bit] NOT NULL, CONSTRAINT [PK_SysRight] 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] GO CREATE TABLE [dbo].[SysRightOperate]( [Id] [varchar](200) NOT NULL, [RightId] [varchar](200) NOT NULL, [KeyCode] [varchar](200) NOT NULL, [IsValid] [bit] NOT NULL, CONSTRAINT [PK_SysRightOperate] 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] GO ALTER TABLE [dbo].[SysModule] WITH NOCHECK ADD CONSTRAINT [FK_SysModule_SysModule] FOREIGN KEY([ParentId]) REFERENCES [dbo].[SysModule] ([Id]) GO ALTER TABLE [dbo].[SysModule] NOCHECK CONSTRAINT [FK_SysModule_SysModule] GO ALTER TABLE [dbo].[SysModuleOperate] WITH CHECK ADD CONSTRAINT [FK_SysModuleOperate_SysModule] FOREIGN KEY([ModuleId]) REFERENCES [dbo].[SysModule] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[SysModuleOperate] CHECK CONSTRAINT [FK_SysModuleOperate_SysModule] GO ALTER TABLE [dbo].[SysRoleSysUser] WITH CHECK ADD CONSTRAINT [FK_SysRoleSysUser_SysRole] FOREIGN KEY([SysRoleId]) REFERENCES [dbo].[SysRole] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[SysRoleSysUser] CHECK CONSTRAINT [FK_SysRoleSysUser_SysRole] GO ALTER TABLE [dbo].[SysRoleSysUser] WITH CHECK ADD CONSTRAINT [FK_SysRoleSysUser_SysUser] FOREIGN KEY([SysUserId]) REFERENCES [dbo].[SysUser] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[SysRoleSysUser] CHECK CONSTRAINT [FK_SysRoleSysUser_SysUser] GO ALTER TABLE [dbo].[SysRight] WITH CHECK ADD CONSTRAINT [FK_SysRight_SysModule] FOREIGN KEY([ModuleId]) REFERENCES [dbo].[SysModule] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[SysRight] CHECK CONSTRAINT [FK_SysRight_SysModule] GO ALTER TABLE [dbo].[SysRight] WITH CHECK ADD CONSTRAINT [FK_SysRight_SysRole] FOREIGN KEY([RoleId]) REFERENCES [dbo].[SysRole] ([Id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[SysRight] CHECK CONSTRAINT [FK_SysRight_SysRole] GO ALTER TABLE [dbo].[SysRightOperate] WITH CHECK ADD CONSTRAINT [FK_SysRightOperate_SysRight] FOREIGN KEY([RightId]) REFERENCES [dbo].[SysRight] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[SysRightOperate] CHECK CONSTRAINT [FK_SysRightOperate_SysRight] GO
复制SQL语句放到查询窗口执行一下然后用视图看看他们之间的对应关系
今天就到这里了
本文转自ymnets博客园博客，原文链接：http://www.cnblogs.com/ymnets/p/3424985.html，如需转载请自行联系原作者
