MVC permission control continued-use stored procedure judgment

The previous documents did not provide a database. Here we provide a specific database and how to use the stored procedure to determine

Refer:

Asp.net MVC permission design (continued)

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

Below is the database:

/***** Object: Table [DBO]. [Resource] script Date: 03/16/2012 10:43:39 ******/set ansi_nulls ongoset quoted_identifier ongocreate 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, expiration = 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 ongoset quoted_identifier ongocreate 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 ongoset quoted_identifier ongocreate 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 ongoset quoted_identifier ongocreate 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 ongoset quoted_identifier ongocreate 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]) goalter 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]) goalter 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]) goalter 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]) goalter table [DBO]. [resourcerole] Check constraint [fk_resourcerole_role] Go

the Stored Procedure for permission determination is as follows. The judgment method is the same as the previous C #, which improves the efficiency:

/***** Object: storedprocedure [DBO]. [checkuserauthorization] script Date: 03/16/2012 10:43:34 ******/set ansi_nulls ongoset quoted_identifier ongo -- ================ =================================-- Author: -- create Date: <2012/2/28> -- Description: <-- determine whether a user has access permissions> -- ============================ ================== create procedure [DBO]. [checkuserauthorization] @ controllername nvarchar (100) = 'task', @ actionname nvarchar (100) = 'index ', @ userid Int = 1 -- user number anonymous user input 0 asbegindeclare @ isallowed bit = 0 declare @ isallowednoneroles Int =-1 declare @ isallowedallroles Int =-1 declare @ ID Int = @ resourcecount Int = 0 -- search for records with wood records first search for actionselect @ ID = ID, @ isallowednoneroles = isallowednoneroles, @ isallowedallroles = isallowedallroles fromdbo. resourcewhere controllname = @ controllername and name = @ actionnameif @ ID =-1begin -- select controllselect @ ID = ID, @ role = role, @ isallowedallroles = isallowedallroles fromdbo for an action. resourcewhere controllname = @ controllername -- If @ ID =-1 goto allowed; end -- if anonymous access is allowed or if @ isallowednoneroles = 1 goto allowed is allowed; -- If @ userid = 0 goto notallowed; -- allow all login users to access if @ isallowedallroles = 1 goto allowed; -- check whether the user's role allows access to the resource. Select @ resourcecount = count (*) from DBO. resourcerolewhere rolesinternal_id in (select rolesinternal_id from DBO. roleuser where DBO. roleuser. userinternal_id = @ userid) and resourceinternal_id = @ ID -- with results, if (@ resourcecount> 0) goto allowed; -- no results, access to goto notallowed; allowed: select 1 as result; goto theend; notallowed: Select 0 as result; goto theend; theend: endgo

To determine whether you have access permissions, You need to execute the checkuserauthorization stored procedure. You only need to pass in the controller, action name, and user ID. EF can be called using the following method:

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

 

To reduce the load on the server, you can cache the results after the verification is executed and set the cache time.

 
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); // sets the cache for 60 minutes. set (key, result, timespan. fromminutes (60); // access to return result is prohibited by default;}