ASP.NET大中小型系统权限管理设计

来源:互联网 时间:1970-01-01

每个系统几乎都会有权限管理这一模块,我也为许多个大中小项目搭建过权限管理的模块,总结了一些经验和设计的思想,现在分享给大家,欢迎大家指出不足和一起交流。

下面我列出数据库权限模块的表设计,为了做最大的分享,下面的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

一共是设计为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代码,大家不一定按照我的方案,我写本文的目的是想帮助搭建权限管理模块的时候有一点灵感,大家觉得有建议和不足的地方可以指出。



相关阅读:
Top