jade 内存权限_mvc 权限控制续——使用存储过程判断

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

参考：

下面是数据库：

/****** 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;

}