ASP.NET/IIS7 - Securing access to SQL Server for multiple user roles

Question

I'm working on an web application using ASP.NET 4.0, C#, and IIS7. The web application is a content management system that defines multiple user roles, such as editor and administrator. These users and roles are managed by the asp.net membership framework, and the associated database tables are integrated into the web app's database using aspnet_regsql. Finally, the web app is running under the ApplicationPoolIdentity. Thus, the web app runs under the virtual account "IIS AppPool\" which it does not share with any other application.

The site is designed such that user accounts are handed out by the administrator (there is no public sign-up page), although this detail may be irrelevant. In any case, the administrator should have the power to create and delete users and edit any of the content on the site. Editors, on the other hand, should be capable of editing only assigned sections of the site. Finally, anonymous visitors to the site should only be capable of viewing the content, with no option to edit.

The question is: Would it be insecure to just give read and write access in the SQL Server database to the IIS AppPool\ virtual account and give functionality to different user roles in the underlying business logic for the web application?

I wouldn't think so, but due to the necessity of the integrity of the data, I thought it might be a good idea to seek the opinion of another developer.

If (and only if) this does pose an unforeseen security risk, would it be a better idea to use impersonation, store multiple connection strings in the web.config file with SQL authentication, or track user privileges in the database itself?

Answer 1

The question is: Would it be insecure to just give read and write access in the MSSQL database to the IIS AppPool\ virtual account and give functionality to different user roles in the underlying business logic for the web application?

This is how it's usually done, and for most business cases this is enough. There are insecurities in every application so you have to do the best you can to avoid buffer overflows, script injections and SQL injections, scrub your input, etc.

If (and only if) this does pose an unforeseen security risk, would it be a better idea to use impersonation, store multiple connection strings in the web.config file with SQL authentication, or track user privileges in the database itself?

Using impersonation is not uncommon, and very easy if you're using Windows Authentication. It's an administration headache, since users have to be added via database security in addition to the application database. Multiple connection strings is probably the least extensible and favorable of the approaches, not to mention it would hurt performance on a busy site.