mssqltips logo

How to Setup Row Level Security for SQL Server

By:   |   Updated: 2010-12-17   |   Comments (16)   |   Related: More > Security

Problem

I've been tasked to put together a row-level security model for a database where users are connecting with their Windows user accounts. I'm not sure how to go about this. How do I setup such a security model?

Solution

This is actually not as hard as it sounds, especially if your organization is making proper use of Windows groups in your Active Directory domain(s). For instance, let's consider a sample database that has employee information. Here's how we'd break down security:

  • The DBAs obviously have access to all the rows.
  • HR personnel also need access to all the rows.
  • Managers only have access to employee information for their departments.

This type of scenario is perfect for instituting a row-level security solution. So let's walk through how to do this.

First, let's set up some basic tables and load them with data.

USE MSSQLTips;
GO

-- Sample tables to use to demonstrate row-level security
-- We'll key off the DepartmentID as our filter
CREATE TABLE dbo.Department (
DepartmentID INT NOT NULL,
DepartmentName VARCHAR(30),
CONSTRAINT PK_Department PRIMARY KEY CLUSTERED (DepartmentID)
);
GO

CREATE TABLE dbo.Employee (
EmployeeID INT NOT NULL,
FirstName VARCHAR(30) NOT NULL,
MiddleName VARCHAR(30) NULL,
SurName VARCHAR(30) NOT NULL,
DepartmentID INT NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID),
CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentID) REFERENCES dbo.Department (DepartmentID)
);
GO

-- Sample data so we can verify the row-level security will work as we modeled it
INSERT INTO dbo.Department (DepartmentID, DepartmentName) VALUES (1, 'IT');
INSERT INTO dbo.Department (DepartmentID, DepartmentName) VALUES (2, 'HR');
INSERT INTO dbo.Department (DepartmentID, DepartmentName) VALUES (3, 'Marketing');
INSERT INTO dbo.Department (DepartmentID, DepartmentName) VALUES (4, 'Facilities');
INSERT INTO dbo.Department (DepartmentID, DepartmentName) VALUES (5, 'Accounting');
GO

INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (1, 'George', NULL, 'Washington', 1);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (2, 'John', NULL, 'Adams', 2);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (3, 'Thomas', NULL, 'Jefferson', 3);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (4, 'James', NULL, 'Madison', 4);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (5, 'James', NULL, 'Monroe', 5);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (6, 'John', 'Quincy', 'Adams', 1);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (7, 'Andrew', NULL, 'Jackson', 2);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (8, 'Martin', 'Van', 'Buren', 3);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (9, 'William', 'Henry', 'Harrison', 4);
INSERT INTO dbo.Employee (EmployeeID, FirstName, MiddleName, SurName, DepartmentID) VALUES (10, 'John', NULL, 'Tyler', 5);
GO

So we have 5 departments, with 2 employees per department. We'd expect a DBA or an HR person to see all 10 employees. We'd only expect a manager to see 2 employees. However, if we just give SELECT access to the tables, then a manager could query the whole table and get all the rows back. This isn't what we want.

We've got to build a mechanism on top of these tables. That mechanism has to somehow tie to the Windows groups to filter the data properly. So what we're going to need is some sort of security mapping table that ties our security to the department as well as a view that uses that security mapping table to properly filter the results. We'll use ownership chaining, granting SELECT permissions against the view, but not granting any permissions against the underlying tables. This will force the managers to come through the view, and if our view is written right, row-level security will be enforced.

The question then is, what should the security mapping table look like? We know we need DepartmentID, because we're filtering on department. We could use the Windows group name, but I prefer to create database roles that mirror what we'd expect to find. There's a couple of reasons for this. First, if I just want to test my model and I don't have the Windows groups yet, I can tie things to the database roles and add and remove users from the database roles to verify my security model is working. Second, if you switch domains between production and development, you won't have to worry about re-seeding your security mapping table, you'll just need to associate the right Windows security groups to the right database roles. You'll have to touch these Windows groups to make them users anyway, so this is probably the solution that requires the least amount of work.

