Microsoft SQL Server 2012 Bible

Chapter 35

Row-Level Security

In This Chapter

Extending The Abstraction Layer For Custom Row- Level Security

Implementing The Custom Cell-Level Security

SQL Server is excellent at vertical security (tables and columns), but it lacks the capability 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 read any customer's data but only edit those customers who frequent that branch.

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

Chapter 2, “Data Architecture,” made the case for database encapsulation and a strong abstraction layer as a means toward database extensibility. ...