> > 1. Security - with stored procedures you do not have to give > > permissions to the underlying tables. This is becoming a bigger issue > > with Sarbanes-Oxley, HIPAA and other privacy regulations. > > Valid.
And even that is only halfway valid. You can remove update/delete/insert permissions and get security from that. However, in most real-world scenarios I've seen removing the select permission on tables would stall the organization. Even if you have developed 500 reports in your master information system, people will still need 500 more which they'll do on an ad-hoc basis with various query tools, tables attached thru Access or Excel, and so on. > > 2. Cached query plans. > > Bogus. Query plans for parameterized SQL are cached. His argument used to be valid, you know. Circa 1999... > > 3. SQL injection. > > Bogus. Parameterized SQL is save from injection. I know at least a few instances of production code that handle 'security' in roughly the following way: CREATE PROCEDURE GenerateReport @query varchar(1000) AS DECLARE @theQuery varchar(1000); SET @theQuery = 'SELECT * FROM X WHERE ' + @query; sp_execute @theQuery Let readers decide by themselves how much actual security you get from stored procedures like this ;) <Sigh> Isn't it time people understood that security is something you think about constantly during the life of the project, instead of something that your whizbang database/framework/OS/IDE will take care of "all by itself" :) Kamen Lilov Delera Systems http://www.delera.com/blog/ =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
