web系统权限设计

 

应该有七张表

1.appSystem 表；

  主要在多系统中的 统一权限管理；主要就是系统的URL

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[AppSystem] 脚本日期: 2016/9/22 14:45:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AppSystem] (
    [AppCode]    CHAR (8)       NOT NULL,
    [AppName]    NVARCHAR (50)  NOT NULL,
    [Url]        NVARCHAR (200) NULL,
    [CreateTime] DATETIME       NOT NULL,
    [ModifyTime] DATETIME       NULL,
    [CreateBy]   INT            NOT NULL,
    [ModifyBy]   INT            NULL,
    [Remark]     NVARCHAR (200) NULL
);

2.Funtion表

然后就是我们的Function表；主要是我们的额菜单功能；{xxContorlller/Index}

会有appCode 和 自己的 FunCode

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[Funcation] 脚本日期: 2016/9/22 14:49:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Funcation] (
    [FunCode]    NVARCHAR (50)  NOT NULL,
    [AppCode]    NVARCHAR (50)  NOT NULL,
    [FunName]    NVARCHAR (20)  NOT NULL,
    [ParentID]   NVARCHAR (50)  NOT NULL,
    [PageUrl]    NVARCHAR (200) NULL,
    [IsMenu]     INT            NULL,
    [Icon]       VARCHAR (200)  NULL,
    [CreateTime] DATETIME       NOT NULL,
    [ModifyTime] DATETIME       NULL,
    [CreateBy]   INT            NOT NULL,
    [ModifyBy]   INT            NULL,
    [Remark]     NVARCHAR (200) NULL
);

3.FunAction表

  主要是控制我们的action的权限。主要是controller下的action控制；

  会自己的ActionCode和FunCode

 

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[Funcation] 脚本日期: 2016/9/22 14:51:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Funcation] (
    [FunCode]    NVARCHAR (50)  NOT NULL,
    [AppCode]    NVARCHAR (50)  NOT NULL,
    [FunName]    NVARCHAR (20)  NOT NULL,
    [ParentID]   NVARCHAR (50)  NOT NULL,
    [PageUrl]    NVARCHAR (200) NULL,
    [IsMenu]     INT            NULL,
    [Icon]       VARCHAR (200)  NULL,
    [CreateTime] DATETIME       NOT NULL,
    [ModifyTime] DATETIME       NULL,
    [CreateBy]   INT            NOT NULL,
    [ModifyBy]   INT            NULL,
    [Remark]     NVARCHAR (200) NULL
);

4.systemRole表

   这个就是web系统额角色；主要就有个RoleID和RoleName

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[SysRole] 脚本日期: 2016/9/22 14:55:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SysRole] (
    [RoleID]     NVARCHAR (50) NOT NULL,
    [RoleName]   NVARCHAR (20) NOT NULL,
    [Status]     INT           NULL,
    [CreateTime] DATETIME      NOT NULL,
    [ModifyTime] DATETIME      NULL,
    [CreateBy]   INT           NOT NULL,
    [ModifyBy]   INT           NULL
);

5.当然也会有我们的SystemUser表的呀

 主要就一个UserID 和 UserName

 

6.UserRole

 一个用户可以有多个角色，

一个角色可以别多个用户所拥有。

主要就是userID 和 roleID

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[UserRole] 脚本日期: 2016/9/22 15:02:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserRole] (
    [UserID]     VARCHAR (30)  NOT NULL,
    [RoleID]     NVARCHAR (50) NOT NULL,
    [CreateTime] SMALLDATETIME NOT NULL,
    [CreateBy]   INT           NOT NULL,
    [ID]         BIGINT        IDENTITY (1, 1) NOT NULL
);

 

7.RoleFun

  同样一个角色可以有多个功能

 一个功能可以被多个角色拥有

 RoleID FunCode AcCode

USE [Star_Permission]
GO

