LINQ and Database Permissions

Question

I'm still trying to get my head around LINQ and accessing a SQL Database.

I was always taught that you should only have execute permissions of stored procedures to your data.

You should never have select / insert / update / delete.

(This is because of performance and security)

To get the data out of LINQ you obviously need select permissions. I know you can use stored procs with LINQ, but since I can't do joins what's the point?

Have I missed something???

Answer 1

1) We're programmers, not DBA fascists. If your requirements state that the database must be locked down 100%, Linq is not for you. I'm not a DBA, so I believe that most of the performance/security hype is just that. Linq is for me.

2) You can do joins with linq.

@Philippe: Linq automatically transforms evaluations into query parameters, so it provides some sql injection protection. However, you still have to closely evaluate your requirements to determine how much security you need and at what levels. Linq makes dealing with the database much easier, but it makes it easier to put secuirty design on the back burner, which is a bad thing.

Answer 2

I'm very much in agreement with Jeff Atwood on the "Stored Procedures vs. Inline SQL/LINQ" issue: Who Needs Stored Procedures, Anyways?.

I'm confused as to why you'd even want to perform a JOIN if you're in the SPROCs-for-everything crowd; shouldn't you wrap that JOIN up into another SPROC?

As Will said, LINQ wasn't designed for the kind of DB use you're talking about; it was designed to give us statically-typed inline SQL. You could, however, still control access through user permissions if you use LINQ to SQL.

Answer 3

Well, for security reasons you should not input any user entered data into queries. If you stick with this rule, I don't see the problem of having select permission.

Answer 4

Whether all of your database access is "behind" stored procedures depends on the needs of the application and the company. I have implemented systems that use views to get all data and stored procedures for all updates. This allows for centralized security and database logic while still letting front-end developers use SQL queries where appropriate.

Like so many other things in programming - it depends on the needs for your project.

LinqToSql does support stored procedures. Scott Gu has a post on it:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx