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


Beware of Side-Channel Attacks in Row-Level Security in SQL Server 2016

By:   |   Read Comments (1)   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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).


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.


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:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1
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:


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
INSERT INTO dbo.Payroll(UserName, Salary)
  VALUES (N'Madeline',100000), ('Nora',60000);
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:

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:

SELECT UserName, Salary
  FROM dbo.Payroll
  WHERE UserName = N'Madeline'
  AND 1/(Salary-100000) < 0;  -- this filter attempted first

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);

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:

SELECT UserName, Salary
  FROM dbo.Payroll WITH (INDEX(2)) -- force the non-clustered index
  WHERE Salary > 100000;

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.

Showplan XML reveals number of rows read before RLS filter.

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;
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:


Sure enough, this returns NULL. Now, if we run a simple query and generate a plan:

SELECT UserName, Salary FROM dbo.Payroll;

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.)

Showplan XML reveals security predicate details.

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.


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

Last Update:

signup button

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

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    Notify for updates 

SQL tips:

*Enter Code refresh code     

Monday, July 25, 2016 - 2:05:51 PM - Thom Holderman Back To Top

 or just use

SELECT 1/(SALARY-100000.0001)

since it's unlikely anyone is paid in 10,000ths of a dollar 

Learn more about SQL Server tools