0

I am building a web application to store sensitive medical information into a database with TDE on. However my users are in 3 categories:

  1. The medics who have access everywhere

  2. Management who can only see certain pages and access certain tables/views

  3. I.T. guys who will login and need to access a different database in the SQL Server to load up some dummy data so they can do their troubleshooting.

The first thing that came to mind was to create a user management system. I created one by asking the users to enter their own private password I made for each of them, but I am not sure what I should protect. So what I tried to do is to encrypt the SQL connection string using AES and the password I have provided each medic, and saved the encrypted string into a table in a different database.

When they launch the application, it will ask for their password which in turn will decrypt the corresponding connection string, store it in a cookie and protect it using Machine.Protect() found in asp.net 4.5

This way, I can use page permissions for allowing only the medics to access certain pages with certain reports. Allow management to see their own pages and thirdly when the I.T. guy goes in, he will have his own key that will decrypt a a different connection string to connect to a dummy database so he can do his troubleshooting.

Is this a safe way or can this be exploited easily? I would love to have any feedback on this.

All code calls to the SQL server is in a single class library dll, language used: asp.net and c#, we are using Active Directory to limit access in the first place.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
i_shoot_photos
  • 166
  • 2
  • 15
  • 1
    Don't encrypt the connection string and store it in a cookie. That is not a good idea. Instead you should authenticate your user when they login (against your table of user IDs/hashed passwords) and issue a session token as a cookie. The token will have an expiration date. This token will be sent by the browser for all requests, and you can lookup the token in your database to check if it's valid, who the user ID is etc. Then pass the user ID to your Data Access Layer. Have the DAL check the users permission based on their user ID/category. – Jon Jan 31 '17 at 16:44
  • @Mangist Thanks im looking into this but im still not sure how to create a second query string for the IT group of users for example. Will that be managed in the DAL? – i_shoot_photos Jan 31 '17 at 17:59
  • 1
    In the Users table, you should have a UserType (1, 2 or 3). Then your data access layer class should check this UserType and when it creates the SqlConnection, if UserType == 3 user your Test Database, otherwise use the Production Database. – Jon Jan 31 '17 at 18:00
  • 1
    Also, check this out. There are lots of examples of custom authentication for ASP.NET websites using your own User table http://stackoverflow.com/questions/20529401/how-to-customize-authentication-to-my-own-set-of-tables-in-asp-net-web-api-2 – Jon Jan 31 '17 at 18:01
  • This is great thanks a lot I will read up on this – i_shoot_photos Jan 31 '17 at 18:02

0 Answers0