Should data protection be performed on the database side?

Question

We are in the process of creating a new structure and way of doing business for our new internal applications. Our current design dictates that all security logic must be processed by our database, and all information (and I mean everything) will enter and exit the database through stored procedures.

The theory is that the data access layer requests information from the stored procedure and passes authentication to the database. The database determines the role / permissions of the user and decides whether or not to perform the task (whether it is receiving data or updating).

I guess this means less database transactions. One call to the database. If security were at our data access level, this would require 1 database call to determine if the user had the proper permissions, and then 1 separate database call to complete the action.

For example, I believe that SQL Management is completely absent as an IDE. My main concern is that we will have to maintain some nasty business logic in our stored procedures for minimal performance gains.

Right now we are using LINQ for our ORM. It seems easy and quick, but, best of all, is developing rapidly.

Is the cost of maintenance an increase in productivity? Do we fool ourselves into thinking that there will even be a noticeable performance boost? Or are we just making ourselves a nightmare?

Our surroundings:

• Internal, non-critical business applications
• C # / ASP.NET 3.5
• Windows 2003
• MS SQL Server 2005
• 35 medium-sized web applications with approximately 500 users

Answer 1

Do not do this . We recently had VERY BAD experience when the “database guru” decided to go to another company. The content of all the logic in the procedures is just awful!

Yes, you will have some performance improvement, but it is not worth it. In fact, performance does not cause much concern in the internal application. Invest more money on good servers. It will pay off.

Answer 2

Unfortunately, there is no "one true answer." The choice you have to make depends on several factors, such as:

• Acquaintance of the team with these solutions (that is, if most of them are convenient for writing SQL, it can be in the database, however, if most of them are more convenient with C #, it should be in the code)
• The "political power" of each side
• etc.

There is no decisive advantage in any direction (since you said that the performance gain is minimal), one thing to keep in mind is the principle of DRY (Do not Repeat Yourself): do not redefine functionality twice (in the code and in the database), therefore that keeping them in sync would be a nightmare. Choose one solution and stick to it.

Answer 3

You can do it, but it is a huge pain to develop against and support. Take it from someone who is in a project where almost all of the business logic is encoded in stored procedures.

For security, ASP.NET uses user and role management, so you can save trips to the database, but what? In return, it becomes much more annoying to process and debug system errors and checks because they need to bubble from the database.

Unit testing is much more complicated since the framework available for unit testing is much less developed.

The correct design based on oop and domain is outside the window.

And performance will be tiny, if any. We talked about it here .

I would recommend that if you want to keep your sanity as a developer, you will struggle with your teeth and nails to save the database only as a save layer.

Answer 4

IMHO:

Application Service Level → Application Logic and Validation
Application Data Layer → Data Logic and Security
Database -> Data Consistency

Sooner or later you will be bitten by the sproc approach, I learned this. Procs are great for single shot operations that require a lot of performance, but part of CRUD is working with data layers.

Answer 5

It all depends on your case, it is probably best not to go the SP path and do all the DDDs (make a domain model in the code and use this).

However, if you have a database that is used not only by your application, but also by many, then you should probably consider web services. In any case, the database should be accessible only through one level, which ensures compliance with business rules, otherwise you will receive "dirty" data, and the subsequent cleaning of your data is a much more difficult task than pre-writing several business rules. A good database should have validation restrictions and indexes installed, so it will have some business rules, whether 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 who will solve the problem for you.

Answer 6

Stored procedures usually benefit from security. Simplifying the relationship between your application and the database reduces the number of places where errors may occur; errors in code that links business logic to the database are typically security issues. Thus, your database administrator is not mistaken in blocking all stored procedures.

Another advantage of locking the application to stored procedures is that the application stack database connection can have its privileges blocked for certain stored procedure calls, and nothing more.

The advantage of using DBA in the security logic for your application is that the various functions and roles of the applications can be shared in the database prior to representations, so that even if dynamic SQL statements and general choices are needed, the damage from SQL vulnerability can be limited.

The flip side of this, of course, has lost flexibility. Obviously, ORM will evolve faster than constant consistency with the DBA regarding stored procedure parameters. And, as the pressure on these stored procedures grows, it is more likely that the procedures themselves will resort to dynamic SQL, which will be as vulnerable as the application compiled by SQL for attack.

There is a happy middle ground here, and you should try to find it. I recently worked on projects that were saved from the rather terrible problems with SQL injection, because the database administrator carefully configured the database, its connections and stored procedures for "minimal privileges", so that any database user had access only to that what they need to know.

Obviously, when you write SQL code in your application logic, make sure that you consistently use parameterized prepared statements, that you sanitize your input, that you remember internationalized input (there are many ways to specify a single quote over HTTP), and that you remember how your database behaves when inputs are too large for column widths.

Answer 7

My opinion is that the application itself should handle authentication and authorization. On the database side, you should only handle data encryption.

Answer 8

In the past, I had built-in applications based on stored procedures. In your case, there might be a way to keep authentication at the database level and have your own business logic in C #. Use views to restrict data (you only see rows for which you have permission). These views can be used in LINQ with the same ease as tables. You install updates for stored procedures.

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