The Truth about Stored Procs and O/R Mapping
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.