0

I've come across an issue when using Entity Framework and trying to delete an object.

The SQL-Server DB that it's trying to delete from, and the Login it is using, has a role membership of public only. If I check the db_datawriter box, the data is deleted as expected.

I'm completely new to SQL-Server admin, would enabling this option pose any security risk to our website?

  • The db_datawriter role cannot be disabled. But anyone you grant to have that permission can change any data in your database. So you would have to be very careful who got those rights. It is better to limit any access to only what is needed, but even better to encapsulate the code in stored procedures that are injection proof and single purpose. – RLF Feb 6 '15 at 18:59
2

If your application is required to change data (update, insert or delete) then the login it uses must have permissions to do so. There are many ways to implement that, and adding the login to the db_datawriter role is one but it may be too permissive (depending on the nature of the data).

You can define more granular permissions such as granting EXECUTE on specific stored procedures, or creating a separate schema for the tables, views or stored procedures used by the application and granting permissions on the schema. See the MSDN article on Application Security Scenarios in SQL Server for more detail.

4

Yes it would be a security risk. db_datawriter gives the account access to all tables in the database. This means that anyone getting control of the web user can delete all content.

A better solution would be to create an new role in the database. Assign the entity framework user to this role and only grant the bare minimum privileges needed to run the app to the role. Ideally, the Entity Framework would only read tables via GRANT SELECT (maybe even only some columns) and perform all writes via stored procedure with GRANT EXECUTE enabled.

An even better solution would be to not use Entity Framework at all. Like every other ORM on the planet - it is horrible way to access a database. Tell your programmers to learn SQL instead of fooling about with Object Oriented database access frameworks.

0

My preference for granting permissions is to use windows groups & database roles.

1) Create a database role.
2) Assign permissions to the database role on individual objects, schemas, etc, going down to the column-level if necessary.
3) create an active directory (AD) group
4) add AD users to the AD group
5) add the AD group as a member of the database role.
6) in the case where SQL logins are required, add to database role.

Don't grant permissions directly to individual user logins. Using database roles and AD groups does take a little more effort to setup but long run it will save time and its easier to audit & debug.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.