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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

2706
来自专栏沃趣科技

SQL优化案例-从执行计划定位SQL问题(三)

当SQL出现问题,能从执行计划中快速的定位哪部分出现问题很重要,SQL文本如下(为保证客户隐私,已经将注释和文字部分去掉):

1456
来自专栏杨建荣的学习笔记

使用dbms_metadata生成建表语句(r2笔记97天)

有时候在工作中,可以使用exp/imp得到表的创建语句。 如果想得到关于table,index,constraint的语句,可以考虑使用dbms_metadat...

2233
来自专栏文渊之博

DATETIME类型和BIGINT 类型互相转换

项目中使用BIGINT来存放时间,以下代码用来转换时间类型和BIGINT类型 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIE...

1738
来自专栏杨建荣的学习笔记

生产系统调优之_敢于质疑(90天)

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后,速度有了一定的提升,从5秒的响应降低到了2秒。但是和预期还是有一定 的差距,按照80条查询请求在短...

2467
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

511
来自专栏xiaoheike

为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中...

814
来自专栏杨建荣的学习笔记

很多人比较纠结的约束和索引的关系(r7笔记第75天)

最近有不少朋友公众号留言或者微信私信问我一个问题,出乎我的意料,问题竟然都是很相似的,所以我统一答复一下。 之前写了一篇文章 一个清理和查询都要兼顾的简单方案,...

3346
来自专栏跟着阿笨一起玩NET

Sql Server 存储过程使用技巧

Copy下面的代码,然后新建查询,就可以写sql语句,执行完后,一个你自己的存储过程就建立好了!

481
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第三篇(r2笔记38天)

生产环境有一条sql语句执行比较频繁,占用了大量的cpu资源。原本执行需要花费11秒。在一次排查中引起了我的注意,决定看看cpu消耗到底在哪儿? sql语句是比...

2644

扫码关注云+社区