Hilton Giesenow's Jumbled Mind

In the previous post, we discussed the first topic - performance considerations in LINQ To SQL - in some detail. In this post we will continue this evaluation by looking into the topic of security in terms of this new OR/M. As before, we will break down the discussion into a number of specific sub topics.

A note before we begin: Security is a critical topic, as we're all used to hearing about. However, recall that our focus is on LINQ To SQL, and the impact it may or may not have on security, specifically in an ASP.Net context. While the topics are important, we will not discuss topics like Cross Site Scripting, Cross Site Request Forgery, AJAX Security (the last link, for those who're interested, is to my DevDays 2007 slide deck on ASP.NET AJAX Security). If you're interested in those topics, there are some great links available (1) (2). In terms of LINQ to SQL security though, it's only fair to compare apples with apples, so to speak, so we need to bare in mind we're dealing only with data access layer security.

Stored Procedures

We had quite an intense look into performance related issues around Stored Procedures in the last post because LINQ To SQL favours inline (generated) SQL instead. Recall that LINQ To SQL can easily make use of stored procedures for queries and for updates.. Incidentally, one point I intended to mention on the topic is that, in terms of the argument that DBAs can tweak sprocs to improve DB performance, the truth is that arguably the primary and most common gain in performance that a DBA does most regularly relates to maintaining and improving indexing on the database, not managing CRUD sprocs. Sprocs are coming up here again because probably the second most common argument in favour of sprocs is the supposed security they provide. As Jeff Atwood puts it (in a blog post NOT in favour of stored procs, he's just putting forward the arguments we're all used to hearing):

Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.

This is one of the two primary security arguments put forward in favour of sprocs. The other relates to parameterised SQL queries (effectively overcoming a SQL injection attack). However, without rehashing another old topic too much, this is completely overcome (from a database CRUD perspective at least) with simply using parameterised queries, so I'm not even going to give this point any more airtime.

Back to the "individually securing areas of the database" with sprocs argument. Now, in my mind this is more theoretical because I find in practice it holds little water. On the one hand, I quite commonly will hear clients espouse this concern as a reason to use sprocs, then simply go and use an 'sa' or equivalent account for the data access anyway! In addition, the majority of the systems I've seen with sprocs follow something like the following. The have a table (e.g. Customers), then have at least the following sprocs, following a very strictly defined naming convention:

• spCustomer_Insert
• spCustomer_Update
• spCustomer_Delete
• spCustomer_GetByID
• spCustomer_GetAll
• spCustomer_Get... (numerous of these)

Now, if an attacker was able to get hold of the text of your inline SQL calls, and from there possibly derive your schema and wreak havoc, they could just as easily then see the name or names of these sprocs and quickly derive your common naming convention! This will pretty much give him the same access if he would have had anyway! If you're concerned about internal security (i.e. developers not being able to access the tables directly) the situation is even worse for the same reason. I've been there working on an emergency late at night with NO TABLE ACCESS because we were "NOT allowed to be modifying the database without approval" and I simply called the correct sprocs in the correct order (don't worry, I had approval, we just couldn't get hold of the DBA). If you are legitimately worried about specific operations on specific tables (which you should be), you can just as easily create an account and set the specific CRUD rights on the table. With proper user schema separation in SQL 2005 this allows you to lock down to specific rows and specific column-level security, really giving the granularity that was argued from a sproc perspective.

SQL Connection

Once again, this is not a LINQ To SQL-specific issue, but please remember to use a secure database connection string, regardless of your data access technique. If you're not sure how easily it is to do this, you can check out my How Do I: Encrypt My Web.Config File? video on MSDN. Again, please take this as a very important point. It's so important, in fact, that it is (laughably) the only point that Microsoft lists in the MSDN library under Security in LINQ to SQL. To quote 50% of the "article":

Security risks are always present when you connect to a database, especially if the password is clearly readable in the connection string.

Application Architecture Security Concerns

As I've mentioned a few times above, the issues we've discussed above under "LINQ To SQL" security have really been around in various data access approaches and OR/Ms. Arguably, we now come to something more unique to LINQ To SQL. The nature of how LINQ To SQL implements its classes and the datacontext, because of how the validation works and also because they are your business classes, means that you are forced to mix certain aspects of your layering more closely than might otherwise be the case. For instance, if you look at the Product class generated by the LINQ To SQL Designer, you will find the following code shortly after the class definition:

#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnProductIDChanging(int value);
partial void OnProductIDChanged();
partial void OnProductNameChanging(string value);
partial void OnProductNameChanged();
partial void OnSupplierIDChanging(System.Nullable<int> value);
partial void OnSupplierIDChanged();
partial void OnCategoryIDChanging(System.Nullable<int> value);
partial void OnCategoryIDChanged();
partial void OnQuantityPerUnitChanging(string value);
partial void OnQuantityPerUnitChanged();
partial void OnUnitPriceChanging(System.Nullable<decimal> value);
partial void OnUnitPriceChanged();
partial void OnUnitsInStockChanging(System.Nullable<short> value);
partial void OnUnitsInStockChanged();
partial void OnUnitsOnOrderChanging(System.Nullable<short> value);
partial void OnUnitsOnOrderChanged();
partial void OnReorderLevelChanging(System.Nullable<short> value);
partial void OnReorderLevelChanged();
partial void OnDiscontinuedChanging(bool value);
partial void OnDiscontinuedChanged();
#endregion

#region Extensibility GetHealthy Definitions
partial void OnCreated();
partial void InsertCategory(Category instance);
partial void UpdateCategory(Category instance);
partial void DeleteCategory(Category instance);
partial void InsertProduct(Product instance);
partial void UpdateProduct(Product instance);
partial void DeleteProduct(Product instance);
partial void InsertCustomer(Customer instance);
partial void UpdateCustomer(Customer instance);
partial void DeleteCustomer(Customer instance);
partial void InsertOrder(Order instance);
partial void UpdateOrder(Order instance);
partial void DeleteOrder(Order instance);
partial void InsertOrder_Detail(Order_Detail instance);
partial void UpdateOrder_Detail(Order_Detail instance);
partial void DeleteOrder_Detail(Order_Detail instance);
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WhyLinQ
{
    public partial class Product
    {

        partial void OnValidate(System.Data.Linq.ChangeAction action)
        {
            //do some validation logic here
        }

    }
}

This is turn means that the validation code, which is part of the business logic, needs to sit in the same layer as the database access code. From a security perspective, this limits how these two concerns can be physically separated. In my experience, separating the business and data layers is rarely implemented, certainly not for security reasons. It is far more common to split the business and UI layers, for instance behind a service, so this concern is perhaps not a big one.

With that we've covered the 2nd primary concern in evaluating LINQ To SQL. Next up, and time-permitting, I'll be looking into what concerns we had relating to developer productivity and effort, both in terms of using LINQ as well as in terms of the ramp up it requires.