Using Stored Procedures instead of Dynamic SQL

By Chris Miller on 29 November 2000 | Tags: Stored Procedures

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:

delete orders where OrderID = 42
delete orders where orderid in (select OrderID from BigOrdersTable cross join Employees)
delete orders

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:

create proc lsp_deleteorders @OrderID int
as
begin
	delete Orders where OrderID = @OrderID
end
go

grant execute on lsp_deleteorders to spud

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:

alter proc lsp_deleteorders @OrderID int
as
begin
	update orders set active = 0 where OrderID = @OrderID
end

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:

alter proc lsp_deleteorders @OrderID int
as
begin
	declare @Sql varchar(200)
	set @SQL = 'update orders set active = 0 where OrderID = ' + convert(varchar, @OrderID)
	exec(@SQL)
end

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.

RocketScientist