Microsoft® SQL Server® 2008 Bible

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