Single Connection String with different user permissions in Azure App Service

Question

I have a web app in azure.

My solution is something below:

1. MyApp.Web
2. MyApp.WebjobTasks
3. MyApp.WebjobAnotherTask

The webjobs are part of the web application. They all share the same Data layer to access data by Entity Framework and in Azure portal when App settings are defined the app settings are overrides in all projects web app and web jobs config files.

All projects has a connection string key as "DefaultConnection" for to the same database.

But I would like to change the user (that has more privileges to do housekeeping) for webjobs. So Webjob should be able to ALTER/Truncate tables whereas MyApp.Web should only have the db_datareader and _db_datawriter roles.

How can I diverse this? If I change the connection string key in Azure portal to use a user that has permission to alter database then this is not a good solution that public faced application will be given unnecessary permissions.

Wouldn't be nice that each application should have their own App Settings page in Azure portal. So that each application could be run different user permissions.

Is there any better way to achieve this? Or I should be adding new user and password keys to app.config for webjobs and read those keys to update connection string before running house keeping jobs?

Answer 1

I advise against changing connection string on the fly. This might create race conditions.

I would suggest adding additional connection strings for those web jobs if they have different responsibilities. This however may require extra logic for your DbContext creation if you are not currently injecting the connection string.

With different connection strings it is easier to migrate web job to another place (ie. Azure Functions or whatever) if needed and your web jobs are restricted from doing db operations they are not allowed do, like creating users if job is only designed to clean audit log.