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.
|