Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

SQL Server 2016 Row Level Security Introduction


By:   |   Last Updated: 2015-08-31   |   Comments (3)   |   Related Tips: More > SQL Server 2016

Problem

At my previous job, we built multi-tenant software-as-a-service. This meant we had to store data for multiple customers and protect their data from each other, even if it was in the same table, as well as support data protection within a single customer, and agencies that were able to manage multiple customers. We had to build our own access control system within the database and application, and spent as much time implementing, maintaining, and troubleshooting those parts of the code as we did on the parts that our customers actually used. It would have been great if there were some high-level feature in SQL Server that could help implement row-granularity access control without having to write and debug all of that code.

Solution

SQL Server 2016 introduces a new feature called Row-Level Security, which can simplify much of the work required in earlier versions. The goal is to provide per-row read and write access control, based on attributes of the executing user (such as role or department), with minimal schema, application, or query changes. In previous versions, you would typically implement this using views over your own access control table, or just applying WHERE clauses to queries, both of which can introduce a lot of complexity.

This tip walks through basic Row-Level Security configuration, shows some examples, and explains limitations, all based on the most recent build at the time of writing (CTP 2.2). You can play with this feature by downloading the latest SQL Server 2016 CTP or registering for a trial of Azure SQL Database v12.

SQL Server Row-Level Security Concepts

There are three core concepts used in Row-Level Security:

  • Predicate function - An inline table-valued function that implements access control logic (for example, returning a row or not depending on the principal_name, role, or other attributes of the calling user).
  • Security predicate - Glues a predicate function to a table (for example, applying a function that checks for rep name or the rep manager role to the Accounts table).
  • Security policy - A set of security predicates (for example, you might have an Account policy that applies multiple security predicates to Account-related tables, and an HR policy that applies several security predicates to various HR tables).

SQL Server Row-Level Security Example

To illustrate with an example, let's assume we have a table of Accounts and a table of AccountReps, where an Account belongs to a single rep, and that rep should only be able to see their own Accounts (and their own AccountRep details). We also have a RepManager, Susan, who should be able to see the rows belonging to all of the reps, and a user named Peon, who shouldn't be able to see anything.

CREATE TABLE dbo.AccountReps
(
  RepID            INT,
  SQLPrincipalName SYSNAME,
  CONSTRAINT PK_AccountReps PRIMARY KEY(RepID),
  CONSTRAINT UQ_PrincipalName UNIQUE(SQLPrincipalName)
);
GO

CREATE USER Rep1 WITHOUT LOGIN;
CREATE USER Rep2 WITHOUT LOGIN;
CREATE USER Rep3 WITHOUT LOGIN;
CREATE USER Peon WITHOUT LOGIN;
CREATE USER Susan WITHOUT LOGIN;

CREATE ROLE RepManagers;
ALTER ROLE RepManagers ADD MEMBER Susan;
GO

INSERT dbo.AccountReps(RepID, SQLPrincipalName)
VALUES(1, N'Rep1'),(2, N'Rep2'),(3, N'Rep3');
GO

CREATE TABLE dbo.Accounts
(
  AccountID INT,
  AnnualFees INT,
  RepID INT NOT NULL,
  CONSTRAINT PK_Accounts PRIMARY KEY(AccountID),
  CONSTRAINT FK_AccountReps FOREIGN KEY(RepID)
    REFERENCES dbo.AccountReps(RepID)
);
GO

INSERT dbo.Accounts(AccountID,AnnualFees,RepID) VALUES
(1,55000,1),(2,25000,1),(3,65000,1),(4,35000,2),(5,82000,2);
GO

GRANT SELECT ON dbo.Accounts    TO Peon, Rep1, Rep2, Rep3, Susan;
GRANT SELECT ON dbo.AccountReps TO Peon, Rep1, Rep2, Rep3, Susan;

Now, we need the function that can help determine row-level access. Note that the function requires SCHEMABINDING (to prevent changes to underlying tables); unfortunately, this prevents direct access to sys.database_principals or other catalog views/DMVs, but many built-ins are supported, such as USER_NAME(), DATABASE_PRINCIPAL_ID(), and IS_MEMBER().

CREATE FUNCTION dbo.LimitAccountAccess(@RepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT [Go] = 1 FROM dbo.AccountReps
      WHERE 
      (
	    RepID = @RepID 
		AND SQLPrincipalName = USER_NAME()
      )
      OR IS_MEMBER(N'RepManagers') = 1
      OR IS_SRVROLEMEMBER(N'sysadmin') = 1
  );

