Authorize user from database in c#

Question

I am working on a website in asp.net(C#) . I want to authorize every logged in user (restrict to open any web page which user has no rights). I have 2 tables users and roles in Sql Server from where I want to get role & then authorize that user.

Answer 1

Since, users and roles have a many to many relationship, there would be another junction table between users and roles. Let's say it would be UserRoles

So, now on any request you will design a global filter/ handler which will do the following:

var user = Session["UserName"]; // getting logged in user
var role = // query that junction table **UserRoles** to get all the roles this current user has

Answer 2

If you want to validate user access using a stored procedure, you will need the following:

1) extra table to store roles for users - let's call it UserXRole

2) a stored procedure or a user defined function to get user roles or check if a user has a certain role. Something like the following:

CREATE PROCEDURE dbo.UserHasRole
(
    @UserId INT,
    -- if you provide role name or code, an extra join with dbo.Role is required
    @RoleId INT
)
AS
BEGIN
    DECLARE @result BIT = (CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM dbo.UserXRole X
            WHERE X.UserId = @UserId AND X.RoleId = @RoleId) THEN 1 
        ELSE 0)

    RETURN @result        
END

The code obeys user defined functions restrictions, so you can change it to a function that returns BIT. If you suspect that authentication logic might get complicate in the future, leave it as stored procedure.

3) Call the procedure using current logged user identifier

Whenever you validate user authentication, I expect to place some information about logged user into the Session object.

Checking that the user must have a certain role in order to do something means calling the stored procedure with current user identifier (e.g. Session["UserId"]) and required role (RoleId).

Above approach should work, but it might lead to many calls to your database. A better approach would be:

1) Cache all user roles on authentication - when user is authenticated, stored all his/her roles in a list within the Session. E.g.:

Session["CurrentUserRoles"] = rolesFromDb.Select(r => r.RoleId).ToList();

2) Checking if a user has a role is done in memory only. E.g.:

bool hasSomeRole = (Session["CurrentUserRoles"] as IList<int>)?.Contains(someRoleId) ?? false;

As it is, the question is quite broad. Try one of the suggested solutions and come back with more targeted questions when you get stuck.