For our example, we can create a database role for HR personnel, and one for each manager type. Since we're not doing a full blown implementation, let's create 1 for the HR personnel, which we'll call HR_User, and create several roles for different managers. Three roles should be sufficient to show us that row-level security is working. We also want to create a generic role, Manager, which is what we'll use to give permissions to select against the view, once we create it.

-- Create database roles to go along with the Windows groups
CREATE ROLE HR_User;
GO

CREATE ROLE IT_Manager;
GO

CREATE ROLE Marketing_Manager;
GO

CREATE ROLE Facilities_Manager;
GO

CREATE ROLE Manager;
GO

Now that we have our database roles, we need our security mapping table and we need it to be populated with some rows to match up the departments with the right database roles:

-- Create the security mapping table and populate it so we can enforce row-level security
CREATE TABLE dbo.SecurityMap (
DepartmentID INT NOT NULL,
RoleName NVARCHAR(255),
CONSTRAINT PK_SecurityMap PRIMARY KEY CLUSTERED (DepartmentID, RoleName)
);
GO

INSERT INTO dbo.SecurityMap (DepartmentID, RoleName) VALUES (1, 'IT_Manager');
INSERT INTO dbo.SecurityMap (DepartmentID, RoleName) VALUES (3, 'Marketing_Manager');
INSERT INTO dbo.SecurityMap (DepartmentID, RoleName) VALUES (4, 'Facilities_Manager');
GO

Finally, we need the view that uses the SecurityMap table to do the filtering:

-- Create a view which uses row-level security to filter the roles
-- Note the OR IS_MEMBER('HR_User') = 1 as well as the OR IS_MEMBER('db_owner') = 1
-- as part of the WHERE clause. If a user is a member of either of these roles,
-- then all the rows will be returned.
-- Otherwise, the only way a user will get a row back is if he/she is mapped to the
-- appropriate manager role for that department based on the SecurityMap table.
CREATE VIEW dbo.DepartmentEmployees
AS
SELECT Emp.FirstName, Emp.MiddleName, Emp.SurName, Dept.DepartmentName
FROM dbo.Department Dept
INNER JOIN dbo.Employee Emp
ON Dept.DepartmentID = Emp.DepartmentID
LEFT JOIN dbo.SecurityMap
ON Emp.DepartmentID = dbo.SecurityMap.DepartmentID
WHERE IS_MEMBER(dbo.SecurityMap.RoleName) = 1
OR IS_MEMBER('HR_User') = 1
OR IS_MEMBER('db_owner') = 1;
GO

GRANT SELECT ON dbo.DepartmentEmployees TO Manager;
GRANT SELECT ON dbo.DepartmentEmployees TO HR_User;

Note that the "magic" is being worked by the IS_MEMBER() function. If the user is a member of the role which matches up to the department based on the SecurityMap table, the user sees the row containing the employee information. If not, the only way the user sees the row is if the user is a member of the HR_User role or is a member of the db_owner role, both roles of which see all the data. If all three of those are false for a given row, then the user is not able to see the data on the employee. We can run a series of tests by creating users who are members of the various roles to verify row-level security is working:

-- Query view as DBA
SELECT * FROM dbo.DepartmentEmployees;

-- Query as HR_User
CREATE USER Nancy WITHOUT LOGIN;
GO

EXEC sp_addrolemember @membername = 'Nancy', @rolename = 'HR_User';
GO

EXECUTE AS USER = 'Nancy';
GO

SELECT * FROM dbo.DepartmentEmployees;
GO

REVERT;
GO

-- Query as Marketing Manager
CREATE USER Mark WITHOUT LOGIN;
GO

EXEC sp_addrolemember @membername = 'Mark', @rolename = 'Marketing_Manager';
EXEC sp_addrolemember @membername = 'Mark', @rolename = 'Manager';
GO

EXECUTE AS USER = 'Mark';
GO

SELECT * FROM dbo.DepartmentEmployees;
GO

REVERT;
GO

-- Query as IT Manager
CREATE USER Sydney WITHOUT LOGIN;
GO

