In the first two installments of my articles on SQL Server security, I described server 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;
Now, this is a pretty simple query. In the real world, we might see a large number of joins in our query just to get from the security data to the actual securables. To make these queries easier, you might want to use a materialized, or indexed, view to replace the bulk of the query logic. An indexed view is a view that has been materialized to disk and stored. Basically, you tell SQL Server ‘I want to keep this info on disk because this query is annoying and I know it’s not going to change much.’ SQL Server duly obeys you, and that’s all she wrote. The best information to store in a materialized view is information that rarely changes – security information, for example.
Obviously, in a real-world scenario this situation would be much more complicated with departments within a company coming into play as well as territories within a region. However, if you carefully look at the type of data you’re storing and examine it, you will no doubt find ways where you can create materialized views or pre-materialize queries into tables on a regular schedule in order to meet your performance needs.
Row-based security is not the daunting task that it first appears to be. There are some other articles available online that make this topic appear a lot more complicated than it really is. Rest assured, this is something that is within reach, highly performant, and very easy to implement once you understand the basic principles behind it.
(For your convenience, all of the code in this article is available in one easy to download file: row-based-security.sql)
If you missed the first two posts in this series on SQL Server Security, you can find them at:
