The Truth about Stored Procs and O/R Mapping

There's been yet another series of posts recently that spread some FUD about stored procs and O/R Mapping.  I could try to refute the various points one by one, but that's been done before for those willing to look.  So instead I decided to write a little about how O/R Mapping actually changes very few things we know well.

First, do you give each user a separate db login, with varying capabilities, or does your app use just one?  This seems to be the central point of contention in some of these discussions about stored procs and O/RM, but this is actually a very independent design decision that has nothing to do with whether you use an O/RM.  That's right, you can use separate db logins with O/RM, and you can use a single db login without an O/RM.  So how do you determine which approach to use?  You must consider the various tradeoffs that are involved.  It may seem obvious that every user must have their own separate db login, but what are the implications?  The most serious tradeoff is that your app will never be able to take advantage of db connection pooling.  Because of that fact, most all web apps, as well as those enterprise apps that have a distributed design, make the decision to have just a single db login that the application uses for all users and db interaction.  This approach is used almost universally, including by those that use stored procs and do not use any O/RM, so I really find it troubling when some people ignore these facts to imply that this makes O/RM insecure.  But what if you do have a situation where you have determined that you do need separate db logins instead?  Then there is absolutely nothing about O/RM that prevents you from using that approach if that is required.  Some O/RMs won't allow you to use stored procs, but others do allow stored procs -- its not all or nothing.  The point is that arguing that using stored procs with separate db logins is more secure than a single login has absolutely nothing to do with whether or not you use O/RM -- any such comments otherwise are pure FUD.

Next, if like most projects, you have chosen to use the single db login approach to get connection pooling, then how do you end up securing your application and giving varying capabilities to your different users?  You of course end up checking user authentication and feature authorization by querying a membership store, and you probably also have a system in place so that users won't even see the capabilities they do not have.  So how do you actually prevent users from doing things that they are not allowed if your db login doesn't itself prevent those capabilities -- this is the issue raised when someone asks what if they get your dll.  The answer is that your application must manage security correctly, but this has nothing to do with O/RM, since this is true as soon as you go down the path of having a single db login for the connection pooling.  And just getting your dll doesn't give anyone any capabilities unless they also have your single db login, which could easily be the process account itself, instead of a sql login, to make that feat impossible.  The point is that security is protecting that single db login, which is trivial if its the process account, and if you don't protect that asset then you have a big problem irregardless of stored procs and/or O/RM.  So lets be very clear that security comes down to your application design and your protection of the login, and both of those key issues remain exactly the same regardless of whether or not you choose to use an O/RM.  As a side note, even if you do use separate db logins, you probably still only show users what they can do, which again makes the argument that a single login must provide additional security logic to be pure FUD.

So now, assuming we can agree that the important security questions are not at all specific to using O/RM, then what other issues are brought up in regards to whether or not one should use or avoid stored procs?  Some people continue to raise the same old tired and flawed discussions about sql injection and performance, but the facts are out there for those really interested in the truth instead of simply spreading more FUD.  I think the most interesting observation is that I know of many, myself included, that switched from these stored proc based systems to using O/RM without stored procs, but I know no one that switched the other way!  Why is that?  Could it possibly be that we know based on our experience, instead of idle academic guessing, that our apps are just as secure and performant as they were before O/RM, but far easier to build/maintain?  And why is it that I still use stored procs when they make sense, especially for batch jobs and reports, as well as for the occasional situation where there are some serious performance optimizations to be made?  Could it be that O/RM users are not anti- stored proc, but instead simply use the best tool for the job?  And why do they assume we can't possibly use triggers for maintaining history -- I've recommended triggers.  And if they really insist on stored procs, then why do they ignore O/RMs that do support those stored procs?  It seems instead that some people must defend their decision to continue doing what they have always done, in spite of the many facts and success stories that speak differently, thus the continuing parade of FUD.  Can you do things poorly with O/RM?  Absolutely -- just like you can make poor choices without an O/RM!

Finally, I saw a few people ask for examples that prove O/RM can be secure and performant and scalable.  If you are truly wanting such an example, then I point you to my WilsonWebPortal which uses my own O/RM.  All of my sites run exceptionally well on it -- better than many other popular portal based sites out there.
Published Saturday, May 27, 2006 11:21 AM by PaulWilson
Filed under: , ,

Comments

# re: The Truth about Stored Procs and O/R Mapping

Nie article and amen to a debate that will unfortunately never die.

Saturday, May 27, 2006 3:38 PM by Gregor Suttie

# re: The Truth about Stored Procs and O/R Mapping

Apparently many in the Stored Proc only crowd have never coded in the Java community where O/RM is much more prevalent.

Saturday, May 27, 2006 4:50 PM by Joe Brinkman

# re: The Truth about Stored Procs and O/R Mapping

Without religious wars, some people will have nothing to talk about.  Well said!

