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 ?