Hi, I have one reservation about jumping into LINQ for an enterprise application: Security.
It seems (and correct me if im wrong) that to be able to use DLinq to perform insert/updates on the database, you would have to allow the database user you connect as, to have insert/update permissions on the tables in the database. This is not considered good practise at the company I work for, where we only give the user execute permissions for stored procedures (Obviously to prevent attacks like SQL Injection).
I know one way around this is to override the default insert/update actions in DLinq to use stored procedures, but this takes away the advantage of using it in the first place as there is a large amount of custom code that needs to be written as well as the queries.
As I said, im not quite sure how the queries are sent to SQL Server, but im assuming they are just plain text queries, not pre-compiled into stored prodecures or anything.
I really want to be able to use LINQ because I can see the potential productivity benefit, but this could be a show stopper! Let me know if there is a way around this.
Cheers.
LINQ and SQL Server Permissions
Peppermint
Hi,
Yep, "clear text" is passed to sql server.
But I think I have a solution for you. You'll have to generate all that stored procedure wrappers as you know. But, here is the surprise, you might automatically generate them using a template based code generator such as CodeSmith (which I highly recommend). Basically you would have to create a template and run codesmith to generated the actual code.
David Springate
With DLINQ, you have several options. One option is to use views to take care of your horizontal partitioning and security. I realize that may not be optimal. Additionally, you can use stored procedures. In your custom partial class for each entity, implement methods for your Inserts, Updates and Deletes. For each method, add the appropriate attribute [InsertMethod], [UpdateMethod] and [DeleteMethod] respectively. The methods with the attribute will be called in place of the auto-generated procedure. See section 4.5 of the DLINQ overview document that comes with the CTP for samples.
If you are using the ASP2.0 binding sources, you likely already have the methods in place for your custom objects. You will need to do the mapping yourself rather than rely on DLINQ to do it for you at this point.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
Sly
I am unclear why you think having DLINQ generate stored procedures rather than parameterized queries is any more secure. Consider the fact that SQL requires elevated permissions in order to create procedure and to grant access to execute that procedure. If you allow all of your users the ability to do this you would have the following side effects:
Stored procedures are good for:
Since the SQL created by DLINQ uses parameterized queries, it already gives you #4 and at least some of #3. You don't want all users having the ability to do #2, but setting table/view based permissions may be sufficient depending on your business needs. Some would argue that #1 would be best placed in your business tier depending on the functionality needed. Thus I would argue that the DLINQ is more secure than your proposed dynamic stored procedure option.
Consider this my anti-suggestion for the box.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
C# nb
Ok thanks for confirming my suspicions guys.
I wonder if there could be a future enhancement in LINQ to be able to generate stored procedures from the DLINQ code you create (and automatically apply them to the database
). At the moment it is nearly doing this anyway, its already generating the SQL, why wouldn't it be able to be configured to write that sql to a script and execute it
This would be very beneficial as the amount of custom code that would need to be written would be minimal again (just as if you were not using the stored procedures).
Anyway know where the 'suggestions box' is
Cheers!