MSSQL User Defined Functions vs Stored Procedures
I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”
While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.
Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.
Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions
DLL in Stored Procedure MSSQL
Extended Stored Procedures (MSDN), http://support.microsoft.com/kb/190987
Thread Local Storage in an extended stored procedure (MSDN), http://support.microsoft.com/kb/163449
How to Create secure relational databases in SQL
The first step to a secure DB, is a well structured DB and the application layers that access it.
Take the following example of a table containing factories and the robots they produce.
This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?
First, lets separate the data into two separate tables of factories and robots:
In order to group these two together. we now require a mapping table.
To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.
Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.
Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.
Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join
To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.
So now you have:
For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?
What if a new business change now requires you to only get robots in factories after specific manufacture dates?
You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.
This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.
What’s the alternative?
Views are joins
For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.
An example of such join for our above is:
select * from factories inner join robots on factories.factoryid = robots.robotid
This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.
Cool, now what?
You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.
Stored Procedures offer security
Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.
Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.
Consider the following stored procedure:
USE [RobotCo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIED ON GO CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime) AS BEGIN SET NOCOUNT ON; select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate() END
You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.
Locking it down
All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.
Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.
Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.
Microsoft Linq, http://msdn.microsoft.com/en-us/netframework/aa904594
Ruby on Rails, http://rubyonrails.org/