CODEWITHSUNDEEP
sql-server-2008entity-framework
gunnerz
gunnerz

Reputation: 1926

EF or Stored procedure security

I am trying to convince my team to use an ORM layer like entity framework, instead of using the old fashioned way of creating data tables from stored procedures.

They are of the opinion that stored procedures are more secure, because you can ensure that only way to modify the database is through the stored procedure. We own the database, and application can be built either in-house or by third party.

The fear is that if we use Entity framework we will have to relax the database security, the third party might start writing code to directly modify the database.

Can you tell if we can control the security of the database, such that database can be modified only by EF, and not directly. Or if we can control EF such that EF can do only selects, but not inserts or deletes.

Sorry if I am sounding a bit ambiguous, but I am not very good at database security aspects. We are using SQL server 2008.

Thanks

Upvotes: 1

Views: 395

Answers (3)

gunnerz
gunnerz

Reputation: 1926

Found a useful link:

http://msdn.microsoft.com/en-us/magazine/ff898427.aspx

Upvotes: 0

usr
usr

Reputation: 171178

No, you can't make an untrusted application trusted. The application is just sending SQL strings to the database, even when using EF. SQL Server does not know anything about EF. It cannot make sure EF was used.

I'd solve this with code reviews and database permissions.

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

Database security still remains in database. You can still control if the user can only select data or execute any other operation. That is something unrelated to EF.

With EF you can even use your current model - you can map stored procedures (allowed only with EDMX) for update, insert and delete operations per entity and allow only selecting data and executing those mapped stored procedures. If you need additional level of security you can even map database views to EF and use mapped stored procedures for inserting, deleting and updating without allowing direct access to database tables.

Btw. database security can be controlled per user / role connecting to database so your application can have different permissions than third party application.

Upvotes: 2

Related Questions