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 user's role/permissions and decides whether or not to perform the task (whether that be retrieving data or making an update).

I guess this means fewer 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

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 3

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 4

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