Skip Navigation LinksHome > Categories > Code from a Category

How to use Stored Procedures instead of Dynamic SQL



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1125
Add Date: 12/20/2013
In this article we will learn about how to use stored procedures instead of dynamic sql
Applications that access SQL Server databases should make extensive if not exclusive use of stored procedures for any statement, from a very simple one-row select to a complex cascaded delete.

Applications that paste together SQL statements are more difficult to manage and result in less secure databases than applications that exclusively use stored procedures.

This is a topic which could cause a small flamewar. But I'm right and if you disagree with me, you're wrong, so there :). I've answered a few forum questions that revolve around security and design modularity that have compelled me to write this article.

SQL Server has a security concept called an Ownership Chain. If the database owner creates a table called Orders, and then grants a user rights to delete from that table, it gives the user the authority to do any of the following:

OK, the first statement is probably the one that you want the user to run: delete order number 42. The second statement is going to consume the CPU on the SQL Server for about 3 days fulfilling the cross join repetedly, and probably results from either a clueless user in ISQL/W or something typically done by Microsoft Access. The third is the most dangerous, because it will delete all of the orders in the orders table, and it's conceivable that a poorly written application could generate this kind of behavior accidently.

OK, so how do ownership chains fix this? Well, the datbase owner (actually, the object owner) can create a stored procedure called lsp_DeleteOrders:

This procedure allows one and only one order to be deleted from the orders table at a time by the user named 'spud'. Then the database owner can grant individual users rights to run that stored procedure, and they'll be able to delete rows without having delete permissions on the orders table. Basically, SQL Server looks at the stored procedure and since the database owner owns the stored procedure and the table being modified, it assumes that the database owner realizes that granting execute permissions on the stored procedure will allow the granted user to delete records. It's pretty smart thinking actually, why else would the database owner write that stored procedure and grant permission to it if it wasn't supposed to be used by the grantee?

OK, that's the security problem fixed, what about stored procedures improving manageability? Well, let's say that your boss walks up and says "We shouldn't be deleting orders, we should just change them so they aren't active any more". Well, you can either change the application and redistribute it, or you can just do this:

Notice that I used "Alter Proc" instead of dropping the procedure and re-building it. That way I don't have to re-grant my 'spud' user so he can delete records.

One limitation of ownership chains is the exec() call. While both of the procedures above will work with ownership chains, the following one won't:

If you do this, then SQL Server doesn't follow the ownership chain, but instead evaluates the statement as if you'd run the query directly from ISQL/W. So, don't do that.

I think you can see where this is going. By using stored procedures, you can build more secure systems that are easier to manage. As a standard at my place of business, we don't allow any of our web sites to use any dynamic SQL because of both the security problems as well as the management issues involved.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.