/****** 对象: Table [dbo].[SysRoleFun] 脚本日期: 2016/9/22 15:01:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SysRoleFun] (
    [ID]         INT           IDENTITY (1, 1) NOT NULL,
    [RoleID]     NVARCHAR (50) NOT NULL,
    [FunCode]    NVARCHAR (50) NULL,
    [ActCode]    NVARCHAR (50) NULL,
    [CreateTime] DATETIME      NOT NULL,
    [ModifyTime] DATETIME      NULL,
    [CreateBy]   INT           NOT NULL,
    [ModifyBy]   INT           NULL
);

 

  使用的时候，我们先从sql中进行过滤；

  （ps，直接这样做，不太好，应该把过滤条件直接加在我们sql语句中取出来的）

/// <summary>
        /// 获取用户权限
        /// </summary>
        /// <returns>List&lt;UserPermissionDto&gt;.</returns>
        public List<UserPermissionDto> GetUserPermissionList()
        {
            string sql = @"
       WITH RoleIds
         AS ( SELECT   RoleId ,
                        UserID
               FROM     dbo.UserRole AS R WITH ( NOLOCK )
             ),
        Funs
        AS ( SELECT   M.UserID ,
                        M.RoleId ,
                        F.FunCode AS Code ,
                        F.PageUrl AS ActName
               FROM     RoleIds AS M
                        INNER JOIN dbo.SysRoleFun AS R WITH ( NOLOCK ) ON M.RoleId = R.RoleId
                        INNER JOIN dbo.Funcation AS F WITH ( NOLOCK ) ON F.FunCode = R.FunCode
             ),
        Acts
        AS ( SELECT   M.UserID ,
                        M.RoleId ,
                        A.ActCode AS Code ,
                        A.ActName
               FROM     RoleIds AS M WITH ( NOLOCK )
                        INNER JOIN dbo.SysRoleFun AS R WITH ( NOLOCK ) ON M.RoleId = R.RoleId
                        INNER JOIN dbo.FunAction AS A WITH ( NOLOCK ) ON A.ActCode = R.ActCode
             )
    SELECT  UserID , RoleId ,Code , ActName
    FROM    Funs
    UNION
    SELECT  UserID ,RoleId ,Code ,ActName
    FROM    Acts
                ";
            using (var dbScope = _dbScopeFactory.CreateReadOnly())
            {
                var db = dbScope.DbContexts.Get<MaybachDbContext>();
                var list = db.Database.SqlQuery<UserPermissionDto>(sql);

                return list.ToList();
            }
        }

然后装入我们缓存中，

  class CacheHelper
    {
        ///  <returns >权限项列表 </returns >
        public static List<UserPermissionDto> GetPermissionItems()
        {
            // 如果缓存中已经存在权限列表信息，则直接从缓存中读取。
            if (HttpContext.Current.Cache["PermissionItems"] == null)
            {
                // 如果缓存中没有或者已经过期权限列表信息，则从数据库获取并写入缓存
                var httpDto = IocConfig.Container.GetInstance<ISysRoleProvider>().GetUserPermissionList();
                if (httpDto != null)
                {
                    HttpContext.Current.Cache.Insert("PermissionItems", httpDto, null, DateTime.Now.AddMinutes(1), TimeSpan.Zero);
                }
            }
            return (List<UserPermissionDto>)HttpContext.Current.Cache["PermissionItems"];
        }
    }

 然后就接着看我们下面的各种代码了滴呀；

 然后添加，我们的过滤文件：

 

包含我们的授权，权限，异常处理，各种过滤器的地呀;

 

然后在onActionExecuting中 添加方法：

 

 public class PermissionFilterAttribute : ActionFilterAttribute
        {
            public override void OnActionExecuting(ActionExecutingContext filterContext)
            {    
                if (Thread.CurrentPrincipal.Identity.IsAuthenticated)
                {
                    //AllowAnonymousAccess
                    var actionFilter = filterContext.ActionDescriptor.GetCustomAttributes(typeof(AllowAnonymousAccess), false);
                    var controllerFilter = filterContext.ActionDescriptor.ControllerDescriptor.GetCustomAttributes(typeof(AllowAnonymousAccess), false);
                    if (controllerFilter.Length == 1 || actionFilter.Length == 1)
                    {
                        base.OnActionExecuting(filterContext);
                        return;
                    }

                    var user = Thread.CurrentPrincipal.Identity as ClaimsIdentity;
                    var nickName = user.Claims.FirstOrDefault(r => r.Type == "nickname").Value;
                    var userId = user.GetLoginUserId();

                    //测试账户 
                    if (string.Equals(nickName, "揉一", StringComparison.Ordinal))
                    {
                        base.OnActionExecuting(filterContext);
                        return;
                    }

                    //var userDto =  IocConfig.Container.GetInstance<IUserProvider>().GetUserByNickName(nickName);
                    //var permisionList = CacheHelper.GetPermissionItems();
                    var permisionList = CacheHelper.GetPermissionItems().FindAll(item => item.UserID == userId);
                    var atcion = $@"{filterContext.RouteData.Values["controller"]}/{filterContext.RouteData.Values["action"]}";

                    var isExists = permisionList.Exists(p => p.ActName.ToLower().Contains(atcion.ToLower()));
                    if (isExists)
                    {
                        base.OnActionExecuting(filterContext);
                        return;
                    }
                    else
                    {
                        if (filterContext.RequestContext.HttpContext.Request.IsAjaxRequest())
                        {
                            var json = new JsonResult()
                            {
                                Data = new { Code = "403", Message = "对不起,你没有操作权限!" },
                                JsonRequestBehavior = JsonRequestBehavior.AllowGet
                            };
                            filterContext.Result = json;
                        }
                        else
                        {
                            filterContext.Result = new RedirectResult("/NoPermission/Index");
                        }
                    }
                }
            }
        }

 

然后......