Data access moves on - LINQ for SQL and other technologies such as Hibernate can allow data access logic to be moved out of the database and into the application tier. Those of us with a dba background may recoil in horror, but I'm starting to accept that we have a new paradigm here, and we have to adjust.

There are mixed feelings – how can database access be tuned if the queries are taken away from the database? I don’t mean execution plans here (DLINQ is quite efficient in that respect), but rather implementation of proper indexes. If you don’t index properly, your million dollar SQL cluster scales down to a very expensive version of Access 2.0.

What happens when you have locking problems? A DBA can ensure that stored procs always access tables in the correct order. That dba won’t be able to look into your code.

In any case, the move is being made, and it’s proper to accept that we have to make the new paradigm work, rather than argue against it. The solution is to find new practices and patterns to allow this to work.

So, we move on to security. The traditional model, using stored procedures within the database, offers the following security advantages:

Defence against sql injection attacks – using command parameters prevents execution of rogue sql strings

 

Least privilege and segregation of duties are enforced at a more granular level

 

Table level permissions do not have to be granted

Granting execute permissions to stored procedures means that the security model is that of allowing exactly the access we wish to grant. Compare this to the execution of dynamic sql, where we have to validate against every possible attack that might occur.

How secure is LINQ to SQL? An article by Jason Schmitt demonstrates the parameterized queries created by the LINQ infrastructure can prevent SQL injection. Use of the sp_executesql command certainly prevents injection attacks of this type.

What about the granting of CRUD access to tables within the database? Well, here’s an issue.

The role based security at the database boundary breaks down. We lose some granularity of least privilege and separation of duties as we access the server.

Granting correct table/column access directly to tables becomes incredibly complex when you have more than perhaps three roles. The greater the complexity, the more prone to error your security strategy is.

Sprocs allow access to be restricted to certain rows, via joins and where clauses. Table access doesn't do this. Although you can restrict which rows you access in your LINQ code, you've lost that security granularity at the database server level. What if an internal user downloads the SQL utilities to his/her desktop?

There’s a way to work with this.

It seems that flowing the security context is probably going to be ruled out in applications that use LINQ to SQL. To have the flexibility to allow developers to put data access logic within the code, a fair few table permissions would be required.

A rogue user downloading, say, the SQL Management tools would be able to disrupt database activities due to the security barrier at the database server being relaxed.

What about a trusted subsystem approach? Database access would be restricted to an application, and the application would have to enforce the appropriate access to tables. This would mean granting the necessary access to a role that the application service account belongs to. And don’t think that means you can put the account into the dbo role!

This is the security context in which many web apps work, and is not exactly rocket science. It’s quite effective.

However, it will mean a change for Windows Forms apps that run in the context of the logged on user. You may have to write a component running in a separate process on the database server to handle data access, rather than just use the SQL provider to access directly. The Windows app would then access the component, perhaps via a web service. The Windows front end would stay dumb.

At the moment, this is more of a ‘thoughts of Chairman Chris’ rant, but perhaps it’s the seed of so far unwritten best practice. Please give me your thoughts, and perhaps we can achieve something more solid.