mvc 权限控制续——使用存储过程判断

以前的随笔没有给出数据库，这里给出具体的数据库以及怎么使用存储过程来判断

参考：

asp.net MVC 权限设计(续）#

http://www.cnblogs.com/xiaoqi/archive/2011/01/24/1942880.html

 下面是数据库：

?
/****** Object:  Table [dbo].[Resource]    Script Date: 03/16/2012 10:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Resource](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [ControllName] [nvarchar](max) NOT NULL,
    [IsController] [bit] NOT NULL,
    [IsAllowedNoneRoles] [bit] NOT NULL,
    [IsAllowedAllRoles] [bit] NOT NULL,
    [CreateTime] [datetime] NOT NULL,
    [IsShow] [bit] NOT NULL,
    [Title] [nvarchar](max) NULL,
    [IsHeader] [bit] NOT NULL,
 CONSTRAINT [PK_Resource] 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
/****** Object:  Table [dbo].[Role]    Script Date: 03/16/2012 10:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [nvarchar](max) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [CreateTime] [datetime] 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]
GO
/****** Object:  Table [dbo].[User]    Script Date: 03/16/2012 10:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Email] [nvarchar](max) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Password] [nvarchar](max) NOT NULL,
    [CreateTime] [datetime] NOT NULL,
    [UpdateTime] [datetime] NOT NULL
 CONSTRAINT [PK_User] 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
/****** Object:  Table [dbo].[RoleUser]    Script Date: 03/16/2012 10:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoleUser](
    [RolesInternal_ID] [int] NOT NULL,
    [UserInternal_ID] [int] NOT NULL,
 CONSTRAINT [PK_RoleUser] PRIMARY KEY NONCLUSTERED
(
    [RolesInternal_ID] ASC,
    [UserInternal_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
/****** Object:  Table [dbo].[ResourceRole]    Script Date: 03/16/2012 10:43:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ResourceRole](
    [ResourceInternal_ID] [int] NOT NULL,
    [RolesInternal_ID] [int] NOT NULL,
 CONSTRAINT [PK_ResourceRole] PRIMARY KEY NONCLUSTERED
(
    [ResourceInternal_ID] ASC,
    [RolesInternal_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
 
/****** Object:  Default [DF_User_CurrentScore]    Script Date: 03/16/2012 10:43:39 ******/
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CurrentScore]  DEFAULT ((0)) FOR [CurrentScore]
GO
/****** Object:  ForeignKey [FK_RoleUser_Role]    Script Date: 03/16/2012 10:43:39 ******/
ALTER TABLE [dbo].[RoleUser]  WITH CHECK ADD  CONSTRAINT [FK_RoleUser_Role] FOREIGN KEY([RolesInternal_ID])
REFERENCES [dbo].[Role] ([ID])
GO
ALTER TABLE [dbo].[RoleUser] CHECK CONSTRAINT [FK_RoleUser_Role]
GO
/****** Object:  ForeignKey [FK_RoleUser_User]    Script Date: 03/16/2012 10:43:39 ******/
ALTER TABLE [dbo].[RoleUser]  WITH CHECK ADD  CONSTRAINT [FK_RoleUser_User] FOREIGN KEY([UserInternal_ID])
REFERENCES [dbo].[User] ([ID])
GO
ALTER TABLE [dbo].[RoleUser] CHECK CONSTRAINT [FK_RoleUser_User]
GO
/****** Object:  ForeignKey [FK_ResourceRole_Resource]    Script Date: 03/16/2012 10:43:39 ******/
ALTER TABLE [dbo].[ResourceRole]  WITH CHECK ADD  CONSTRAINT [FK_ResourceRole_Resource] FOREIGN KEY([ResourceInternal_ID])
REFERENCES [dbo].[Resource] ([ID])
GO
ALTER TABLE [dbo].[ResourceRole] CHECK CONSTRAINT [FK_ResourceRole_Resource]
GO
/****** Object:  ForeignKey [FK_ResourceRole_Role]    Script Date: 03/16/2012 10:43:39 ******/
ALTER TABLE [dbo].[ResourceRole]  WITH CHECK ADD  CONSTRAINT [FK_ResourceRole_Role] FOREIGN KEY([RolesInternal_ID])
REFERENCES [dbo].[Role] ([ID])
GO
ALTER TABLE [dbo].[ResourceRole] CHECK CONSTRAINT [FK_ResourceRole_Role]
GO

 判断权限的存储过程如下，判断方法同前面的c#，效率有所提升：

