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;
}
