By: Aaron Bertrand | Updated: 2015-08-31 | Comments (3) | Related: > 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
- Download the latest SQL Server 2016 CTP (or register for a trial of Azure SQL Database v12, where this feature appeared first).
- Try out Row-Level Security in scenarios where it may seem useful.
- Stay tuned for Part 2 of this tip, where I will go over some of the limitations and gotchas.
- See these related tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist 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 also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-08-31
