Doug Reilly's Weblog

Embedded Reporting of the Information Age...

November 2003 - Posts

One more SQL SP Issue..

Frans says:

Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves. Changing an API is not that good, it will break a lot of code in some situations. Adding new functionality or new procedures is the "best" way to extend an existing API. A set of stored procedures is no different. This means that when a table changes, or behaviour of a stored procedure changes and it requires a new parameter, a new stored procedure has to be added.

Not true.  In most cases, you can add a parameter with a default value to the existing stored procedure, and that will then allow the new parameter to be used, but will not break existing code that does not need to vary from the default value of that new parameter.

Also, I would use a temp table or table variable over cursors, and in many cases, given a temp table, you can do the type of set based uperations on the temp table that SQL Server was created for.  In any event, it will be more efficient than looping through the result set on the client in most cases.

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.

Another Blog...

I have started blogging about mobile software issues here.  A warning:  This site requires registration (which is free) and does not have an RSS feed yet.  And in the interests of full disclosure, I am blogging at the new site “for hire” for the new electronic magazine  It is an interesting concept:  Blogging as actual editorial content.

I do not think the folks in charge completely get blogging and how it best works, but with any luck they will learn.


ASP.NET Page Title
Fabrice writes about makeing the Title as a server-side control, and dynamically changing it from code behind. This works well in general, but be prepared if using VS.NET to have the RunAt=Server attribute removed now and again (causing an error when you then reference the title in codebehind. Sigh. Hopefully, this will be resolved in Whitbey!

Panopticon Central talks about the likelyhood that what is called "Refactoring" in the C# IDE will be present, but called something else in VB's IDE, presumably because VB programmers will not know what refactoring is. If folks like Julia want VB.NET programmers to be given respect (as they should be) then we need to make sure that the VB.NET IDE is not Dumbed Down.

Folks, if VB.NET programmers are "real programmers", they will, upon being presented with a menu option called Refactoring, go to Deja or elsewhere and discover what refactoring is, presuming they do not already know what it is. Andrew is a proud VB programmer, and I am darn sure he knows what Refactoring is.

More Posts