SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Always Abstract

By Steve Jones,

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. That might make this easy to implement.

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.

Total article views: 155 | Views in the last 30 days: 1
Related Articles

DB Design Help



Slowly Changing dimensions Design and ETL Help

Slowly Changing dimensions Design and ETL Help


Need to give Developer access to create stored procs

Need to give developer access to create stored procs


A SSIS Design Pattern for Change Data Capture

The design pattern described in this article can be used to develop very efficient CDC operations in...


Database Design Guidelines For Developers

One of the developers that I work with asked me to write a “brief” (really brief) guide on database ...