Always Abstract

The way we build databases and design security is rarely done ideally. It doesn’t matter what your Utopian design is for schemas and objects; most of us make compromise as we deal with the pressures and diverse requirements thrust upon us in the real world. That’s unfortunate, but it’s also reality. I often see advice to use some best practice, like using only stored procedures and revoking all access to tables. I rarely see this in place, though I sometimes think that an investment in developer education would quickly erase any productivity losses from implementing stored procedures and probably reduce security issues.

I don’t know we’ll get away from stored procedures, but I wonder if we can get away from tables. I’ve seen a lot of design refactoring lately that requires changing schemas. The use of views to hide base tables would allow developers to easily make changes changes. Implementing vertical or horizontal partitions (or federations), additing of new columns, and even enhancing your application with RBAC (row based access control) or encryption would be easy.

This has me wondering if we should consider it a basic practice to not ever expose base tables and use views for all of our data access? Would it make sense to make a tCustomers table and a Customers view from the very beginning of development? We could grant rights to the view and use that for all queries. Perhaps SQL Server should include a dataViewReader role and dataViewWriter role that would allow quick assignment of rights to all views, and not all objects.

I know there can be times when this wouldn’t work, and the proliferation of queries building on views built on views can be an issue. However, I also think it would make sense for data access through tables to be the exception, not the rule. Would this work in your environment? I bet it would for most of you.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.
Steve Jones Windows Media Video ( 17.0MB) feed

MP4 iPod Video ( 20.6MB) feed

MP3 Audio ( 4.1MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

1 Response to Always Abstract

  1. Noah Yetter says:

    Not a good idea. If app developers thought relationally, we wouldn’t even need to hide tables behind procedures. Hiding them behind views is worse because now you have both an abstraction problem (the app isn’t manipulating what it thinks it is) *and* a performance problem (joins and logic hidden in views cause performance to degrade in mysterious and often catastrophic ways).

    I’ve seen the view approach implemented on a large enough scale that I doubt it’s the right solution for *anyone*, much less everyone.

    Stored procedures are great but the productivity tax is ENORMOUS. Direct access to tables will remain the norm because it gives the highest productivity and the highest modal performance. Even if we DBA’s don’t like it.

    Like

Comments are closed.