Thanks to those who attended Saturday’s Microsoft Code Camp (see http://richmondcodecamp.org/). Here are materials for the presentation “Pretty Good Row Level Security” which I did with Nic Morel, my fellow CapTech Ventures Lead Consultant.
The presentation reviewed a security solution that limits access at the data level, leverages simple database protocols, requires minimal administration resources, applies to both users and application developers, and overcomes some of the vulnerabilities of default approaches like having the application use a “system” user id.
The solution presents a three layer security architecture:
1. Base tables are accessible only to DBAs.
2. A security layer including cross-reference tables relating user ids to key data like department or sales territory, and table-valued functions that accept user id as a parameter and deliver data from base tables with a join to the security cross reference tables.
3. A data access layer exposed to users consisting of views that access the table-valued functions, providing the current user id as a parameter. End users and developers only have access to this data access layer.
Email me if you’d like the powerpoint (a little big to attach here, even without the accent graphics), and I’ve pasted in the SQL scripts as a comment on this post. The second two examples run against the SQL Server AdventureWorks database that ships with 2005 Developer Edition.