Monday, May 29, 2006 1:31 AM by Aaron Feng

# re: The Truth about Stored Procs and O/R Mapping

Does multiple db login mean that the application on end-user's computer has a direct connection to the database?  I am guessing that if the same company has gotten blasted (or I should "blaster" -ed) by virus attacks if they are using MS SQL Server (even other DB platforms are not immune to worms and other attacks).  

Wednesday, May 31, 2006 3:30 PM by David Parslow

# re: The Truth about Stored Procs and O/R Mapping

David:  That depends on the implementation.  A traditional client/server app would have each client computer having a direct connection to the database, but a web app or other distributed enterprise app would have all the connections coming from a server computer.

Wednesday, May 31, 2006 3:51 PM by PaulWilson

# re: The Truth about Stored Procs and O/R Mapping


The one thing I will say for stored procedures is that they can be used to better define a contract for the data source which isn't always a bad thing.

That said there is no reason why the two should be mutually exclusive .. only use a hammer when you have nails :)


Thursday, June 1, 2006 5:01 AM by Greg Young

# re: The Truth about Stored Procs and O/R Mapping

I have been using your mapper for 18 months now in both personal and professional projects, without stored procedures (not because I am necessarily anti-sproc, but because it is just easier and I have not needed them generally) and have 'converted' a lot of my peers to using it as well. None of them complain about not being burdened with writing TSQL and ADO.NET code (except where it makes sense) - I am continuing to win people over because, as you point out, their "apps are just as secure and performant as they were before O/RM, but far easier to build/maintain." I just figure those that want to let their fears get the best of them are just going to make it to market that much later than I will, with a product that isn't necessarily any better. I can't believe people don't do the very little research necessary to discover the real truth about things, but would rather remain in the dark; to each his own!

Thursday, June 1, 2006 2:46 PM by Jason Bunting

# re: The Truth about Stored Procs and O/R Mapping

Is it because stored procs can offer an 'interface' to a complex normalized database ?

Tuesday, June 6, 2006 5:54 PM by Steve

# I agree but need some examples

I agree with this statement:

<i>Because of that fact, most all web apps, as well as those enterprise apps that have a distributed design, make the decision to have just a single db login that the application uses for all users and db interaction.  This approach is used almost universally</i>

The place I work at seems to be stuck in the past and doesn't like the idea of a trusted subsystem model. Do you or anyone have any documentation on who out in the world is following this model?

Wednesday, June 28, 2006 11:30 AM by Ren

# re: The Truth about Stored Procs and O/R Mapping

Very good argument. We are using the O/RMapper at our client within clustred environment in java world and sometimes we feel this layer is somewhat slower than direct connection to database. Even with this approach we are using single db login approach and ACLs.

Saturday, July 22, 2006 9:39 AM by Ashesh Sheth

# re: The Truth about Stored Procs and O/R Mapping

This discussion is another one of those holy wars with staunch supporters on both sides of the fence fiercely defending their views until the bitter end. But the reality is that O/R mappers are just a tool like all of the other tools we use in order to get the job done. Each tool has inherent advantages and disadvantages and no single tool has been invented that gets the job done in every situation. As developers we are guilty of having a limited view of the architecture and design of systems. What is perceived as “best” to us would not always be agreed upon by the corporate DBA, Systems Architect, or CIO. How well does the tool (O/R Mapper) work in a corporate environment with DBA’s in total control of the database? How well does the tool work in LARGE applications that have several hundred programmers designing them? How well does it coordinate change or even versioning with that many programmers? How do you guarantee security with the tool? How do you prevent the evil programmer (or DBA) from simply inserting or modifying database data and paying himself a million dollar salary? Performance is secondary and worrying about the actual performance difference between dynamic SQL and stored procedures is a moot point if you can’t prove the application is secure. Removing all direct access to tables and limiting users to accessing stored procedures simplifies things to a point that your manager, DBA, or company owner can understand. Yes, the SetSalary() procedure is only callable by the Human Resource Manager. I know you can set table and column permissions on a granular level but you have just increased the complexity of the system tremendously and reduced buy-in from your security and RDBMS departments. Security Auditing with this tool becomes a non trivial task. Single login versus multiple login is just an authentication issue. There are many ways to authenticate the user. What percentage of corporate intranet applications use Windows authentication where every user is uniquely identified ( or certificates, or card readers, or biometric scanners)? Not everyone is building and using Internet apps with basic authentication on a single login. Does the RDBMS in the application have to run standalone, or does it have multiple applications accessing it? I suspect that the question of whether or not to have stored procedures is really “where do you want your business logic”. And that is a topic worthy of its own discussion. Note that I am not bashing the tool. In environments where the programmer is totally in control (and probably serving all the other roles I mentioned) they can use such a tool without all the political and corporate hoops that many of us have to jump through to have such a privilege.

Wednesday, September 13, 2006 4:49 PM by David Elyk