Restrict Orders by User

Introduction

It would be reasonable for an application such as the Purchase Order system to have a number of different roles, with the highest access roles having access to everything, and the lowest having access only to orders entered by themselves. There could also be an intermediate level that had access to all the purchase orders, but not to system tables such as Tax Rates (or possibly Suppliers and Products) for example.

We will now explore this type of scenario by introducing a 'Manager' role that has access to all purchase orders, and to the Suppliers and Products tables, but not the Tax Rate table. Users who are not in the Admin or Manager roles will only have access to purchase orders entered by them.

YouTube Video

Start by running the Identity Manager and adding a role called 'Manager'. Add a new user and assign them the Manager role.

We'll now grant access to the Supplier and Product menus by amending NavMenu so that the Tax Rates item is surrounded byand Suppliers and Products are surrounded by. However, we also need to add 'Manager' to the @attributes statement at the top of SupplierPage.razor and ProductPage.razor.

Run the application and check that the new user with the Manager role can access the Supplier and Product pages.

The page we want mainly affected is the Index page showing the purchase order list. If the user is not in the Admin or Manager roles only those orders for the logged in user should be displayed.

Currently, when the page is initialised a SQL stored procedure is run that returns a list of all purchase orders. The way I want to tackle this is to check in the C# code whether the user is in either the Admin or Manager role, if they are not in either role the user's login will be passed to the stored procedure as a parameter to be used in the 'where' clause. If they are in the Admin or Manager role, the parameter being passed to SQL will be null.

In summary, we will need to amend:

  • Index page to determine the user's role and pass the user's name to the stored procedure
  • The SQL stored procedure, to take into account the user's name (if present)
  • POHeaderService to handle passing the parameter to the SQL stored procedure
  • IPOHeaderService to handle the change to POHeaderService.

Index.razor

To pass the user's name (or null) to the stored procedure, these are the changes we need to make to Index.razor

At the top of the file add the following:

@inject AuthenticationStateProvider AuthenticationStateProvider

Somewhere at the top of the code section insert the following declaration:

    [CascadingParameter]
    private Task<AuthenticationState> authenticationStateTask { get; set; }

Create a new method called 'GetOrderList', as follows. (I am creating the new method because the purchase order list needs to be used in a number of places.)

protected async Task GetOrderList()
{
    var user = (await authenticationStateTask).User;

    if (user.IsInRole("Admin") || user.IsInRole("Manager"))
    {
        poheader = await POHeaderService.POHeaderList(null);   //leave user name blank
    }
    else
    {
        poheader = await POHeaderService.POHeaderList(user.Identity.Name); //pass user name
    }
}

The GetOrderList method gets details of the current user and if he/she is in the role of either Admin or Manager, it passes a null parameter to the POHeaderService.POHeaderList (and the SQL stored procedure), otherwise it passes their user name.

In the OnInitializedAsync method insert the following

await GetOrderList();

The above is also needed in the ConfirmOrderArchive method, to replace the existing poheader line.

SQL stored procedure

It's probably worth looking at the SQL stored procedure at this point. Currently the 'where' clause simply limits the records being returned to those that haven't been archived. ("WHERE POHeaderIsArchived = 0") We now need to extend the where clause to handle the situation where a user name is required to restrict the records returned, or the alternative of returning all the records.

The way I have decided to tackle this is to use a 'Case' statement, testing for the presence of a user name, or the alternative of a null. My modified 'Where' clause is shown below:

WHERE POHeaderRequestedBy Like CASE WHEN @UserName is null THEN '%' ELSE  @UserName END AND POHeaderIsArchived = 0

Where the parameter @UserName is null we want all records to be returned; to achieve this I am using the 'Like' and the wildcard '%'. (I found that I couldn't use '=' as it returned no records.) If @UserName is not null, the Where clause is effectively 'Where POHeaderRequestedBy like @UserName'.

I'm not sure if there would be a performance hit by using 'Like' and the wildcard; an alternative way of solving this problem would be to have two stored procedures, a simple listing where the user name is not used, and another, perhaps called 'ListByUser' that always has the user name passed as a parameter. My rather weak excuse for using one (admittedly, more complex) stored procedure is that it means only one stored procedure, service and interface needs to be maintained should anything change in the future.

POHeaderService

So that the user name can be passed to the SQL stored procedure, the POHeaderList method needs to be changed to the following:

public async Task<IEnumerable<POHeader>> POHeaderList(string @UserName)
{
    IEnumerable<POHeader> poheaders;
    var parameters = new DynamicParameters();
    parameters.Add("@UserName", UserName);
    using (var conn = new SqlConnection(_configuration.Value))
    {
        poheaders = await conn.QueryAsync<POHeader>("spPOHeader_List", parameters, commandType: CommandType.StoredProcedure);
    }
    return poheaders;
}

IPOHeaderService

Similarly, the interface file also needs to changed, replacing the existing with:

Task<IEnumerable<POHeader>> POHeaderList(string @UserName);

With these changes in place, run the application and log in as someone in the Admin role, Manager role and no role, enter a few orders and check the results. 

Project Code

The code for all the files changed in this post are shown here.

Powered by Publii