Should data security be performed on the database side?

Question

We're in the process of setting up a new framework and way of doing business for our new internal apps. Our current design dictates that all security logic should be handled by our database, and all information (and I mean all) will be going in and out of the database via stored procedures.

The theory is, the data access layer requests info from a stored procedure and passes over authentication to the database. The database determines the users role/permissions and decides whether or not to perform the task (whether that be retrieving data or making an update).

I guess this means less database transactions. One call to the database. If the security was in our data access layer, this would require 1 database call to determine if the user had proper permissions, and then 1 separate database call to perform the action.

I, for one, find the Sql Management studio completely lacking as an IDE. My main concern is we will end up having to maintain some nasty amount of business logic in our stored procedures for some very minimal performance gains.

Right now, we're using LINQ for our ORM. It seems light and fast, but best of all, its really easy to rapidly develop in.

Is the maintenance cost worth the performance gain? Are we fooling ourselves into thinking there will even be a noticeable performance gain? Or are we just making a nightmare for ourselves?

Our environment:

• Internal, non-mission critical business apps
• C#/ASP.NET 3.5
• Windows 2003
• MS SQL Server 2005
• 35 Medium sized web apps with approx 500 users

Answer 1

Don't do that. We recently had a VERY BAD experience when the "database guru" decided to go to another company. The maintenance of all the logic in the procedures are just horrible!!

Yes, you're going to have some performance improvement, but that's not worth it. In fact, performance is not even a big concern in internal application. Invest more money in good servers. It'll pay off.

Answer 2

You could do it but its a huge pain to develop against and maintain. Take it from someone who is on a project where almost all business logic is coded in stored procedures.

For security, ASP.NET has user and role management baked into it so you might be saving trips to the database but so what? In exchange it becomes far more annoying to handle and debug system and validation errors because they have to bubble up from the database.

Unit testing is far more difficult since the frameworks available for unit testing sprocs are far less developed.

Proper oop and domain driven design is all but out the window.

And the performance gain is going to be tiny if any. We talked about this here.

I would recommend that if you want to save your sanity as a developer you fight tooth and nail to keep the database as the persistence layer only

Answer 3

Unfortunately there is no "one true answer". The choice you must make depends on multiple factors, like:

• The familiarity of the team with the given solutions (ie if a majority of them is comfortable writing SQL, it can be in the database, however if a majority of them is more comfortable with C#, it should be in the code)
• The "political power" of each party
• etc

There is no decisive advantage in any direction (as you said performance gains are minimal), the one thing to keep in mind is the DRY (Don't Repeat Yourself) principle: don't reimplement the functionality twice (in the code and in the DB), because keeping them in synch will be a nightmare. Pick one solution and stick to it.

Answer 4

My opinion is that the application itself should handle authentication and authorisation. On the database side you should only handle encryption of data as needed.

Answer 5

I have built stored procedure based applications in the past. In your case there maybe a way to keep authentication at the database layer and have your business logic in C#. Use views to limit data (you only see the rows you have authority to). These views can be used in LINQ with the same ease as tables. You set your updates to happen with stored procedures.

This allows linq, business logic in C#, and a common authentication layer in the database that controls access to the data.

Answer 6

It all depends in your case it is probably better not to go the SP route and do everything the DDD way (make a Domain model in code and use that).

However, if you have a database that is not only used by your application but by many then you should probably consider webservices. In anyway the database should only be accessible via one layer that enforces the business rules else you are going to end up with "dirty" data and sanitizing your data afterwards is a much bigger pain then writing a few business rules beforehand. A good database should have checkconstraints and indexes set, so it will have some business rules weather you like it or not.

And if you have to deal with millions and billions of records you will be happy to have a good db-guy that solves the problem for you.

Answer 7

Stored procedures are usually a win for security. Simplifying the relationship between your application and the database reduces the number of places where you can have errors; errors in code that interfaces business logic to the database tend to be security problems. So, your DBA isn't wrong about locking things down to stored procedures.

Another benefit to locking the application down to stored procedures is that the app stack's database connection can have its privileges locked down to specific stored procedure calls and nothing else.

A benefit to having a DBA involved in security logic for your application is that the different app features and roles can be partitioned in the database down to views, so that even if dynamic SQL and generic select statements are needed, the damage from an SQL vulnerability can be constrained.

The flip side of this is, of course, lost flexibility. An ORM is obviously going to be faster to develop to than a constant negotiation with a DBA over stored procedure parameters. And, as the pressure on those stored procedures grows, it's more and more likely that the procedures themselves will resort to dynamic SQL, which will be just as vulnerable as app composed SQL to attack.

There's a happy middle ground here, and you should try to find it. I've worked on projects recently that were saved from pretty terrible SQL injection problems because a DBA had carefully configured the database, its connections, and its stored procedures for "least privilege", so that any one database user had access only to what they needed to know.

Obviously, as you write SQL code in your app logic, be sure that you're consistently using parameterized prepared statements, that you're sanitizing your input, that you're mindful of internationalized input (there are many, many ways to say single-quote over HTTP), and that you're mindful of how your database behaves when inputs are too large for column widths.

Answer 8

IMHO:

Application service tier -> application logic and validation
Application data tier -> data logic and security
Database -> data consistency

You will be bitten by the sproc approach sooner or later, I have learned this the hard way.
Procs are great for one shot operations that need a lot of performance, but the CRUD part is the data tiers job