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

Get Microsoft® SQL Server® 2008 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.