Row-Based Security
In the first two installments of my articles on SQL Server security, I describedserver level roles and flexible database-level roles. This final article talks about row-based security, specifically row-based security using stored procedures. Why would you want to use row-based security? Well, there are a lot of situations where you simply can’t use flexible roles and/or server level rows. When you have a web application that potentially has thousands upon thousands (if not millions) of users, it doesn’t make sense to create a SQL Server account for each user and have to maintain all of those users. Instead you need to take a look at how your data is structured and how the users will be interacting with it. Once you’ve done that you’ll probably come back to me and say ‘Okay, now what?’ Well, you probably won’t because odds are you haven’t actually met me. After taking a careful look at your data and cursing my attempts at humor, you will no doubt notice natural hierarchies and structures to your data. It’s possible to use these structures to control the access to your database. Of course there are some caveats to this approach that will become apparent along the way. Ignore those for a moment and dive right in! To get started, we’re going to go ahead and create a new database to play around with. Originally I had toyed with the idea of messing around with the usernames of people from twitter and creating a giant masterpiece of fiction involving secret identities and fighting crime. Then I remembered that I hadn’t done anything that creative since creative writing class in college, so I scrapped the idea and went with something a little bit more limited – sales contacts! Let’s go ahead and create that database:``` /* Set up database */ USE [master]; GO
CREATE DATABASE RowSecurity; GO
/* Create tables */ USE [RowSecurity]; GO
CREATE TABLE Users ( UserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Username VARCHAR(50) NOT NULL );
CREATE TABLE Regions ( RegionId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, RegionName VARCHAR(50) NOT NULL );
CREATE TABLE Sites ( SiteId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, RegionId INT NOT NULL REFERENCES Regions(RegionId), SiteName VARCHAR(50) NOT NULL );
CREATE TABLE UserRegions ( UserId INT NOT NULL REFERENCES Users(UserId), RegionId INT NOT NULL REFERENCES Regions(RegionId) );
Well, that’s not going to do us any good with some empty tables, so let’s add data:
/* Populate tables with sample data */
INSERT INTO Users (Username) VALUES (‘Alice’);
INSERT INTO Users (Username) VALUES (‘Bob’);
INSERT INTO Users (Username) VALUES (‘Charlie’);
INSERT INTO Users (Username) VALUES (‘David’);
INSERT INTO Regions (RegionName) VALUES (‘East’); INSERT INTO Regions (RegionName) VALUES (‘Central’); INSERT INTO Regions (RegionName) VALUES (‘West’);
INSERT INTO Sites (SiteName, RegionId) SELECT ‘New York City’, RegionId FROM Regions WHERE RegionName = ‘East’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Boston’, RegionId FROM Regions WHERE RegionName = ‘East’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Charleston’, RegionId FROM Regions WHERE RegionName = ‘East’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Chicago’, RegionId FROM Regions WHERE RegionName = ‘Central’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Detroit’, RegionId FROM Regions WHERE RegionName = ‘Central’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘St. Louis’, RegionId FROM Regions WHERE RegionName = ‘Central’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Seattle’, RegionId FROM Regions WHERE RegionName = ‘West’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Los Angeles’, RegionId FROM Regions WHERE RegionName = ‘West’;
INSERT INTO Sites (SiteName, RegionId) SELECT ‘Provo’, RegionId FROM Regions WHERE RegionName = ‘West’;
/* Create records for users and regions */ INSERT INTO UserRegions (UserId, RegionId) SELECT UserId, RegionId FROM Users CROSS JOIN Regions WHERE Username = ‘Alice’
INSERT INTO UserRegions (UserId, RegionId) SELECT UserId, RegionId FROM Users INNER JOIN Regions ON RegionName = ‘East’ WHERE Username = ‘Bob’
INSERT INTO UserRegions (UserId, RegionId) SELECT UserId, RegionId FROM Users INNER JOIN Regions ON RegionName = ‘Central’ WHERE Username = ‘Charlie’;
INSERT INTO UserRegions (UserId, RegionId) SELECT UserId, RegionId FROM Users INNER JOIN Regions ON RegionName = ‘West’ WHERE Username = ‘David’;
Wow, that’s a lot of SQL. Are you still reading? I hope so, because I wrote this all out in longhand and paid someone 15 bucks to transcribe it. If you want to quickly check out the structure of the data that we have thus far, you can use this handy query:
/* A query to check the data */
SELECT u.Username,
r.RegionName,
s.SiteName
FROM dbo.Users AS u
INNER JOIN dbo.UserRegions AS ur
ON u.UserId = ur.UserId
INNER JOIN dbo.Regions AS r
ON ur.RegionId = r.RegionId
INNER JOIN dbo.Sites AS s
ON r.RegionId = s.RegionId
ORDER BY Username, RegionName, SiteName;
From this query, it’s plain to see that Alice is the evil overlord of the sales empire and can see everything that’s going on within this fictitious organization. The other users are each within a single region. You’ll notice that I haven’t created any users in the database. As I mentioned earlier, this is a fictitious web application that supports many thousands of users. It simply isn’t feasible to create a SQL Server login for each and every user and maintain those users in addition to all of the other applications on all of the servers. (We’re highly paid DBAs at a **major** company, remember?) Instead, all of our users are connecting through a single web user account and authenticate against the database. Now, the way that we’re going to have to enforce security is on a row-by-row level. We can do this through either stored procedures or views. In this case, I’m going to talk about using stored procedures to create our row-based security scheme. So far, though, we only have enough data to show that we could, in theory, have things secured. There’s nothing to actually secure. Let’s create some data that needs secured. In our application, we have a business rule which states that a user can only see the sales contacts in their region or regions. First, let’s create our sales contacts and a place to put them:
/* Create sales contacts table */
CREATE TABLE SalesContacts (
SalesContactId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SiteId INT NOT NULL REFERENCES Sites(SiteId),
Name VARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(15) NOT NULL
);
/* Create sales contacts data Names generated from http://www.fakenamegenerator.com/ */ INSERT INTO SalesContacts VALUES (1, ‘Emma L. Atkins’, ‘339-221-1843’); INSERT INTO SalesContacts VALUES (2, ‘Linda R. Oneil’, ‘734-238-6974’); INSERT INTO SalesContacts VALUES (3, ‘Tristan P. Pineda’, ‘201-326-3782’); INSERT INTO SalesContacts VALUES (4, ‘Nicholas M. Garza’, ‘818-917-9238’); INSERT INTO SalesContacts VALUES (5, ‘Ronald C. Villalpando’, ‘603-537-9666’); INSERT INTO SalesContacts VALUES (6, ‘Helen M. Ross’, ‘603-664-8708’); INSERT INTO SalesContacts VALUES (7, ‘Patricia T. Haak’, ‘906-748-3668’); INSERT INTO SalesContacts VALUES (8, ‘Bryant J. Melton’, ‘859-328-7422’); INSERT INTO SalesContacts VALUES (9, ‘Justin J. Norton’, ‘206-835-5862’);
Disclaimer: If any of these names or phone numbers are yours, I’m sorry. The internet gave it to me. Now we have all of our data present. At this point it’s pretty easy to whip up a quick query to return only the appropriate data for a specific user:
CREATE PROCEDURE SalesContactsForUser(
@UserId INT
) WITH EXECUTE AS OWNER AS
SET NOCOUNT ON;
SELECT sc.SalesContactId, sc.Name, sc.PhoneNumber FROM SalesContacts AS sc INNER JOIN Sites AS s ON sc.SiteId = s.SiteId INNER JOIN Regions AS r ON s.RegionId = r.RegionId INNER JOIN UserRegions AS ur ON r.RegionId = ur.RegionId WHERE ur.UserId = @UserID;
* [SQL Server Security – Database Roles](http://facility9.com/2008/12/31/sql-server-security-database-roles/)
* [Flexible Database-Level Roles](http://facility9.com/2009/01/21/flexible-database-level-roles/)