EXEC sp_addrolemember @membername = 'Sydney', @rolename = 'IT_Manager';
EXEC sp_addrolemember @membername = 'Sydney', @rolename = 'Manager';
GO

EXECUTE AS USER = 'Sydney';
GO

SELECT * FROM dbo.DepartmentEmployees;
GO

REVERT;
GO

-- Query as Facilities Manager
CREATE USER Leonard WITHOUT LOGIN;
GO

EXEC sp_addrolemember @membername = 'Leonard', @rolename = 'Facilities_Manager';
EXEC sp_addrolemember @membername = 'Leonard', @rolename = 'Manager';
GO

EXECUTE AS USER = 'Leonard';
GO

SELECT * FROM dbo.DepartmentEmployees;
GO

REVERT;
GO

And there you have a simple way of tying row-level security to the database roles. If the Windows groups (or even the Windows users) are placed in the appropriate database roles, when they query the DepartmentEmployees view, they'll get the filtered results as appropriate for their department.

Next Steps
  • Try implementing a solution like this to limit the data that particular users can access without having to write code for each scenario.
  • Setup this up using Windows groups or Windows users to use Windows Authentication


Last Updated: 2010-12-17


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, January 29, 2019 - 6:40:26 PM - Chris Back To Top

Do you have any suggestions for getting this to work cross-databases?

I can't add additional tables or views to a particular database that I'm working with (long story). So the intention was to stand up a "Restricted" database and have views in there reading out of tables in the original database and filtering the rows.

This works (in theory if the user is given read access to the original tables). However I cannot figure out how to do this without giving the user read permissions to the tables on original database. Because they can just go in there and read the tables directly and unrestricted. So this only seems to work if it's all running out of the one database.

Is it possible to grant some kind of select permissions on the original tables during execution of the view? Or is there any other suggestion available? 


Wednesday, March 08, 2017 - 4:34:36 PM - Sandy Back To Top

 Excellent article on row level security.


Thursday, October 10, 2013 - 9:33:05 AM - K. Brian Kelley Back To Top

IT_Manager as a role doesn't have access to the view. Note that in the case of Sydney, Sydney is being added to the Manager role as well.


Thursday, October 10, 2013 - 5:51:13 AM - Kenny_I Back To Top

I have create db and tables with your scripts.

I have Added AD user domain\TestUser to Role IT_Manager.

However I'm not able to make Select statements "The SELECT permission was denied on the object 'DepartmentEmployees', database 'RoleDB', schema 'dbo'."
 
When I set user to HR, then I'm able to see all rows! That right, but I would like to try row level security.

What could be wrong with Role and rights of TestUser?


Tuesday, March 12, 2013 - 11:42:03 AM - Angela Back To Top

thanks much, will give that a whirl


Monday, February 25, 2013 - 9:52:17 AM - K. Brian Kelley Back To Top

Angela, the problem is GRANT doesn't work this way. GRANT is explicitly against an object (and potentially a column or columns) and explicitly to a security principal (user or role). For instance:

 

GRANT SELECT ON OBJECT::V2 TO Staff;

 

You can use dynamic T-SQL to build these GRANT statements for you, and that's probably what you want to do. For instance:

 

SELECT 'GRANT SELECT ON OBJECT::V2 TO' + [V1.user]
FROM V1
WHERE StudentFlag = 1
  AND FacFlag = 0
  AND StaffFlag = 0;

 


Friday, February 22, 2013 - 2:18:18 PM - Angela Back To Top

I'm trying to tweak your approach to existing structures I have to work with. We have a tbl already that has every user in it and then flags for roles (Staff, Faculty, Student) they can be one or more (V1). I have a view that contains demographic data (V2). I want people who are students to only have select rights on their row. I want Fac and Staff to have select on all. I'm hoping to do the grant statements in my view SQL to do something to the affect of:

grant select V2.* where nt_username = view.username and nt_username in (select user from V1 where Studentflag=1 and FacFlag=0 and StaffFlag=0)

