Identify logged in user

Ronald Rex 1,666 Reputation points
2021-10-22T17:42:34.3+00:00

I am working on a medical patient management system web application. I am thinking I should use the user credentials of the logged in user to access the various tables i have with the respective patient information I have stored in various tables. I was wondering what is the best practice for which value that represents the logged in user should i use to create relationships with the tables where the patients health records are stored. For example, I have a table for medications, patient visits, and documents the patient has signed. If I can reiterate, what is the best practice for creating a relationship between the logged in user and the tables that store this particular users health information? Thank You !!!!

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,362 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,154 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,682 questions
Accepted answer
  1. AgaveJoe 26,191 Reputation points
    2021-10-22T22:44:16.147+00:00

    I assume this is a browser based application. The standard is cookie authentication. The user data (claims) are cached in the cookie. The cookie authentication middleware reads the cookie content on each request and creates a user principal which drives the standard Core security for the current request.

    To get the user is simply... 

    User.Identity.Name

    Use cookie authentication without ASP.NET Core Identity

    If I can reiterate, what is the best practice for creating a relationship between the logged in user and the tables that store this particular users health information?

    You have not explained how the security works so it is hard to provide an accurate answer. Usually the user's Id is used to identify user records. Each record that belongs to the user will contain the user's Id. When the user is logged, they can only see records that belong to them. The query is simple, fetch all the records where the Id = the user's Id.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-22T18:03:59.857+00:00

    Best practice is you don't use individual logins to access the SQL Server database. You setup a single "service" account which the application uses for ALL web users to access the database. So there isn't a valid concept of "logged in user" at the database layer.

    If you want to store information about who created/edited a record, you do that by adding a column to the table and storing the WEB username (or key), like ModifiedBy, and update it via the application when inserting or modifying a record.