Restricting Record Access

Introduction

As we have it at the moment all users will be able to view every other users friends and relations, including their dates of birth.  This is definitely undesirable, possibly even illegal.  To avoid this possibility we must restrict the list of people to only those added by the logged in user.

Since we will restrict the list of people to those added by the logged in user the column showing to whom the email reminder should be sent is redundant (it will always just show the email of the currently logged in user).  We can therefore remove this column.

The list of people is currently displayed in the sequence in which the records were added.  It would probably be helpful to be able to sort the data, for example by name or birthday - but not necessarily by date of birth, although that could also be interesting.

YouTube Video

Summary of Tasks

  • Restrict list of people to currently logged in user
  • Remove Email column
  • Add column for next birthday
  • Add ability to sort

Restrict people to currently logged in user

SQL Stored Procedure

We will start this process by creating a SQL stored procedure that will take as a parameter the email of the currently logged in user and restrict the records returned to those with the same email address.  We already calculate 'next birthday' and 'age next birthday' in another stored procedure so we can steal the code for that and use it here as well.  Open SQL Management Studio and open a new query and paste in the following code to create the stored procedure.

USE [Birthdays]
GO
/****** Object:  StoredProcedure [dbo].[spPerson_GetAll]    Script Date: 24/01/2022 17:54:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE [dbo].[spPerson_GetByUser]
@PersonSendReminderTo nvarchar(100)
AS
BEGIN
--SQL for Select stored procedure.
SELECT PersonID, 
PersonFirstName, 
PersonLastName, 
PersonDateOfBirth, 
PersonSendReminderTo,
dbo.NextBirthday(PersonDateOfBirth) AS NextBirthday,
DATEDIFF(year, PersonDateOfBirth, dbo.NextBirthday(PersonDateOfBirth)) AS AgeNextBirthday
FROM Person 
WHERE PersonSendReminderTo = @PersonSendReminderTo
ORDER BY NextBirthday ASC
END

Run the script.  There is no need to save it.  Notice that I have changed the 'Order By' clause to return the data in NextBirthday sequence.

PersonService.cs

Add a new method to return people using the new stored procedure, passing to it the email address of the current user.  This is based on the PersonGetAll method modified to add the parameter to pass the user email.

// Get people based on logged in user email (SQL Select)
public async Task<IEnumerable<Person>> PersonGetByUser(string @PersonSendReminderTo)
{
    IEnumerable<Person> people;
    var parameters = new DynamicParameters();
    parameters.Add("@PersonSendReminderTo", PersonSendReminderTo, DbType.String);            
    using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
    {
        people = await conn.QueryAsync<Person>("spPerson_GetByUser", parameters, commandType: CommandType.StoredProcedure);
    }
    return people;
}

IPersonService.cs

Add the following line to include the new service in the interface.

Task<IEnumerable<Person>> PersonGetByUser(string @PersonSendReminderTo);

Index.razor

To modify Index.razor to use the PersonService method to restrict records to the logged in user, in the OnInitializedAsync replace the 'people = ...' line with the following:

people = await PersonService.PersonGetByUser(UserEmail);

There are a couple of other places where the 'people' object is refreshed, after adding, editing and deleting records.  These methods will also need to be modified to use the above. The places this will need to be changed are:

  • PersonSave (Add)
  • PersonSave (Edit)
  • PersonSave (Close Dialog)
  • ConfirmDelete

I suggest you save all files and run the application at this stage to check no errors have crept in.

Remove 'Send Reminder To' column

Open Index.razor and in the SfGrid section delete the code shown below:

<GridColumn Field="@nameof(Person.PersonSendReminderTo)"
            HeaderText="Send Reminder to:"
            TextAlign="@TextAlign.Left"
            Width="40">
</GridColumn>

Add Column for Next Birthday

Open Index.razor and copy and paste the html code for Date of Birth column and modify to take account of 'NextBirthday'.  I suggest placing this column as the first (leftmost) column, changing its width to 15 and having the text aligned to the left.

<GridColumn Field="@nameof(Person.NextBirthday)"
            HeaderText="Next Birthday"
            Format="d"
            Type="ColumnType.Date"
            TextAlign="@TextAlign.Left"
            Width="15">
</GridColumn>

Save all files and test.

Sorting

Adding sorting to the grid is very simple, just add 'AllowSorting="true"' to the html tag for SfGrid.

<SfGrid DataSource="@people"
            Toolbar="Toolbaritems"
            AllowSorting="true">

Clicking on a column heading will now sort, toggling between ascending and descending.

I don't anticipate that any one user would have hundreds of people in their list, but if that is likely to occur it would be possible to add a filter to the Syncfusion grid by adding 'AllowFiltering="true"' to the SfGrid tag.  (You can turn off filtering for individual columns by adding 'AllowFiltering="false"' to the the 'GridColumn' tag.)

Log out

Redirect to Azure B2C Login

Whilst testing I noticed that when logging out the user is directed to a generic page that just says 'Signed out', with no opportunity to sign back in.  After a bit of research I found the solution to this problem is to add the following code to Program.cs before the "var app = builder.Build();" line.

builder.Services.Configure<OpenIdConnectOptions>(OpenIdConnectDefaults.AuthenticationScheme, options =>
{
    options.Events.OnSignedOutCallbackRedirect = context =>
    {
        context.HttpContext.Response.Redirect(context.Options.SignedOutRedirectUri);
        context.HandleResponse();
        return global::System.Threading.Tasks.Task.CompletedTask;
    };

});

The solution to this problem was found on StackOverflow

References

Powered by Publii