By: Aaron Bertrand | Comments (1) | Related: > SQL Server 2016
Problem
In previous tips, I introduced the new Row-Level Security (RLS) feature, and explained some of the limitations in the early CTPs. In the latter post, I talked about some security concerns with RLS, most importantly ways for users to obtain data from rows they should not be able to see (data leakage).
Solution
Now that SQL Server 2016 has been officially released, I wanted to address how some of these security concerns can be mitigated, and a couple of new ones that have surfaced in the meantime.
Policy Managers and Collusion
Nothing has changed in this area - if there are system administrators or other users who have the permissions required to modify security predicates (db_owner, db_ddladmin, etc.), then it stands to reason that those people could - even if only temporarily - make changes to the policy, or remove it altogether, such that other users of the system could see rows they weren't meant to see. Much of this can be detected through mechanisms like Extended Events, DDL triggers, or SQL Server Audit, but you would need to set up monitoring specifically for those actions.
CONTEXT_INFO()
One scenario where there is a vulnerability to data leakage is when you rely on information stored in CONTEXT_INFO() to determine eligible rows. Let's say, for example, you set a user's customer id to 5 on login (in a logon trigger, or through the application login process), and stuff that into CONTEXT_INFO(). It is very easy for a clever user to simply overwrite the data in CONTEXT_INFO(), so they could easily change it to 542 or 34 or 7.
-- in a logon trigger, store the value 5 in CONTEXT_INFO(): SET CONTEXT_INFO 0x00000005;
-- now the user comes along and casually changes that to 542: SET CONTEXT_INFO 0x0000021E;
If your security predicate blindly trusts the information in CONTEXT_INFO() and does no further validation, there's nothing stopping a malicious user from seeing another customer's rows.
This can be mitigated by using SESSION_CONTEXT() and sp_set_session_context instead of CONTEXT_INFO(). This allows you to set contextual information to read-only so, when the user logs in, you could set their customer id to 5 similar to before, but prevent it from being overwritten:
EXEC sys.sp_set_session_context @key = N'CustomerID', @value = 5, @read_only = 1;
If they try to change the value like before:
EXEC sys.sp_set_session_context @key = N'CustomerID', @value = 542;
They will get this error:
Cannot set key 'CustomerID' in the session context. The key has been set as read_only for this session.
Clever Ad Hoc Queries
There are ways that information can be inferred about rows in the table, even if the rows can't be read directly, if the user has the ability to craft their own queries. The example they used in the documentation is:
SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe';
If a divide-by-zero error were to occur, then the caller would know that John Doe has a salary of $100,000 (or, potentially, at least one person in the table does, since the calculation could be attempted before the filter was applied).
The good news: This query no longer yields a divide by zero error in SQL Server 2016 RTM; the RLS filter removes ineligible rows before the calculation is attempted. Let's try it. First, we'll create a table, a security predicate function, and a security policy:
CREATE TABLE dbo.Payroll
(
UserName nvarchar(32) PRIMARY KEY,
Salary int
);
GO
INSERT INTO dbo.Payroll(UserName, Salary)
VALUES (N'Madeline',100000), ('Nora',60000);
GO
CREATE FUNCTION dbo.SecurityPredicate
(
@UserName nvarchar(32)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
SELECT ok = 1 WHERE USER_NAME() = @UserName
);
GO
CREATE SECURITY POLICY dbo.SecurityPolicy
ADD FILTER PREDICATE dbo.SecurityPredicate(UserName)
ON dbo.Payroll WITH (STATE = ON, SCHEMABINDING = ON);
Now, we'll create two users, grant them SELECT permissions on the table, and have one try to read the other's rows using the side-channel attack from the documentation:
CREATE USER Madeline WITHOUT LOGIN; CREATE USER Nora WITHOUT LOGIN;
GRANT SELECT ON dbo.Payroll TO Madeline, Nora;
-- we get an error here when Madeline tries to -- use her own salary, as expected : EXECUTE AS USER = N'Madeline'; SELECT 1/(Salary-100000) FROM dbo.Payroll WHERE UserName = N'Madeline'; REVERT;
-- the mentioned attack fails, ignoring Madeline's row : EXECUTE AS USER = N'Nora'; SELECT 1/(Salary-100000) FROM dbo.Payroll WHERE UserName = N'Madeline'; REVERT;
The bad news: It is not difficult to construct a slightly different query that will cause SQL Server to try to perform the calculation before any ineligible rows have been filtered away:
EXECUTE AS USER = N'Nora'; SELECT UserName, Salary FROM dbo.Payroll WHERE UserName = N'Madeline' AND 1/(Salary-100000) < 0; -- this filter attempted first REVERT;
Now we get a divide by zero error. More importantly, Nora has just learned that Madeline's salary is exactly $100,000. And when they fix this one, there'll be some other way to coerce a revealing order of operations, like using a CASE expression with or without an aggregate.
SQL Server Execution Plans
Before we start looking into execution plans, let's continue with the example from the previous section, and add three more rows. We'll also give Nora SHOWPLAN rights
INSERT dbo.Payroll(UserName, Salary) VALUES(N'Bob', 120000),(N'Sue', 150000),(N'Tim', 110000); GO
GRANT SHOWPLAN TO Nora;
Now, let's add a meaningless column and create a simple index that we can use to see seek information:
ALTER TABLE dbo.Payroll ADD Fluff int; CREATE INDEX ForceLookup ON dbo.Payroll(Salary, Fluff);
And then we'll have Nora run the following query:
EXECUTE AS USER = N'Nora'; SELECT UserName, Salary FROM dbo.Payroll WITH (INDEX(2)) -- force the non-clustered index WHERE Salary > 100000; REVERT;
We see the following information in the execution plan. This actually reveals that where were four rows that matched the seek predicate, even though Nora was ultimately unable to read those rows due to the security predicate.
You could envision we could change the salary value and/or start adding other criteria to the where clause to whittle down who is making what.
But wait, there's more!
There's another way the execution plan can reveal information, though in this case it's not about the data in the table, but rather the definition of the security predicate function. Let's change our function to something a little more vulnerable, by adding conditions that check the application name or user name for certain patterns (this could be a way to give certain users or applications the ability to see all rows):
DROP SECURITY POLICY dbo.SecurityPolicy; GO
ALTER FUNCTION dbo.SecurityPredicate ( @UserName nvarchar(32) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT ok = 1 WHERE USER_NAME() = @UserName OR APP_NAME() LIKE N'%AdminApp%' OR USER_NAME() LIKE N'%_Admin' ); GO
CREATE SECURITY POLICY dbo.SecurityPolicy ADD FILTER PREDICATE dbo.SecurityPredicate(UserName) ON dbo.Payroll WITH (STATE = ON, SCHEMABINDING = ON);
To be sure, we can check that Nora can't see the definition of the function directly:
EXECUTE AS USER = N'Nora'; SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.SecurityPredicate')); REVERT;
Sure enough, this returns NULL. Now, if we run a simple query and generate a plan:
EXECUTE AS USER = N'Nora'; SELECT UserName, Salary FROM dbo.Payroll; REVERT;
We can hover over the Filter operator in the plan, and it shows us, essentially, a re-write of the predicate from the body of the security function! (Note: This information is also available in the XML.)
Now the user can spoof Management Studio by adding a custom connection string attribute to match the name pattern the function is looking for, or if they have ALTER ANY USER permissions, they could simply change their own username to match that pattern.
Summary
Row-level security can be a powerful feature, but without trying to sound like Chicken Little, it is important to understand some of these potential data leakage scenarios before you implement it as a primary security mechanism. If you have concerns around data leakage, it might be best to combine RLS with Always Encrypted, so that you can protect column data even from users who get around row-level protection.
Next Steps
- See these tips and other resources:
- SQL Server 2016 Row Level Security Introduction
- SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting
- SQL Server 2016 Row Level Security Example
- SQL Server 2016 Row Level Security Block and Filter Predicates Example
- Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT()
- SESSION_CONTEXT() (MSDN)
- SQL Server 2016 Tips
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
