Row-Based Security
From SQLServerPedia
|
This 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. After taking a careful look at your data, 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. To get started, create a new database for testing: /* 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) ); Next, 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';
NSERT 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';
If you want to quickly check out the structure of the data thus far, here is a 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 the user "Alice" can see everything that’s going on within this fictitious organization. The other users are each within a single region. Notice that there are no users created in the database. As 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. Instead, all of our hypothetical users are connecting through a single web user account and authenticate against the database. Now, the way to enforce security is on a row-by-row level. This can be accomplished through either stored procedures or views. In this case, we’ll investigate 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. The next step is to create some data that needs to be secured. In this application, there is a business rule which states that users can only see the sales contacts in their region or regions. First, create 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'); Now all data is present. Here is the 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;
This is a pretty simple query. In a real-world scenario, you might see a large number of joins in this 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." 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 and is something that is within reach and easy to implement once you understand the basic principles behind it. Author Credits This article was adapted from the last in a series of blog posts by Jeremiah Peschka. Jeremiah Peschka is a SQL Server developer in the Columbus, OH area with HMB Information System Developers. Jeremiah has broad IT experience in systems administration, web design, application development, and database development spread across diverse platforms. In his current role, Jeremiah specializes in developing solutions based on the SQL Server platform. Jeremiah is currently the Group Leader for CBusPASS - the Columbus chapter of PASS. His online presences include:
Related Reading |