By: Aaron Bertrand | Updated: 2015-09-02 | Comments (4) | Related: > 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:
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;
GOIf 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
- 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.
- 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-09-02
