每个系统几乎都会有权限管理这一模块，我也为许多个大中小项目搭建过权限管理的模块，总结了一些经验和设计的思想，现在分享给大家，欢迎大家指出不足和一起交流。
下面我列出数据库权限模块的表设计，为了做最大的分享，下面的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
View Code
一共是设计为6个表：UserAccount（用户表），Permission（权限表），Role（角色表），UserAccountXPermission（用户权限表），RoleXPermission（角色权限表），RoleXUserAccount（用户角色表）。
现在我们来分析这6个表，UserAccount是用户表大家都懂，Permission和Role代表权限和角色，UserAccountXPermission代表一个用户拥有的权限，RoleXPermission代表一个角色里拥有的权限，RoleXUserAccount代表一个用户拥有的角色。我个人的表设计
有"X"是代表两个表之间的关联性，大部分的人设计关联表名会是UserAccount_Permission或者UserAccountPermission等等，我觉得用"X"来进行这样的设计会比较容易理解。
大多数的权限模块设计都是会缺少UserAccountXPermission这一块，则为用户权限表，我个人理解用户是可以拥有一些权限，而不是只是作为一个角色而言，从世界观的角度去想，假如这个用户不一定会拥有一些角色，只是拥有一些权限，那这个设计就会派上用途
了。但是不一定是必要的，大家可以在设计的时候符合项目情景做调整。解释完了表的设计后，我们开始实际运用如何对系统加上权限模块。
以下我们以ASP.NET MVC来进行演示如何进行权限验证的，首先我们建立一个HomeController，并有一个Action为Index。
public class HomeController : Controller{ public ActionResult Index() { return View(); }}最简单的返回Index视图，假设我们希望访问这个Action(即显示Index视图)需要一定的权限，或者需要拥有某些角色，我们这时要怎么做呢？
很简单，我们首先创建一个RequireRolesOrPermissionsAttribute来进行标示这个Action，或者Controller访问的时候所需要的权限或者角色。
public class RequireRolesOrPermissionsAttribute : Attribute
{ private string[] _rolesOrPermissionsName; public RequireRolesOrPermissionsAttribute(params string[] rolesOrPermissionsName) { this._rolesOrPermissionsName = rolesOrPermissionsName; } public string[] RolesOrPermissionsName { get { return this._rolesOrPermissionsName; } }}
由于一个Action可能需要多个角色和多个权限，所以我们用的是string[]类型来保存需要验证的权限或者角色，建立好了这个Attribute后，我们把之前的HomeController的Index Action修改一下：
public class HomeController : Controller{ [RequireRolesOrPermission("System_Admin", "Can_View_Index")] public ActionResult Index() { return View(); }}现在意味着我们访问Index的时候，需要检查当前用户是否属于System_Admin的角色还有是否拥有Can_View_Index的权限。
我们标识了这个Action需要验证的权限后，我们来创建一个权限过滤器来对此进行验证。
public class MyAuthorizeAttribute : AuthorizeAttribute { private readonly ISecurityService _securityService; private string[] rolesOrPermissionsName = null; private Dictionary<string, string[]> cacheDic = new Dictionary<string, string[]>(); private int statusCode; public UnitoysAuthorizeAttribute() { } public UnitoysAuthorizeAttribute(ISecurityService securityService) { this._securityService = securityService; } public override void OnAuthorization(AuthorizationContext filterContext) { if (filterContext == null) { throw new ArgumentNullException("filterContext"); } //1. 首先判断用户是否登录。 if (filterContext.HttpContext.Request.IsAuthenticated) { var controllerDescriptor = filterContext.ActionDescriptor.ControllerDescriptor; var actionDescriptor = filterContext.ActionDescriptor; //2. 记录访问的ControllerName和ActionName作为CacheKey。 var cacheKey = controllerDescriptor.ControllerName + "." + actionDescriptor.ActionName; //持久化对象，每次要初始化。 rolesOrPermissionsName = null; //3. 获取访问需要的权限，先判断CacheDictionary里是否存在，不存在则获取并添加到CacheDictionary里。 if (!cacheDic.ContainsKey(cacheKey)) { //4. 首先判断Action上是否有定义RequireRolesOrPermissionsAttribute。 var attrs = actionDescriptor.GetCustomAttributes(typeof(RequireRolesOrPermissionsAttribute), false); if (attrs.Length == 1) { rolesOrPermissionsName = ((RequireRolesOrPermissionsAttribute)attrs[0]).RolesOrPermissionsName; } else { //5. 如果Action找不到则判断Controller上是否有定义RequireRolesOrPermissionsAttribute。 attrs = controllerDescriptor.GetCustomAttributes(typeof(RequireRolesOrPermissionsAttribute), false); if (attrs.Length == 1) { rolesOrPermissionsName = ((RequireRolesOrPermissionsAttribute)attrs[0]).RolesOrPermissionsName; } } if (rolesOrPermissionsName != null) { cacheDic[cacheKey] = rolesOrPermissionsName; } } else { rolesOrPermissionsName = cacheDic[cacheKey]; } } base.OnAuthorization(filterContext); } protected override bool AuthorizeCore(System.Web.HttpContextBase httpContext) { if (httpContext == null) { throw new ArgumentNullException("HttpContext"); } //1. 判断是否已经登录，若没有登录则跳转到登录界面。 if(!httpContext.Request.IsAuthenticated) { return false; } if (rolesOrPermissionsName != null) { //2. 判断当前用户是否拥有访问权限。 bool isUserInRoleOrHasPermission = _securityService.IsInRole(httpContext.User.Identity.Name, rolesOrPermissionsName); //3. 如果没有权限则返回403错误。 if (!isUserInRoleOrHasPermission) { statusCode = 403; return false; } } return true; } protected override void HandleUnauthorizedRequest(AuthorizationContext filterContext) { bool isAjaxRequest = filterContext.HttpContext.Request.IsAjaxRequest(); switch (statusCode) { case 403: if(isAjaxRequest) { filterContext.Result = new HttpStatusCodeResult(System.Net.HttpStatusCode.Forbidden); } else { ViewResult result = new ViewResult() { ViewName = "AccessDenied" }; filterContext.Result = result; } break; default: filterContext.Result = new RedirectResult("/Manage/Login"); break; } } }大概的思路应该从代码上可以看得出，首先权限过滤器获取出访问的Controller和Action是否标识了RequireRolesOrPermissionsAttribute，有则判断当前用户是否具有标识的权限，没有则跳转到AccessDenied界面，代码只是提供了一种思路，具体的可以按照系统
的需求来进行修改。
开头声明了要做最大的分享，下面再附上SecurityService里的IsInRole如何判断当前用户是否拥有标识的权限的。
PS.代码可能进行过修改，仅提供了一种思想，可能复制粘贴不能直接运行。
public bool IsUserInRole(string userName, string[] rolesOrPermissionsName) { if (rolesOrPermissionsName == null || rolesOrPermissionsName.Length == 0) { throw new ArgumentNullException("roleOrPermissionNames"); } int calculateCount = rolesOrPermissionsName.Length; RoleXUserAccountHandler rxuaHandler = new RoleXUserAccountHandler(_dbContext); RoleXPermissionHandler rxpHandler = new RoleXPermissionHandler(_dbContext); UserAccountXPermissionHandler uaxpHandler = new UserAccountXPermissionHandler(_dbContext); //1. 获取当前用户所有角色。 List<RoleXUserAccount> rxuaList = rxuaHandler.LoadByUserName(userName); foreach (RoleXUserAccount rxua in rxuaList) { if(calculateCount == 0) { break; } foreach (string roleOrPermissionName in rolesOrPermissionsName) { if (rxua.Role.Name.ToUpper() == roleOrPermissionName.ToUpper()) { calculateCount--; break; } } if(calculateCount > 0) { //2. 获取角色所拥有的权限。 List<RoleXPermission> rxpList = rxpHandler.LoadByRoleName(rxua.Role.Name); foreach (RoleXPermission rxp in rxpList) { if(calculateCount == 0) { break; } foreach (string roleOrPermissionName in rolesOrPermissionsName) { if (rxp.Permission.Name == roleOrPermissionName.ToUpper()) { calculateCount--; break; } } } } } //3. 获取用户所有的权限。 if(calculateCount > 0) { List<UserAccountXPermission> uaxpList = uaxpHandler.LoadByUserName(userName); foreach (UserAccountXPermission uaxp in uaxpList) { if(calculateCount == 0) { break; } foreach (string roleOrPermissionName in rolesOrPermissionsName) { if (uaxp.Permission.Name == roleOrPermissionName.ToUpper()) { calculateCount--; break; } } } } return calculateCount == 0; }RoleXUserAccountHandler里的LoadByUserName：
public List<RoleXUserAccount> LoadByUserName(string userName) { List<RoleXUserAccount> rxuaList = null; rxuaList = _dbContext.Include(x => x.Role) .Where(x => x.UserAccount.UserName == userName) .ToList(); return rxuaList ?? new List<RoleXUserAccount>(); }RoleXPermission里的LoadByRoleName：
public List<RoleXPermission> LoadByRoleNameAsync(string roleName) { List<RoleXPermission> rxpList = null; rxpList = _dbContext.Include(x => x.Permission).Where(x => x.Role.Name == roleName).ToList(); return rxpList ?? new List<RoleXPermission>(); }UserAccountXPermission的LoadByUserName：
public List<UserAccountXPermission> LoadByUserName(string userName) { List<UserAccountXPermission> uaxpList = null; uaxpList = _dbContext.Include(x => x.Permission).Where(x => x.UserAccount.UserName == userName).ToList(); return uaxpList ?? new List<UserAccountXPermission>(); }希望对大家有帮助，不太推崇大家直接copy代码，大家不一定按照我的方案，我写本文的目的是想帮助搭建权限管理模块的时候有一点灵感，大家觉得有建议和不足的地方可以指出。
原标题：ASP.NET大中小型系统权限管理设计
关键词：ASP.NET
我知道了 