?
/****** Object:  StoredProcedure [dbo].[CheckUserAuthorization]    Script Date: 03/16/2012 10:43:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     
-- Create date: <2012/2/28>
-- Description: <-- 判断用户是否有权限访问>
-- =============================================
CREATE PROCEDURE [dbo].[CheckUserAuthorization]
    @ControllerName nvarchar(100) = 'Task',
    @ActionName nvarchar(100) = 'Index',
    @UserID INT = 1 --用户编号 匿名用户传入0
AS
BEGIN
DECLARE @ISAllowed bit = 0
DECLARE @IsAllowedNoneRoles INT = -1
DECLARE @IsAllowedAllRoles INT = -1
DECLARE @ID INT = -1
DECLARE @ResourceCount INT = 0
 
-- 查找有木有记录 先查找Action
SELECT @ID=ID,@IsAllowedNoneRoles=IsAllowedNoneRoles, @IsAllowedAllRoles=IsAllowedAllRoles FROM
dbo.Resource
WHERE ControllName = @ControllerName AND Name= @ActionName
 
IF @ID =-1
    BEGIN
        -- 木有Action 选择 Controll
        SELECT @ID=ID,@IsAllowedNoneRoles=IsAllowedNoneRoles, @IsAllowedAllRoles=IsAllowedAllRoles FROM
        dbo.Resource
        WHERE ControllName = @ControllerName
         
        -- 木有记录，均允许访问
        IF @ID =-1
            GOTO ALLOWED;
    END
 
-- 如果允许匿名访问或者允许所有人访问
IF @IsAllowedNoneRoles = 1
    GOTO ALLOWED;
     
-- 未登录用户，不允许访问
IF @UserID = 0
    GOTO NOTALLOWED;
     
-- 允许所有登录用户访问
IF @IsAllowedAllRoles = 1
    GOTO ALLOWED;
 
--查找用户的角色是否允许访问该资源
SELECT @ResourceCount = COUNT(*) FROM dbo.ResourceRole
WHERE RolesInternal_ID IN(
SELECT RolesInternal_ID FROM dbo.RoleUser WHERE dbo.RoleUser.UserInternal_ID = @UserID)
AND ResourceInternal_ID = @ID
     
-- 有结果，允许访问
IF(@ResourceCount>0)
    GOTO ALLOWED;;
 
-- 没有结果，不允许访问
    GOTO NOTALLOWED;
     
ALLOWED:
    SELECT 1 AS RESULT;
    GOTO THEEND;
     
NOTALLOWED:
    SELECT 0 AS RESULT;
    GOTO THEEND;
THEEND:
END
GO

　　

判断是否有访问权限需要执行CheckUserAuthorization存储过程，只需要传入控制器和Action名称以及用户ID即可，EF可以使用下面的方法调用：

?
public bool CheckUserAuth(string controllerName, string actionName, int userId)
        {
            EntityCommand cmd = ((EntityConnection)this.Connection).CreateCommand();
            if (cmd.Connection.State != System.Data.ConnectionState.Open)
                cmd.Connection.Open();
            cmd.CommandText = this.DefaultContainerName + ".CheckUserAuthorization";
            cmd.CommandType = CommandType.StoredProcedure;
 
            cmd.Parameters.AddWithValue("ControllerName", controllerName);
            cmd.Parameters.AddWithValue("ActionName", actionName);
            cmd.Parameters.AddWithValue("UserID", userId);
 
            EntityParameter ret = new EntityParameter("ReturnValue", DbType.Int32);
            ret.Direction = ParameterDirection.ReturnValue;
            ret.Value = -1;
            cmd.Parameters.Add(ret);
            var obj = (int)cmd.ExecuteScalar();
            cmd.Connection.Close();
            return (obj > 0);
        }

 

为了减少服务器压力，你可以在执行验证后将结果缓存起来，缓存时间自己设定

?
var controller = filterContext.RouteData.Values["controller"].ToString();
            var action = filterContext.RouteData.Values["action"].ToString();
 
            var isAllowed = this.IsAllowed(user, controller, action);
 
            if (!isAllowed)
            {
                filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new { controller = "Home", action = "LogOn", ReturnUrl = filterContext.HttpContext.Request.Url }));
            }

 

?
public bool IsAllowed(UserModel user, string controller, string action)
        {
            string key = (user != null ? user.UserId.ToString() : "null") + controller + action;
            var result = false;
 
            if (Cache.TryGet(key, out result))
            {
                return result;
            }
 
            result = IoC.Resolve().CheckUserAuthorization(controller, action, user != null ? user.UserId : 0);
 
            // 设置缓存  60分钟
            Cache.Set(key, result, TimeSpan.FromMinutes(60));
 
            // 默认禁止访问
            return result;
        }

关注作者

欢迎关注作者微信公众号, 一起交流软件开发：欢迎关注作者微信公众号

posted @   JadePeng  阅读(1677)  评论(2)  编辑  收藏  举报
编辑推荐：
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示
CONTENTS
✕