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

 

SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting


By:   |   Last Updated: 2015-09-02   |   Comments (2)   |   Related Tips: More > SQL Server 2016

Problem

In the first part of this tip, Use Row-Level Security in SQL Server 2016, Part 1, I described reasons for using Row-Level Security, and showed some simple examples of applying row-level filtering using either the SQL account name or CONTEXT_INFO(). To avoid distraction, I left out some important details that you should probably be aware of before proceeding with implementing this feature.

Solution

There are several limitations in the first iteration of Row-Level Security. In this tip, I will provide some information about those, as well as some things you can do to mitigate them (in some cases). Thankfully, several of the issues outlined below are only temporarily and will be fixed at some point during the CTP cycles; but you should still be aware of most of them even if you don't touch this feature until after SQL Server 2016 goes RTM.

SQL Server Row Level Security Limitations

SCHEMABINDING

I mentioned in the first part of this tip that the predicate function must be created WITH SCHEMABINDING. If you create a function without this setting and then try to bind it to a security policy, you will get the following error:

Msg 4513, Level 16
Cannot schema bind security policy 'policy'. 'dbo.function' is not schema bound.

Not only does this prevent you from accessing local catalog views and DMVs, which I mentioned previously, but you also won't be able to implement security logic based on lookup tables that are stored in other databases. This means that if you have access control lists stored centrally (elsewhere), you'll have to replicate to this database, or move the central data and point all other references here using views, synonyms, or updated queries.

Indexed Views

Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:

Msg 33266, Level 16
The index on the view 'view' cannot be created because the view is 
referencing table 'table' that is referenced by a security policy.

And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:

Msg 33265, Level 16
The security policy 'policy' cannot have a predicate on table 'table'
because this table is referenced by the indexed view 'view'.

Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.

In-Memory OLTP

In current builds (at the time of writing, the most recent build was CTP 2.2), In-Memory OLTP tables are not supported. I suspect that this feature should be fully supported by the time we reach RTM, as Microsoft has been going to great lengths to make core engine features like this (see Columnstore as well) compatible with all new features.

Full-Text Indexes

Full-text indexes are supported, but it is important to keep in mind that the indexes are based on unfiltered data, so it is possible that data leakage can occur in spite of the security policy over the table. The policy won't prevent otherwise inaccessible rows from being searched.

SQL Server Row Level Security Performance Impact

For SELECT queries, there is definitely an observable change to the execution plan in order to filter. In most cases you will see a left semi-join against whichever table(s) are used in the predicate function.

Here are two plans for the same SELECT * query, the first was run without a security policy in place, and the second had row-level filtering applied:

Comparing plans: with and without security policy

While it shouldn't be surprising that the logic and operators in the inline table-valued function gets folded into the execution plan for the main query, this should illustrate that it is crucially important to optimize the predicate function in order to minimize the impact it can have. How it ultimately affects the overall performance of your workload depends on a variety of factors, including query patterns, the logic in the function, and other factors.

It is important to note, too, that users outside of sysadmin, db_owner, db_ddladmin, and the table owner do not have direct access to DBCC SHOW_STATISTICS (since that could lead to information leakage), so estimates might be way off in some cases.

It goes without saying that you should thoroughly test your application(s) for behavior and performance regressions.

SQL Server Row Level Security Troubleshooting

By default, even dbo and sysadmin do not have access to all rows in the table. If you need to troubleshoot queries, replicate issues, or even just casually inspect the data, you'll want to make sure the predicate function allows you in, or that it is possible to impersonate a user that has full access. Again, please be sure to thoroughly test all aspects of your application(s), including edge cases and panic modes.

In addition, it is quite possible that a complex execution plan for a relatively simplistic query will cause confusion. It will be very hard for users to piece together why the objects in the predicate function are wrapped into the plan, when neither the function nor those objects are referenced in the query text.

SQL Server Row Level Security Data Leakage

