SQL Server 2016 Row-Level Security

With SQL Server 2016 literately just days away from being officially available to public for download Get ready, SQL Server 2016 coming on June 1st. I wanted to highlight one important new key future called Row-Level Security. So what is Row Level Security? Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer’s data access to only the data relevant to their company.

The access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. This makes your security system more reliable and robust by reducing the surface area of your security system.

You implement RLS by using the CREATE SECURITY POLICY T-SQL statement, and predicates created as inline table valued functions.

To better understand SQL Server 2016 Row-Level Security let’s go through some code sample:

Create three user accounts that will demonstrate different access capabilities.

create user Manager without login;
create user Sales1 without login;
create user Sales2 without login;

Create a quick table to hold data.

create table dbo.Orders
(
    OrderID	int,
    SalesRep	sysname,
    Product	varchar(25),
    Quantity	int
);

Populate the table with 10 rows of data, showing 5 orders for each sales person.

insert dbo.Orders
 values (1, 'Sales1', 'Bracket', 11), 
	(2, 'Sales1', 'Wheel', 21), 
	(3, 'Sales1', 'Valve', 4),
	(4, 'Sales1', 'Bracket', 6), 
	(5, 'Sales1', 'Wheel', 8), 
	(6, 'Sales2', 'Seat', 13),
	(7, 'Sales2', 'Seat', 5),
	(8, 'Sales2', 'Seat', 7),
	(9, 'Sales2', 'Bracket', 17),
	(10, 'Sales2', 'Wheel', 20);

View the rows in the table

select * from dbo.Orders; 

Grant read access on the table to each of the users.

grant select on dbo.Orders to Manager;
grant select on dbo.Orders to Sales1;
grant select on dbo.Orders to Sales2;

Create a new schema, and an inline table valued function. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = ‘Manager’).

create schema Security;
go

create function Security.fn_securitypredicate(@SalesRep as sysname)
    returns table
with schemabinding
as
    return select 1 as fn_securitypredicate_result 
where @SalesRep = user_name() or user_name() = 'Manager';

Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy.

create security policy OrdersFilter
add filter predicate Security.fn_securitypredicate(SalesRep) 
on dbo.Orders
with (state = on);

Let’s now test the filtering predicate, by selected from the Orders table as each user. The Manager should see all 10 rows. The Sales1 and Sales2 users should only see their own sales.

execute as user = 'Sales1';
select * from dbo.Orders; 
revert;

execute as user = 'Sales2';
select * from dbo.Orders; 
revert;

execute as user = 'Manager';
select * from dbo.Orders; 
revert;

To alter and disable the security policy.

alter security policy OrdersFilter
with (state = off);

In general, row-level security will work as expected across features. However, there are a few exceptions. The Cross-Feature Compatibility section documents several notes and caveats for using row-level security with certain other features of SQL Server. Details and information on RLS can be found here on the online documentation.

Enjoy