Stored Procedures and security...
Frans argues that Rob is wrong about stored procedures. I expect Rob will have some answers, but I have a good answer to at least one of the arguments Frans makes, about security. He says:
Ah yes, the good old dead horse, security! The most heard argument in favor of stored procedures is that with using stored procedures, security can be controlled using a fine grained mechanism: place the execution rights on the procedure and you're set. Well... ever heard of role-based security? Rob Howard hasn't obviously. Security is a subject that has to be taken seriously, very seriously. Therefore, a good DBA defines user-roles in SqlServer. Users are added to those roles and rights are defined per role, not per user. This way, you can control which users can insert / update and which users can for example select or delete or have access to views in an easy way: in most applications you have 2 roles: the average user, and the administrator user which configures the application. Define 2 roles in the SqlServer database, place the users in the right role, define the rights per role on the tables and views and off you go: fine grained security which works, without a single stored procedure in sight. A new user is added? You just add it to a user role and it has the rights it should have.
Another nice object in SqlServer, which I already mentioned earlier, is the view. Views are there to control which data is accessed on a column basis or row basis. This means that if you want user U to select only 2 or so columns from a table, you can give that user access to a view, not the underlying table. The same goes for rows in one or more tables. Create a view which shows those rows, filtering out others. Give access rights to the view, not the table, obviously using user-roles. This way you can limit access to sensitive data without having to compromise your programming model because you have to move to stored procedures. Views are totally ignored by Rob Howard, while the view is especially created for this purpose. See Books Online: Using Views as Security Mechanisms.
Rob mentions SQL injection attacks. SQL queries which are constructed by simply concatenating data into a query statement are indeed open for SQL injection attacks. However because ADO.NET has a great parameter support, why would anyone neglect this fine instrument? A good Dynamic SQL engine creates parametrized queries, which are not only faster (because the execution plan is cached, yes Rob, it is), they are also not open for SQL injection attacks due to the parameters.
There are a couple of points. First, Frans is absolutely correct with respect to SQL Injection. Stored Procedures are a red herring here. Parameters are the issue, and of course parameters can be used with raw SQL.
On the other hand, Frans is not entirely correct with respect to Roles and Views being the solution to data access security. Do you want to have and maintain a view for each user group on each table that needs to be secured? I sure don't. As far as columns, this is likely a reasonable approach, but not for limiting rows visible by role.
This is, however, an area where Stored Procedures excel. Especailly for ASP.NET applications. How many of you pass DocumentID or other such ID's on the query string? If you do, and if your ID's are sequential, how do you prevent a user from substituting a nearby number for the ID on the query string (or in some other way spoofing a form submission)? The way I do it is to supply some basic user information that is from the Session, not provided by the client, to the stored procedure that retrieves the document (or whatever) and then only retrieve the document if the user has permission (based upon user or group membership). Yes, I am sure there are other ways around this problem, but given an existing database, simple integer ID's are convenient, but need to be balanced with the need for controlled access to data.