Alex Rivera | Logout

Best practice on users/roles on SQL Server for a web application

Asked 2009-09-08T17:38:06.937
45

I searched online a bit and couldn't find anything that really nailed the spot or covered the bases how to go about setting up users/roles on a database.

Basically, there would be a user that would be used to access the database from the application (web application in this case) that will need access to database for the regular database operations (select, insert, update, delete) and executing stored procedures (with exec to run stored procedures within other stored procedures/UDFs).

Then, we would also have a user that would be main admin (this is simple enough).

I currently have a development environment where we don't really manage the security too well in my opinion (application uses a user with db_owner role, though it is an intranet application). Even though it is an intranet application, we still have security in mind and would like to see what are some of the ways developers set up the users/roles for this type of environment.

EDIT: Web application and SQL Server reside on separate machines.

EDIT: Forgot to mention that an ORM is used that would need direct read/write access.

Question: What are the "best practices" on setting up the user for application access? What roles would apply and what are some of the catches?

sql sql-server security
Edit
Report

1 Answer

14

For a long time the SQL Server guidelines for application access to the database were to isolate access to data into stored procedures, group procedures into a schema and grant execute on the schema to the principal used by the application. Ownership chaining would guarantee data access to the procedure callers. The access can be reviewed by inspecting the stored procedures. This is a simple model, easy to understand, design, deploy and manage. Use of stored procedure can leverage code signing, the most granular and powerfull access control method, and the only one that is tamper evident (signature is lost if procedure is altered).

The problem is that every bit of technology comming out from the Visual Studio designers flies in the face of this recommendation. Developers are presented with models that are just hard to use exclusively with stored procedures. Developers love to design their class models first and generate the table structure from the logical model. The procedure based guidelines reuire the procedures to exists first, before the first line of the application is written, and this is actually problematic in development due to the iterative way of modern development. This is not unsolvable, as long as the team leadership is aware of the issue and addresses it (ie. have the procedures ready, even as mocks, when the dev cycle starts).

answered 2009-09-08T18:33:37.480

Your Answer