Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 147 | Views in the last 30 days: 1
 
Related Articles
FORUM

DB Design Help

Design

FORUM

Slowly Changing dimensions Design and ETL Help

Slowly Changing dimensions Design and ETL Help

ARTICLE

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...

FORUM

Need to give Developer access to create stored procs

Need to give developer access to create stored procs

BLOG

Database Design Guidelines For Developers

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

Tags
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones