question

RonaldRex-2335 avatar image
0 Votes"
RonaldRex-2335 asked ErlandSommarskog commented

Identify logged in user

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 !!!!

sql-server-generaldotnet-entity-frameworkdotnet-aspnet-core-security
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Is this application made for patient self-service or for medical personnel?


0 Votes 0 ·

I guess you could say its for both. The medical personelle will enter the information. But the patient will be able to see their specific information once they log on. Thats why I want to be able to determine who is logged in so that I only issue query statements against that specific users information. Thank You !!!!

0 Votes 0 ·
AgaveJoe avatar image
0 Votes"
AgaveJoe answered Bruce-SqlWork commented

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.






· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That was GREAT. That was exactly what I wanted to know. Greatly Appreciate it

0 Votes 0 ·

I am using Token Authentication. I assume I can get the user Id using this security protocol.

0 Votes 0 ·

the user id will be a claim in token. generally you configure which claim is used to fill in the user.identity name.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ErlandSommarskog commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank You !!! Is there a valid concept of "logged in user" at the application layer?

0 Votes 0 ·

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.


I disagree with this. I know that it is common to use a service account, but in the system I work with users are logged into SQL Server as themselves, and this is something I am very happy for, as this means that SYSTEM_USER etc always return accurate information.

Sure, if you use a service account, you can convey the actual user by other means, but this requires extra calls to set that context.

Then again, our solution may more or less require IIS and SQL Server being on the same machine. This may not always be feasible, but it works for this application.

(Disclaimer: I'm an SQL Server guy, and I don't know anything about web programming.)

0 Votes 0 ·

user accounts work when users are given access to sqlserver. But often you don't want to give users access at all or only limited read access. this also means you need to write all update as stored procedure (not a bad practice).


0 Votes 0 ·

In the system I work with, users do not have access to SQL Server directly.

The reason for this is that we have executed these commands:

REVOKE CONNECT TO public ON ENDPOINT::[TSQL Default TCP]
REVOKE CONNECT TO public ON ENDPOINT::[TSQL Named Pipes]

The only endpoint they cannot on is TSQL Local Machine, and they can only access this endpoint through the web server.

Would the web server be running on a different server, this plot would not work out, but I believe it is possible to set up a NIC that only accepts connection from a certain address, which would be the web server. Then you have a specific endpoint for that NIC.

0 Votes 0 ·