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.