There are multiple ways that users can get around the Row-Level Security (in fact, all along, I've been a little hesitant to push this as a security feature just yet). I already mentioned that full-text indexes could present a problem, but here are a couple of other scenarios to keep in mind - based on the users, schema, and policies created in part 1 - several of which are deemed "side-channel attacks":

Policy Managers and Collusion

As mentioned before, even dbo and sysadmin do not have access to the table data unless explicitly granted through the predicate function. However, obviously, if the person responsible for managing security policies also has access to modifying the functions used by those policies, then they could easily manipulate the logic to broaden their reach (this can easily be monitored using DDL triggers), or collude with other users to gain visibility into the rows that user can see. They could also simply disable the policy temporarily (though such actions can be audited). There is a pretty lengthy note about the malicious security policy manager in the documentation.

CONTEXT_INFO

In part 1, I talked about how a middle-tier or web application could implement row-level filtering, even when they use a common login for all users, by passing user-specific attributes through CONTEXT_INFO(). The problem here, of course, is that if any user has ad hoc query access, they can spoof CONTEXT_INFO, too. So the user I created previously, Peon, could see the data for any user they like, using the following code:

EXECUTE AS USER = N'Peon';
GO

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

REVERT;
GO

One potential solution here would be to add dynamic data masking, column-level encryption, or some kind of hashing / obfuscation / salting to the rep's name, so a malicious user wouldn't know what to set the CONTEXT_INFO() to.

But generally, you should also control access to this data through stored procedures and application code, so that ad hoc direct access is not supported. (I used ad hoc direct access in these tips for ease of demonstration, not as a best practice.)

Clever ad hoc queries

While a query on its own can't return rows the user can't access, it can be written in such a way that the user could deduce information about those rows. In the following example, the user Peon can determine which Reps have at least one account with AnnualFees > $75,000:

EXECUTE AS USER = N'Peon';
GO

DECLARE @i INT = 1;

BEGIN TRY
  SELECT * FROM dbo.Accounts
    WHERE CASE WHEN RepID = @i THEN 
      CASE WHEN AnnualFees > 75000 
        THEN CONVERT(INT, 'x') -- designed to raise an exception
      END END = 1;
END TRY
BEGIN CATCH
  PRINT N'Rep ' + RTRIM(@i) + N' has an account with fees > $75,000.';
END CATCH
GO 

REVERT;
GO

If you put a loop around it, incrementing @i, this batch will print a line for each rep that has an account with AnnualFees > $75,000. This works because some expressions can be evaluated before the row-level filtering happens, so it is easy to iterate through and construct clauses that can help you infer a lot of information about the other rows in the table. (This example is pretty benign, but imagine an HR table where you can see your own salary; with ad hoc query access and enough time and patience, this hole could allow you to figure out everyone else's salary too.)

Arguably, Peon should never have been granted SELECT access in the first place, but keep in mind that any other rep could implement the same type of attack. Hopefully, this type of exploit will be closed up before RTM.

Inserting invalid rows

Another note is that, in current builds, write access is not blocked by the policy - so if I can write to the table, I could inadvertently (or even intentionally) insert a row for a different rep:

GRANT INSERT, UPDATE ON dbo.Accounts TO Rep1;
GO

EXEC('INSERT dbo.Accounts(AccountID,AnnualFees,RepID) 
  VALUES(99,50000,2);') AS USER = N'Rep1';

I've just inserted a row I can't even see (you can validate with the following):

EXEC('SELECT * FROM dbo.Accounts WHERE AccountID = 99;') AS USER = N'Rep1';
EXEC('SELECT * FROM dbo.Accounts WHERE AccountID = 99;') AS USER = N'Rep2';

This example is of little value in this case, but it is still worth noting. In a more sensitive environment, being able to insert rows that will end up being seen by another customer or tenant can be a very serious problem.

With that all said, before RTM, we should see the capability extended such that inserts will also be subject to row-level security policies. For now, you could protect the table from rogue or accidental inserts using check constraints or instead of triggers that mimic the logic used in the relevant predicate function, or simply controlling inserts through stored procedures that don't allow user overrides of key tenant columns.

In any case, even in current builds, other DML is already protected; I can't update or delete a row I can't see in the first place. No change happens here, for example:

EXEC('UPDATE dbo.Accounts SET AnnualFees*=2 WHERE AccountID = 99;') AS USER = N'Rep1';
EXEC('SELECT * FROM dbo.Accounts WHERE AccountID = 99;') AS USER = N'Susan';

Change Data Capture and Change Tracking

Both of these features can expose rows to unauthorized users even if a row-level security policy protects that data in the source table. See the documentation for details and other potential data leakage scenarios.

Summary

While Row-Level Security does provide the ability to create elaborate access control with minimal coding and application change, some of these limitations, impacts, and vulnerabilities are very important to keep in mind as you consider implementing the feature to protect your data.

Next Steps


Last Updated: 2015-09-02


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.



    



Thursday, September 15, 2016 - 12:13:31 PM - Aaron Bertrand Back To Top

 

Dinesh, yes, those things implied "will not be supported by RLS" not that the feature would suddenly disappear, but the content has been updated to be more clear.


Monday, July 25, 2016 - 6:38:18 AM - Dinesh Back To Top

 

 "Change Data Capture will not be supported in SQL Server 2016. " You may want to rewrite that w.r.t RLS


Learn more about SQL Server tools