0

I've looked extensively for a duplicate question, but I couldn't find anything that answers this question exactly.

I have a SQL Server that will be used to store data for a multi-tenant application. Every tenant will have their own database, and there will be a central database for managing logins, authentication, etc.

When the user logs in they're authenticating against the central database using an application user. Let's call that "WebSiteUser". The login credentials for WebSiteUser are stored in the Web.Config of the WebApp and is encrypted.

At this point, what user should I use for accessing the tenant's specific database? I perpetually think about what would happen if a hacker was able to do things they shouldn't be able to do, like changing what database the app is pointing at. While I cannot even conceive how they could do that, I don't want to risk confidential information being leaked because of some security flaw that wasn't thought of. I can think of a few options but I'm not sure what option is correct:

  1. I could use the WebSiteUser that the application is already using to access the central database. But I don't like this idea since if a hacker could change what database the app is looking at, they now have access to both the authentication information(passwords are hashed appropriately, but still) and all other tenants' information.

  2. I could create another user that all tenants would use to connect to their specific database. I don't like this idea since now a single database user could connect to every single tenant's database. So if a hacker could somehow trick my app into pointing at a different database, it'll now do that without any issue. However, this is better than option 1 since at least the authentication data is still secure.

  3. I could have a specific user for each database, and after logging in the authentication token is somehow used to set the connection information for the correct database. This way every database uses a unique login. I think a solution similar to this(where every database has its own user) is by far the best option, but I have absolutely no idea how to securely store and then dynamically retrieve the login information for that tenant's database.

What is the correct way to configure database security for a multi-tenant server?

NicholaiRen
  • 1
  • "When the user logs in they're authenticating against the central database using an application user. Let's call that "WebSiteUser". The login credentials for WebSiteUser are stored in the Web.Config" - you mean as in the application user lies in the DB, but the credentials used for the connection string are inside the `web.config`? – Beltway Dec 01 '21 at 07:02
  • @Beltway No, sorry. What I mean is that there's a sql server user configured and mapped over to a role on that database. That user's credentials are stored in the web config, and the API uses that to connect to the database. – NicholaiRen Dec 01 '21 at 13:24

0 Answers0