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.