Given a RepID, this function returns a row from the AccountReps table if either (a) the RepID matches and the principal name matches (and if using Windows authentication, you could use some of the other built-in functions), (b) the user is in the RepManagers database role, or (c) the user is in the server-level sysadmin role. We could have also stored the principal_id in the table, for example, and checked it against DATABASE_PRINCIPAL_ID() - while this would insulate the function and policy from SQL account renames, it would not insulate them from account drop/create (it's also easier to identify a SQL account by its name than by its arbitrary surrogate key from sys.database_principals).

The logic can be a lot more complicated than that, of course. There could be a hierarchy of reps and rep managers, or rep managers who only oversee certain reps, or reps with seniority who can see all others, or a variety of other required scenarios. The function above is simple for brevity, not necessarily illustrative of an exhaustive security policy.

Next, we need a policy to apply those predicates to our tables. I wrote the function so that it could be used against both of our tables; in reality, a slightly simpler function could be used separately against the AccountReps table, since all that would have to match for a given rep is the principal name.

CREATE SECURITY POLICY AccountAndRepPolicy
ADD FILTER PREDICATE dbo.LimitAccountAccess(RepID) ON dbo.AccountReps,
ADD FILTER PREDICATE dbo.LimitAccountAccess(RepID) ON dbo.Accounts
WITH (STATE = ON);

Now that the predicates are bound to the tables, you can immediately see how if affects the output of simple queries, without having to change them:

-- as sysadmin, we see all 5 account rows and all 3 account rep roles
SELECT USER_NAME(),* FROM dbo.Accounts;
SELECT USER_NAME(),* FROM dbo.AccountReps;

-- Susan also sees all 5 accounts and all 3 reps
EXEC (N'SELECT USER_NAME(),* FROM dbo.Accounts')    AS User = N'Susan';
EXEC (N'SELECT USER_NAME(),* FROM dbo.AccountReps') AS User = N'Susan';

-- Rep1 sees their three accounts and their own row in the reps table
EXEC (N'SELECT USER_NAME(),* FROM dbo.Accounts')    AS User = N'Rep1';
EXEC (N'SELECT USER_NAME(),* FROM dbo.AccountReps') AS User = N'Rep1';

-- Rep2 sees their two accounts and their own row in the reps table
EXEC (N'SELECT USER_NAME(),* FROM dbo.Accounts')    AS User = N'Rep2';
EXEC (N'SELECT USER_NAME(),* FROM dbo.AccountReps') AS User = N'Rep2';

-- Rep3 sees no accounts and only their own row in the reps table
EXEC (N'SELECT USER_NAME(),* FROM dbo.Accounts')    AS User = N'Rep3';
EXEC (N'SELECT USER_NAME(),* FROM dbo.AccountReps') AS User = N'Rep3';

-- Peon sees no accounts or reps
EXEC (N'SELECT USER_NAME(),* FROM dbo.Accounts')    AS User = N'Peon';
EXEC (N'SELECT USER_NAME(),* FROM dbo.AccountReps') AS User = N'Peon';

SQL Server Row-Level Security for Multiple Users using the same Login

Now, what if the users are indistinguishable, for example a web or middle-tier application that uses the same common SQL authentication account? For example:

CREATE USER sqlapp WITHOUT LOGIN;
GRANT SELECT ON dbo.Accounts TO sqlapp;

You might consider changing the function to use HOST_NAME(), which might be possible within an office, but not if users are connecting anonymously over the Internet. Also, be aware that this can easily be spoofed in the connection string.

The recommendation here is to have the application store user-specific information using CONTEXT _INFO(). This also demonstrates swapping out the function used (you can't alter a function that is currently referenced by a security policy, so an easy workaround is to create a new function, and then alter the policy).

CREATE FUNCTION dbo.LimitAccountAccess2(@RepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT [Go] = 1
      FROM dbo.AccountReps
      WHERE 
      (
        RepID = @RepID 
        AND SQLPrincipalName = CONVERT(SYSNAME, CONTEXT_INFO())
      )
      OR IS_MEMBER(N'RepManagers') = 1
  );
GO

ALTER SECURITY POLICY AccountAndRepPolicy
ALTER FILTER PREDICATE dbo.LimitAccountAccess2(RepID) ON dbo.AccountReps,
ALTER FILTER PREDICATE dbo.LimitAccountAccess2(RepID) ON dbo.Accounts;

So now you can populate CONTEXT_INFO() using the user_name from the application or session variables. In this example, the application knows that the current user is Rep1, so sets CONTEXT_INFO() accordingly:

EXECUTE AS USER = N'sqlapp';
GO

DECLARE @ci VARBINARY(128) = CONVERT(VARBINARY(128),N'Rep1'); 
SET CONTEXT_INFO @ci;
SELECT USER_NAME(), * FROM dbo.Accounts;
GO

REVERT;

Like when we authenticated directly as Rep1, the application will return the three account rows that belong to Rep1.

Yes, CONTEXT_INFO() can also be spoofed, but that would require direct ad hoc query access, which is potentially a little less likely than having access to a configuration file, particularly for groups like QA who may have a compiled copy of the application on their workstations or in a test environment. I'll show an example of spoofing CONTEXT_INFO() in Part 2.

Summary

Row-Level Security provides a way to apply granular control and filtering without a hefty investment in schema, query changes, or application logic. There are some limitations though, as well as some impacts to performance and vulnerabilities to information leakage, which I will cover in Part 2.

Next Steps


Last Updated: 2015-08-31


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

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, October 17, 2017 - 9:26:13 AM - Thiago SP Back To Top

Hi Aaron: primarily thanks for this tutorial. 

Just one doubt: isn't SESSION_CONTEXT more appropriate to store session information than CONTEXT_INFO? Or there are exceptions? 

 


Thursday, September 03, 2015 - 8:35:25 AM - Aaron Bertrand Back To Top

Thanks Andy, good catch, sorry about that. Bad transcription.


Wednesday, September 02, 2015 - 4:04:15 PM - Andy Back To Top

Thanks for the introduction!  Just FYI - you have a small typo in your code.  There is no FILTER PREDICATE dbo.LimitAccountAccess3, should be 2.  Thanks again.


Learn more about SQL Server tools