Senior Member
Join Date: Jun 2005
|
I'm supposed to write a stored procedure that will update a production database given a table name, column name, and value as inputs. This seems like a bad idea to me because of security, performance, and debugging issues, but my boss is pretty insistent.
Is it actually a bad idea? If so, is there somewhere authoritative I could go to prove it? I've searched the Oracle documentation but I can't find something that explicitly says to avoid this. |
quote |
is the next Chiquita
Join Date: Feb 2005
|
Well, considering that you probably can code the validation/security check into your stored procedure, it may actually make more sense than giving your users direct access to the tables. In general, I'd prefer using stored procedures over triggers mainly to minimize contention. Furthermore, it's not unheard that DBA would grant access only to stored procedures and deny any access to tables for average users so they have control over what data is going in and who can see what above and beyond the built-in security mechanism for a given RDBMS.
But then again, I'm aware of cases where shops will actively avoid any stored procedures... not because of any inherent flaws, but rather to avoid getting locked into single database product. That is generally true for web applications, I think. Finally, since your boss is basically asking for a dynamic SQL, I'm 99% sure that cannot be meaningfully parameterized away short of using, well, dynamic SQL. I would be *extremely* uncomfortable allowing my users to execute a dynamic SQL directly or indirectly, and at least with stored procedure, one can encapsulate the logic and protect them from inputting in system tables or anything that they shouldn't be doing. Note, that also can be done in the application level but that has its own set of issues, and again is generally unrelated to security/technical merits but rather the business needs as is the case with the web application mentioned above. HTH. |
quote |
Senior Member
Join Date: Jun 2005
|
Quote:
|
|
quote |
is the next Chiquita
Join Date: Feb 2005
|
Understandable. I can only say that this is basically what you'd tell your boss for why we shouldn't use stored procedures. I'd be surprised if stored procedures were actually less secure than an external script and wouldn't try to argue that way anyway, regardless of the RDBMS product we're talking about.
|
quote |
‽
|
Quote:
Needless to say, you're throwing most benefits of a relational database out the window. Might consider a dictionary-based alternative, in that case. There are reasons to do this, but a catch-all "update x set y=z" procedure sounds absolutely horrid. |
|
quote |
Posting Rules | Navigation |
|
Thread Tools | |
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need a GUID Partition Table to install Leopard? | turtle | Genius Bar | 17 | 2007-11-23 17:08 |
How to Create Excel Table in Word 2004? | gbh | Genius Bar | 7 | 2007-06-21 10:39 |
CSS noob need schooling (Using CSS to replace a table) | ThunderPoit | Programmer's Nook | 24 | 2006-08-28 20:16 |
Way to Insert Table in vBulletin? | JK47 | Genius Bar | 15 | 2006-06-04 00:51 |
Futuristic Furniture: Tasting Table | curiousuburb | AppleOutsider | 2 | 2006-01-19 13:30 |