Linq and Database Security

Question

I'm a DBA within our development group and am trying to develop policy and standards for our application developers.

Our next project is an internet web application using .net framework 3.5 and SQL Server 2005.  Authentication to SQL Server is currently via a single application login, credentials are embedded in the connection string.

The developers want to use Linq and generate Linq queries using dynamic SQL to update the database.  This means I'll be granting direct table access:  select, update, insert, delete, across all accessed tables to the application login.

I found the following best practices document for web development which specifically states that direct table access should not be granted.  The problem is that it has not been updated to include anything about Linq.  I'm not sure whether the advice here is outdated, or if it still stands as Microsoft best practice.

***********************
Best practice policies for web development. In chapter 14 they outline all of their best practices for database access. Refer to the subsections (Use Stored Procedures and Data access authorization, assembly, and database).

 

 

http://msdn.microsoft.com/en-us/library/aa302430.aspx#c14618429_008

 

******************************

Pls be advised that I'm aware that Linq can be used just to call stored procedures, but that's not what our developers are interested in doing.  It saves time for them to code the db access directly in Linq, but I'm very concerned about opening the database security up to allow the level of access that would be required under a full implementation of Linq.

Here's my question ... does anyone know of a formal best practices document (like this one) which discusses best practice security measures for SQL Server access from Linq?  Some DBAs are advocating granting select access to tables, but not create, update, delete.  I'm hoping Microsoft has published something on this issue.

thx very much,
Kathy

Answer 1

It is fairly simple; if you want to use Linq-to-SQL for reading and writing data without falling back on stored procedures then the login(s) used will need to be able to read and write from the tables (and/or views) that they need to use/access.

If you want to block direct table access you could always create one (or multiple) view(s) for each table, and have instead-of triggers on the views to control whatever security aspects you may want to control when updating/inserting/deleting, and only grant access to those views to the login(s) used by the apps/devs...  The MS Oslo repository demonstrates this, so you may want to take a look at that if you want to go down the tables+views route. (Although 'oslo' is in early CTP, but can still be used for ideas on view frontings for tables).


On the other hand, you may want to ask yourself what is/are the reason(s) for not allowing direct table access. The reasons may include:

a) If you have tables containing data where parts should be accessible to some users but not to others. This could again be overcome by creating views, alternatively by splitting up the tables. This works unless there are an infinite number of combinations of users and access levels. If that is the case then maybe Linq-to-SQL (and other OR mappers) is not a good choice of data access technology; instead classic ADO.NET might be a better choice...

b) Avoiding damages caused by SQL injection could be another reason to deny direct table access. SQL injection with Linq-to-SQL is very unlikely unless developers circumvent Linq-to-SQL itself (by for example building and executing raw SQL (sql-in-strings) directly, or by writing and calling stored procedures that are 'sql-injection-enabled'). All SQL generated by Linq-to-SQL itself is parameterized and thus 'injection safe' (with the exception of calls to 'injection enabled' stored procs/funcs).

c) Keeping all SQL under "dba control" in stored procedures and views. If the developers are not "sql aware" then they can shoot themselves in the foot [almost] as easily with Linq-to-SQL (and other OR mappers) as with plain old SQL. Although I must add that Linq-to-SQL does a good job at optimizing the generated SQL queries in many instances, such as eliminating predicates client-side, taking care of joins when navigation properties/associations are used, etc. Still, database-unaware developers can easily write Linq-to-SQL queries that translate into bad or inefficient SQL just the same way that they can do with plain SQL, so profile, profile, profile if that is the case in your org.

(b) and (c) are outdated reasons in my opinion, (a) can be valid but is rare in most LOB apps...


In short, the normal permissions granted to logins used by Linq-to-SQL apps would be db_datareader and db_datawriter, possibly sprinkled with a couple of table-level denys if there are any tables that the app or login should not be able to access...   ...if that is unacceptable then you may want to either look at another data access technology and/or go into more detail as to why you don't want to allow direct table access.

Just my 2 cents.

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

Thanks for your insight. 

My primary concern with granting table access is not that Linq doesn't prevent against existing attack technology like SQL injection.

I guess I'm trying to find out if we should still be concerned about future technology.  If somehow someone does find a way to breach the security at the application layer then the database is unprotected.  Indepent enforcement of security at both the application and database levels was I thought one of the primary reasons for staying with stored procedures.

I was hoping that someone might know of some official statement or white paper from MSDN sight itself which endorses relaxing of permissions at the database level in favor of the application layer.

Right now ... in the white paper that I sited above.  Microsoft states not to allow any direct table access at all, not even for selects.

The workaround using views and triggers seems complicated, but I will look into it.

 

Answer 3

Well, everything has its tradeoffs. :) It is easier to use Linq-to-SQL efficiently with full table access than without. Without table access, the benefits of using Linq-to-SQL will shrink a bit although it does work fine with stored procs.

Oh, and I forgot to mention; you can grant read-only access to the tables and configure your L2S datacontext to do just the insert/update/delete operations through stored procedures. Every entity in the designer has a property for Insert/Update/Delete that defaults to "use runtime". "Use runtime" means that L2S will generate the insert/update/delete statements itself. Those can be changed to map to stored procedures instead. I'm not sure if there really is a lot to gain security-wise from that since the login will still need access to the same stored procs, so if malicious code would make it into the app it could still create damage by calling those stored procs...  ...but at least you could protect individual [sensitive] fields, do auditing etc within those stored procs. (This is described in more detail here http://msdn.microsoft.com/en-us/library/bb384575.aspx )

As for other potential future attack vectors - you can always layer your application to reduce the risk from that. A Linq-to-SQL based DAL that is accessed through a WCF service layer would minimize the risk if the top layer app would get compromized by some other kind of code injection. The compromised top-layer-app would only be able to do what the service layer allows it to do (through whatever methods are exposed by the service layer).

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)