Limiting access to data using Row-Level Security
To satisfy compliance standards, internal regulations or basic security principles, applications often need to limit a user’s access to only certain rows of data in a database. For example:
- An oil and gas exploration application might restrict an analyst’s access to well production data based on the analyst’s region and role.
- A healthcare application might restrict a doctor’s access to patient data based on the doctor’s staffing assignments.
- A multitenant application with a “shared database, shared schema” tenancy model needs to prevent tenants from accessing data that does not belong to them.
Traditionally, customers have implemented their row-level access logic using SQL views or customized application code. But these workarounds can introduce problems: Views are decentralized, susceptible to runtime errors and difficult to maintain during application upgrades. And customized application code is not only difficult to maintain as your codebase grows, but also impossible in scenarios where you don’t own the application code (e.g., commercial off-the-shelf software).
Row-Level Security (RLS), a new programmability feature available in Azure SQL Database and SQL Server 2016, solves these problems by centralizing your row-level access logic within the database. As your application grows, RLS helps you maintain a consistent data access policy and reduce the risk of accidental data leakage.
How it works
RLS is a form of “predicate-based access control” — it works by automatically applying a security predicate to all queries on a table. The predicate determines which users can access which rows. For example, a simple predicate might be, “WHERE SalesRep = CURRENT_USER”, while a complicated predicate might include JOINs to look up information in other tables.
There are two types of security predicates:
- Filter predicates silently filter SELECT, UPDATE and DELETE operations to exclude rows that do not satisfy the predicate.
- Block predicates explicitly block INSERT, UPDATE and DELETE operations that do not satisfy the predicate.
To add a security predicate on a table, you first need an inline table-valued function that defines your access criteria. Then, you create a security policy that adds filter and block predicates on any tables you like, using this function. Here’s a simple example that prevents sales representatives from accessing rows in a customer’s table that are not assigned to them:
CREATE FUNCTION dbo.customerPredicate(@SalesRepName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE @SalesRepName = USER_NAME() OR USER_NAME() = ‘Manager’
go
CREATE SECURITY POLICY dbo.customerAccessPolicy
ADD FILTER PREDICATE dbo.customerPredicate(SalesRepName) ON dbo.Customers,
ADD BLOCK PREDICATE dbo.customerPredicate(SalesRepName) ON dbo.Customers
go
— Now test the policy by impersonating SalesRep01
EXECUTE AS USER = ‘SalesRep01’
go
— Only rows where SalesRepName = ‘SalesRep01’ are returned (filter predicate)
SELECT * FROM dbo.Customers
go
— Error because the new SalesRepName <> ‘SalesRep01’ (block predicate)
INSERT INTO dbo.Customers
(CustomerId, CustomerName, SalesRepName)
VALUES
(1, ‘New Customer’, ‘SalesRep99’)
go
REVERT
go
Frequently asked questions
What is the performance impact of using RLS?
In general, RLS will have the same performance as a view. Because RLS relies on the query optimizer to inline the predicate function efficiently, the performance depends on the complexity of your queries and predicates, as well as any indexes you have created. For more information, see Row-Level Security: Performance and common patterns.
Can I limit access based on AD group memberships?
Yes, you can use the IS_MEMBER() function in your predicate to check SQL role or AD group memberships. For an example, see the RLS Hospital Demo script.
What if my application uses connection pooling with a single login for all users?
No problem, your application can use the new SESSION_CONTEXT feature to get and set session-scoped key-value pairs to identify users for RLS, while still enabling efficient connection pooling. For examples, see the RLS Mid-Tier Demo script or the Web app with a multitenant database using Entity Framework and Row-Level Security tutorial.
If I’ve enabled RLS, does this mean that my DBAs cannot access data using SSMS?
No. Like other row-level security solutions, RLS is intended for scenarios where the queries that a user can execute are controlled by a middle-tier application. DBAs and users with ad-hoc query access to the database may be able to infer the existence of filtered data, using side-channels. For more information, see the RLS official documentation.
Getting started
If your application needs to limit users’ access to specific rows of data, we encourage you to use RLS. The easiest way to try it with SQL Server 2016 is to download the AdventureWorks Database for SQL Server 2016 CTP3 and walk through the RLS sample script.
You can also learn more with the following resources:
See the other posts in the SQL Server 2016 blogging series.
