3

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 ?

  • 2
    "I don't like the language" is not a professional attitude, and one man's "archaic" is another's "tried and trusted". Stored procedures make a very valuable contribution to well-designed systems. Don't let the fact that some misguided soul stuffed everything into one huge mess of a routine blind you to that. The cleaner solution you seek will still contain SQL code. – Kilian Foth Mar 30 '15 at 7:28
  • @KilianFoth, I do accept that my bias against sProcs is unprofessional and I am working on it. Just that the samples I have seen in the wild all have used them in bad ways. Ways like I just exampled. If only anyone gives a sample of that well-designed system with sProcs, it will help me a lot. – inquisitive Mar 30 '15 at 8:11
  • 2
    I would posit that a stored procedure that gives you rather poorly designed CRUD access is essentially no different from giving the client INSERT, UPDATE and DELETE access on the relevant tables. This seems like a crutch to replace proper security. – Mr Cochese Mar 30 '15 at 8:21
  • Putting a giant switch statement in a stored proc like that is generally not a good idea. A better idea would be to have n number of stored procedures. This is for both maintainability and database performance. – Matthew Mar 30 '15 at 13:33
  • @Matthew, One question though, is having hundreds of sProcs OK? sProcs are kind of linear, no folders or namespaces. Would there be any reason to frown upon very trivial queries (say SELECT name, address FROM user_master WHERE id=@id) put into a separate sProc? – inquisitive Mar 30 '15 at 13:35
2

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.

  • Hi, thanks for the insight. really helpful. One question though, is having hundreds of sProcs OK? sProcs are kind of linear, no folders or namespaces. Would there be any reason to frown upon very trivial queries (say SELECT name, address FROM user_master WHERE id=@id) put into a separate sProc? – inquisitive Mar 30 '15 at 12:38
  • 1
    you can organise them by schema (at least in sqlserver). We had trivial sprocs - you have no security benefit if you allow any read access to the tables. You might want to stick several related queries into a single sproc, eg selecting a user record based on id or name is ok in 1 sproc I would say. Use common sense where to draw the line. And get a decent naming convention! – gbjbaanb Mar 30 '15 at 13:35
  • 1
    FYI we used to partition our DB schemas by logical component, and each component had a service to correspond to it. So think of the DB as just another service. Some of our services sourced data from other web services too, so the DB became a 3rd party service that returned data via its API (that just happened to be SQLClient calls rather than WCFClient calls). Its quite nice when your DB stops being a database and starts being just another API to call. – gbjbaanb Mar 30 '15 at 13:42
0

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.

  • The more secure argument is coming from the camp which points out that sProcs provide one additional level of security when the application server itself is compromised. I understand and fully appreciate the SQL parameterisation technique, but it beats me at the application server compromized situation. Given that some security paranoid clients force to use them anyway, I am looking for a cleaner pattern. – inquisitive Mar 30 '15 at 13:19
  • SQL injection is a bug, not security. To implement security you should be restricting access and putting controls in over who can do what. Testing sprocs is actually very easy - you have transactions so you can start transaction, populate data, run test, rollback. We used to do this from C# code. You can also get your ORM to map sprocs too. – gbjbaanb Mar 30 '15 at 13:39
-1

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
  • na, creating sprocs is incredibly easy - you get a DBA to write them for you, and they're usually happy to do it!! (never let sprocs become business logic though, just data access wrappers) – gbjbaanb Mar 30 '15 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.