Bob Lambert

—–

Update 10 January 2010: Thanks to Gints Plivna for observing that we had not posted the slides to this presentation, here they are: Pretty Good Row Level Security Slides.  – Bob

—–

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.

Summary:

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.

Excerpt from "Protecting Your Data with Row Level Security for SQL Server Databases," March 17, 2009

Data security is not optional in today’s business environment. High-visibility hacking and fraud, Sarbanes-Oxley, HIPAA regulations, and the Patriot Act all reinforce the need to present the right data to the right users and prevent the wrong ones from gaining access. Typically, “row level security” (RLS) is one requirement: to allow or permit access to particular users based on data in a particular database row. SQL Server does not provide built-in row level security.

In an article at Dr. Dobbs I present a way to build your own SQL Server row level security that restricts user access to data based on data in the row, without changing content of business tables, without affecting application or presentation developers, and regardless of how users access the data. Here’s my example application: How to can I add security to my existing orders database that will limit managers to departments they manage — and departments that report to those they manage — regardless of how users get to the tables and what queries and reports are developed against the database?

There are a number of ideas and code samples for hand-built RLS solutions ideas available on the web.  My favorite is A Fairly Capable Authorization Sub-System with Row-Level Security Capabilities (AFCAS) by Kemal Erdogan.  His solution is based on a lookup table, similar to the one I present. His solution is better than many others because it doesn’t involve business table changes, like adding a security code to each row, but still leaves tables unsecured if users access tables directly rather than through a given application.

The essence of the approach is to (1) use cross reference tables linking userid (or Active Directory group id) to business data values, and (2) protecting data tables behind table-valued functions that accept userid as a parameter and join it to the cross reference tables, returning only those rows that the user is permitted to see.

Using this solution can reduce security admin overhead and enable users to get to what they need without putting secure data into the wrong hands.