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.