grant select V2.* where nt_username in (select user from V1 where Studentflag=0 or FacFlag=0)

I'm new at this so any help is greatly appreciated. I do already have roles in the db for students, faculty and staff and have given them all read rights to V2.


Monday, February 18, 2013 - 11:55:36 AM - raj Back To Top

thanks alot....


Wednesday, September 26, 2012 - 9:49:13 AM - Me Back To Top

I'm missing something - it seems to me that the above assumes each environment is on a separate domain.  i.e. the roles mentioned aren't environment specific i.e. IT_Manager will appear as a role across Development, UAT, and Live - so it seems you need to create a mapping table that maps each environment specific role to the SQL Server role - to do this creates an overhead - a rather large (unworkable) overhead - it means if I bring down Live to UAT which often happens I need to create a post deployment script (which may or may not be run by DBA) to replace the Live AD role with UAT roles


Monday, July 18, 2011 - 2:58:14 AM - Panco Cheong Back To Top

Great Tips!

However, it introduces an security concern. In an application, user can insert/update the information. If the application use the user's account to make connection to the database, this implies the user's account has the permission to do the update/insert record.

Then, the user can directly connect to the database and make the change, in other words, the user can by-pass the application and make change. How to protect the database and avoid user from connecting to the database directly?


Tuesday, January 18, 2011 - 1:02:27 PM - Alex Lopez Back To Top

Hi Brian,

 

Thanks for the solution. It is a very interesting example of where to use IS_MEMBER().

 

As for the implementation, you have to be careful because it is very likely that the view will return duplicated values when a role has access to multiple departments. For instance, if you add a new row (one more than the three in your example) to the table Security Map granting access to "Marketing_Manager" on the "Department 1 - IT" and at the same time you add the user Mark to the role "IT_Manager" (i.e., Mark will be a member of the roles IT_Manager and Marketing_Manager), you will get duplicate rows:

 

1.

INSERT INTO dbo.SecurityMap (DepartmentID, RoleName) VALUES (1, 'Marketing_Manager');

 

2.

EXEC sp_addrolemember @membername = 'Mark', @rolename = 'IT_Manager';

 

 

3.

EXECUTE AS USER = 'Mark';
GO

SELECT * FROM dbo.DepartmentEmployees;
GO


this is the resultset:

FirstName MiddleName SurName DepartmentName

 

------------------------------ ------------------------------ ------------------------------ ------------------------------

 

George NULL Washington IT

 

George NULL Washington IT

 

Thomas NULL Jefferson Marketing

 

John Quincy Adams IT

 

John Quincy Adams IT

 

Martin Van Buren Marketing

 

(6 row(s) affected)

 

 

Cheers,

 

Alex


Wednesday, December 22, 2010 - 10:53:05 AM - Mark Hayter Back To Top

Thanks

I was actually looking at the wrong thing before I posted

http://msdn.microsoft.com/en-us/library/ms138529.aspx

Thanks

Mark


Monday, December 20, 2010 - 10:06:24 PM - K. Brian Kelley Back To Top

That syntax is new to SQL Server 2008. I try to write these tips for both 2005 and 2008. Except for the CREATE USER, this will work for 2000, as well, but unless it's related to auditing, I try to focus on the latest two.


Monday, December 20, 2010 - 7:33:46 AM - Illya Back To Top

Cool tips, thanks. Just as a minor change in the code, why don't you use INSERT statement as INSERT ... VALUES (...), (...), ..., (...)? It makes the code a little simplier.


Saturday, December 18, 2010 - 12:48:15 PM - K. Brian Kelley Back To Top

Mark,

  I don't think you have much to worry about. It's not deprecated in SQL Server 2008 and I just checked the online docs for the Denali CTP. It's not marked as deprecated there, either: http://msdn.microsoft.com/en-us/library/ms186271(v=SQL.110).aspx

 


Friday, December 17, 2010 - 10:01:32 AM - Mark Hayter Back To Top

great read thanks....do you know what the alternative to is_member() will be?  I think SQL is making that redundant in future versions.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools