0

I'm considering converting my project from using Sprox to linq (EF6), however I'm a bit concerned about DB permissions. At the moment I'm assigning explicitly rights to each and every procedure and nothing beyond that. How can I have a similar level of security with the use of dynamically created queries in EntityFramework?

Dave
  • 349
  • 1
  • 15
  • Why do you want to move your sql out of SPs? EF can use SPs as well has dynamic sql. – juharr Oct 23 '14 at 11:28
  • I want to increase the development speed and to move away from writing unit tests in TSQL but stay with c#. For more demanding SQL operations I'd keep stored procedures and execute them either with EF or pure ADO – Dave Oct 23 '14 at 11:37
  • @Dave are you ok with doing security on the repository / service layer of your application instead of doing it with the database? I don't think you can do it on the database with Entity framework without having it call stored procs or otherwise have "static" queries. Are the permissions user specific or more like you can read this but not write to it? – John Oct 23 '14 at 16:38

1 Answers1

0

You can set individual permissions up using the GRANT command in SQL.

e.g.

The following example grants SELECT permission to user RosaQdM on table Person.Address

GRANT SELECT ON OBJECT::Person.Address TO RosaQdM;

EF will have to respect the permissions granted, so whichever user that you are using in your application's connection string is the one you need to assign the appropriate permissions to.

SilverlightFox
  • 32,436
  • 11
  • 76
  • 145