Views and stored procedures - using both for DB security (Entity Framework 4)

Question

I'm a bit new to database programming in general, and even newer to Entity Framework 4. I'm reading the O'Reilly Press book on the subject, and just want to be sure I understand what the author is saying about using views in conjunction with stored procedures for an added layer of security. She says:

If you are reluctant to expose your database tables for querying, you don't have to. ... Views come into the model as entities, but because views are read-only, Entity Framework is not able to construct commands to persist data back to the database when you call SaveChanges. ... However, these entities still participate in change tracking just like any other entities (with a caveat about EntityKeys that I'll discuss momentarily). You can then map stored procedures to these view-based entities in order to persist data when SaveChanges is called. This gives you a complete round trip to query and update data without exposing your database tables.

I'm having some trouble understanding how this stored procedure mapping would work since views are read-only. Is she saying that the stored procedures would be mapped to the entities which are represented by the view, with the insert, update, and delete procedures mapped to the db and the select procedure mapped to the view?

Answer 1

This is a common requirement in hosted database environments. Basically, it works this way:

• you SELECT your data from views
• you use stored procedures for INSERT, UPDATE, DELETE statements

Entity Framework 4 nicely supports these requirements and makes it possible to create solutions that do not require any direct table access for your application (and its users). This can be a big plus in sensitive environments and industries.

You would basically "import" the views into your EF model. Each view would then create one entity (one class) in your model. For each of those entities, you can then map the INSERT, UPDATE, DELETE operations onto existing stored procedures in the database.

See the Deny Table Access to the Entity Framework Without Causing a Mutiny article in MSDN magazine for more in depth explanation of the ins and outs of this. It explains all the steps in great detail.