O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 52. Row-Level Security

IN THIS CHAPTER

  • Extending the abstraction layer for custom row-level security

  • Components of row-level security

  • Assigning and checking permissions

SQL Server is excellent at vertical security (tables and columns), but it lacks the ability to dynamically enforce row-level security. Views, using with check option, can provide a hard-coded form of row-level security, but developing a row-based security schema for an entire database using dozens or hundreds of views would create a maintenance headache.

Enterprise databases often include data that is sensitive on a row level. Consider these four real-life business-security rules:

  • Material data, inventory-cost data, and production scheduling are owned by a department and should not be available to those outside that department. However, the MRP system contains materials and inventory tracking for all locations and all departments in the entire company.

  • HR data for each employee must be available to only the HR department and an employee's direct supervisors.

  • A companywide purchasing system permits only lumber buyers to purchase lumber, and only hardware buyers to purchase hardware.

  • Each bank branch should be able to read any customer's data, but only edit those customers who frequent that branch.

I believe the best possible solution for these requirements is to build the security into the abstraction layer.

In Chapter 2, "Data Architecture," I tried to make the case for database encapsulation and a strong abstraction ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required