SQL Server 2016 – Row Level Security

SQL Server 2016 includes the Row Level Security feature which allows users to define a function to that will display or hide individual rows, based on criteria that we define.
We’ll start by creating some test data.

if object_Id('dbo.RLSTest') is not null
	drop table dbo.RLSTest
go
create table dbo.RLSTest (
RecordId int not null identity(1,1) primary key,
DisplayName varchar(20) not null,
IsSensitive bit not null
);

insert into dbo.RLSTest(DisplayName, IsSensitive) values ('Record1', 0);
insert into dbo.RLSTest(DisplayName, IsSensitive) values ('Record2', 1);
insert into dbo.RLSTest(DisplayName, IsSensitive) values ('Record3', 1);
insert into dbo.RLSTest(DisplayName, IsSensitive) values ('Record4', 1);
insert into dbo.RLSTest(DisplayName, IsSensitive) values ('Record5', 0);

select * from dbo.RLSTest;
go

Some of the records are marked with IsSensitive set to true, so we want to hide these rows from the end user.
We’ll need to define a function that will return true for the rows that we want displayed, in this case where IsSensitive is false. We’ll then create a security policy to link the function to our table.

create function dbo.CanViewIsSensitive(@IsSensitive bit)
returns table
with schemabinding
as

return select 1 as CanViewIsSensitive
where @IsSensitive = 0; 

go
create security policy RLSTestPolicy
add filter predicate dbo.CanViewIsSensitive(IsSensitive)
on dbo.RLSTest
with (state = on);
go

select * from dbo.RLSTest;
go

Now when a select is run to return all rows, we’ll only see the ones where IsSensitive is false.
This is a simple example on row level security. A real world example would be a little more complex. In most cases we would probably want to display certain rows to certain users, so we would need to check a user name or role membership to see if the user was allowed access.

Row Level Security on MSDN

Advertisements

2 Responses to SQL Server 2016 – Row Level Security

  1. […] of these features. SQL Server 2016 – Temporal Tables SQL Server 2016 – Data Masking Row Level Security JSON […]

  2. […] and wasn’t quite sure what this displays. I finally figured out this refers to (Row Level Security. A security policy is created, which links a table to the function that will determine if a row […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: