861
CHAPTER
35
Row-Level Security
IN THIS CHAPTER
Extending The Abstraction Layer For Custom Row- Level SecurityImplementing The Custom Cell-Level SecurityS
QL 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. But a strong abstraction layer also enables the
security objective.
Implementing a server-side code version of row-level security requires four components:
■ Security table: Can contain the list of users and their departments, or branches, with their
appropriate read-and-write rights
■ Security procedure: Checks the user’s rights against the data requested and returns a
status of approved or denied
c35.indd 861c35.indd 861 7/31/2012 9:59:38 AM7/31/2012 9:59:38 AM
http://www.it-ebooks.info