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 ...ENDThe 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 ?
Secure DAL Design using Stored Procedures
security;database design;stored procedures
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.