Secure DAL Design using Stored Procedures

Question

I normally avoid sProcs as much as possible. I dont like the language be it TSQL or PL/SQL; they seem archaic against Java/Dot-Net which I use. I go for them when a routine needs to fetch a lot of data, crunch it and generate a small set of output. Sitting inside the DB makes the fetching process a lot fast, no network hit. But that is all.

I recently came across a DAL design where absolutely all of the CURD operations were implemented in Stored Procedures. Actually one giant sProc to be precise. Here is the skeleton:

PROCEDURE myGenericProc(int QueryNo, varchar genericParam1, ..., varchar genericParamN)
BEGIN
    SWITCH queryNo
    CASE 1
        SELECT * FROM table1 INNER JOIN table 2 ON ...

    CASE 2
        DELETE FROM table 3 WHERE ...
    ...

    CASE n
        UPDATE table4 SET a=b WHERE ...
END

The designer's logic behind this is: if I do these things in code, then the database-connection needs to have full rights on all the tables. The connection credentials are generally in connection string, which is on the application server. If the application server is compromised, inevitably the entire DB is also compromised.

As an alternative, have all the queries in the sProc, then grant that sProc full rights. Call only that sProc. This way, even if the application server is compromised, only the sProc interface can be attacked. No one can do DROP users_master.

While I agree with the principle, I hate the implementation. Unfortunately, some security paranoid clients (banks) want us to do exactly that. Also, the DBAs hate tuning access privileges on 200+ sProcs, they want as-few-as-possible items to audit.

Question:

Is there any other implementation that provides same level of security, but is more cleaner ?

Answer 1

Sprocs are very good for implementing a secure data access layer - you write sprocs for reading and writing data, and give the client execute access to the sprocs only - no access to the underlying tables or views.

This gives your DB an API that clients use, in much the same way as any class implemented in your business logic code would, but much more secure. It prevents the kind of exploits we keep reading about in the news where some hacker has gained access to every user's password - if the only way to access a password was via a sproc, the attacker who gained access to the DB could only retrieve 1 password at a time, running select * from users just isn't possible once he's bypassed your publicly-facing servers.

In addition, you can partition your back-end DB into schemas so that some sprocs cannot even access other parts of the DB.

In short, its a nice way of implementing a controlled API for the DB rather than just letting anyone run whatever query that feel like against it. You can obviously improve performance using sprocs for data access that requires complex queries, and you can re-implement your back-end schema without any client realising its changed.

I worked in a highly secure system a few times (financial) that required the front-end web site access business logic in a middle-tier service, the service was secured so only the web server could access it, it in turn called sprocs on the db that were in turn secured so only the middle tier services that needed access to them were allowed. It might seem over complex but once you'd done the first example of each part it was very easy to understand where to put other features. It also meant specialists could write the relevant parts of the application (ie web, service or sql) and they'd come together later in integration.

I wouldn't like to write a single sproc that caters to all API calls - that's total pants. The DBAs should be happy with several sprocs, they can review and audit only changes then - and not audit the entire thing for 1 piddly change.

Answer 2

Modern Object Relation Mapping libraries, and even the lower level libraries allowing you to run SQL against the database, have guards in place to prevent SQL injection attacks. To use stored procedures because all other solutions are not secure is both short sighted and false. If you must hard code SQL, you can use parameterized queries, which guard against SQL injection attacks.

If your reason for using stored procedures is to centralize business logic, then you've got a legitimate reason for using them, but it had better be a good enough reason to make your system harder to test. Throwing a bunch of unit tests at classes in Java or C# is pretty easy. Unit testing your stored procedures is more difficult, but if you many different technologies needing the same database, and the same business rules then stored procedures are nice to have.

I have a love-hate relationship with them. On the one had, I hate having to map every single stinking parameter and I like how an ORM can just auto generate the INSERT, UPDATE and DELETE commands. Then again, most ORMs allow you to call stored procedures, so you get the dynamic querying ability of the ORM with the hardened interface provided by stored procedures.

Really, your reason for using stored procedures should not be "because it's more secure", as this likely results from not knowing how to use the other tools available.

Answer 3

I try to avoid stored proc's if at all possible. But when I have to for security, my goal is a compromised approach. I use stored proc's for updating commands (INSERT, UPDATE, DELETE) and the ORM for everything else.

Further, each stored proc does a single operation on a single table. The application code coordinates all of the calls and manages the transactions.

The biggest problem I've experienced with stored proc's is that development becomes more difficult. Stored proc's obfuscate the work:

• Following the program flow is more difficult, especially if two different IDE's must be used (one for the stored proc's, one for the code)
• Tracking bugs down is harder because of the mental context switch
• When stored proc's call other stored proc's, the problems get exaggerated