Sprocs? Really?

I’ve been studying for my first SQL Server certification (70-431, if you’re curious).  I read the chapter today on “Implementing Stored Procedures.”  As I was reading, the following passage got my attention:

The permission delegation possible with stored procedures provides a powerful security mechanism within SQL Server.  If all data access – insertions, deletions, updates, or selects – were performed through stored procedures, users could not directly access any table in the database.  Only by executing the stored procedures would users be able to perform the actions necessary to manage the database.  And although users would have the permissions delegated through the stored procedures, they would still be bound to the code within the stored procedure…

How is this in any way desirable?  Who are you actually trying to keep the real data away from?  The only answer that is obvious to me is developers.  The author seems to be describing some personal utopia, where no childish developers could destroy what the mighty DBA hath wrought.

He says in an earlier section, “Even more important, stored procedures hide the structure of a database from a user…”  Hide the structure of the database?  Again, why?  How could developers make informed decisions about creating data access layers if they don’t even know what tables the data is on?

The author also says that using stored procedures lets you “…isolate database code for easy maintenance instead of requiring you to find hard-coded SQL statements throughout an application if you need to make changes.”  A worthy goal, certainly, but is the answer really to put the executable code in the database?  I would think that a well-crafted data access layer would do a much better job of this.  Conventional languages offer much more flexible language environments, and code changes are tracked by source control.  You can put stored procedure definitions in source control, but it’s in no way integrated into SQL Server.  It’s basically the same developer experience as checking in some unrelated text file to some arbitrary directory in your tree, which has has no real bearing on what’s actually running in the DB at the moment.

I’ve heard people talk about the database-centered approach espoused by MS, but this really makes it clear.  It’s as if the author feels that the best application would be one with as little application code as possible, basically a thin veneer over a relational database.  Since I’ve worked for the first three years of my career in a shop that used DB2 (with absolutely no stored procedures), I guess I haven’t even been offered this particular flavor of Microsoft Kool-Aid until now.  If this has been the Redmond gospel for a while, I can see why using OR mappers must feel like such as breath of fresh air to some people.

For what it’s worth, I’m planning on using ASP.NET MVC with Castle’s ActiveRecord for my next pet project, a little site I’m putting together for my mom’s fiancee.  I’ll be sure to record my impressions of that story.

6 thoughts on “Sprocs? Really?

  1. Jason

    This is not so much for developer. They may be the ones writing the procs. If the login that your app uses, can only write to your tables via delegated permission through the stored proc’s there is a lot less damage that can be done through SQL injection.

    Reply
  2. Brian Sullivan

    Jason,

    Thanks for your feedback! I agree that SQL injection is a legitimate threat, but parameterized dynamic queries can give you close to the same level of proctection, as far as I’m aware. As far as locking down tables to certain logins for particular parts of an application, it seems to be a case of the left hand not knowing what the right as doing. Seems a bit unnecessary to me.

    Reply
  3. Keith Elder

    Ok Brian, I read your article and went, yep, Brian hasn’t built any large scale applications in the Enterprise. Anyone that has had to deal with that type of application would have understood what that paragraph was talking about.

    There are A LOT of use cases for this very example. For example, let’s take compliance regulations in the financial sector. There are rules that state every request to personal data has to be audited along with data must be secured (the rules read very law like but I’m dumbing down here).

    In order to achieve this the only way to be “compliant” is to use stored procedures. Example, how would you handle the situation by throwing plain SQL at a database if you had to track each and every time certain pieces of data was changed in a table row for audit purposes? Think about the complexity of managing that on the developer side of the house. Not only that but think about the numerous entry points into a database that also have to be tracked. For example, imports through SSIS packages, web services, and not to mention the UI. It is insain to think that ALL developers would remember to do it in their code and even then do it correctly. Think about it, how do you REALLY guarantee the integrity of the data if you had to prove it to someone beyond a reasonable doubt.

    This is where DBAs add a tremendous value and become the gate keeper so to speak of. I can give you a bunch of other reasons but until you get over the fact in your mind that plain SQL will work and fit all cases and that is the only way it should be, I can’t help you.

    Reply
  4. Greg Young

    Its funny Keith …

    I don’t use sprocs and I build “enterprise” systems. My “audit logs” are no problem at all.

    My personal favorite part is “For example, imports through SSIS packages, web services, and not to mention the UI.”

    Perhaps if you stopped viewing your database as a point of integration you would be able to free yourself from your current viewpoints?

    What is truely funny though is that not only are you so completely and utterly off base you are also a condescending at the same time.

    Brian keep on the good path! Integration through the database is the big problem being shown, so don’t do it. Treat your data model as being private, make integration explicit either synchronously or asynchronously with messaging. If you follow Keith’s advice the next logical step is that all business logic needs to be in the database as well (what happens when SSIS writes data that breaks business rules?)

    Cheers,

    Greg

    Reply
    1. admin Post author

      @Greg,

      Thanks for the comment! I think we agree that, while the database has an important role in an application (storing data), it’s a bad idea to try to get it to be all things to all people. We can be much more expressive about a lot of things in a general-purpose, object-oriented programming language than we can in SQL.

      Plus, the idea of shutting developers out of the database to the point of them not being aware of the underlying table structure sounds like a recipe for disaster to me.

      Don’t give Keith too hard a time, though. 😉 He’s a good guy, and he and I go way back, so I’m sure he had no intention of coming off condescending. He’s also got quite a bit more development experience under his belt than I do, so I would be remiss not to at least consider his viewpoint and advice.

      Cheers!

      Brian

      Reply
  5. Pingback: Should we still be using stored procedures? « whygwarren

Leave a Reply to Keith Elder Cancel reply

Your email address will not be published. Required fields are marked *