Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Row Level Security Example
Published: Dec 02, 2020
This follows on from the last post in which we discussed Row Level Security. This post provides a nice and simple example based on the example proffered in the previous blog post.

We have the following query:

select p.BusinessEntityID, p.FirstName, p.LastName,
              
count(s.SalesOrderID) noCustomers,
              
sum(s.TotalDue) totalDue
from sales.SalesOrderHeader s
join person.person p
on s.SalesPersonID = p.BusinessEntityID
group by p.BusinessEntityID, p.FirstName, p.LastName



What we now want is to allocate managers to some of the staff and seamlessly, behind the scenes, restrict their access so that they can keep a nice and generic query (with no WHERE filter required) yet only see their own staff performance.

Firstly we’re going to create a mapping table for managers to staff and add a couple of records:

drop table if exists managerTable
go

create table managerTable
(
      
managerUserName varchar(100) not null,
      
personID int not null
)
insert into managerTable select 'manager1', 284
insert into managerTable select 'manager1', 276

insert into managerTable select 'manager2', 275
insert into managerTable select 'manager2', 290
go


Next we make a user for each manager:

create user manager1 without login
create user manager2 without login
go

grant select on sales.salesOrderHeader to manager1, manager2
grant select on person.person to manager1, manager2
go


Now we can see that manager1, for example, can see a full dataset:

execute as user = 'manager1';

select p.BusinessEntityID, p.FirstName, p.LastName,
              
count(s.SalesOrderID) noCustomers,
              
sum(s.TotalDue) totalDue
from sales.SalesOrderHeader s
join person.person p
on s.SalesPersonID = p.BusinessEntityID
group by p.BusinessEntityID, p.FirstName, p.LastName;

revert
go


execute as user = 'manager1';

select p.BusinessEntityID, p.FirstName, p.LastName,
              
count(s.SalesOrderID) noCustomers,
              
sum(s.TotalDue) totalDue
from sales.SalesOrderHeader s
join person.person p
on s.SalesPersonID = p.BusinessEntityID
group by p.BusinessEntityID, p.FirstName, p.LastName;

revert
go



So now we need to restrict their access based on our manager to employee table.

To do this we need to create a security function which we will then apply to the table(s) we’re interested in restricting. The job of this function is to determine success criteria for the security and it runs as follows: If successful then return something, otherwise return nothing…

create function dbo.secManagerFunction(@employeeID int)
      
returns table
with
schemabinding
as
       return
      
(
              
select top (1) 1 as pass
              
from dbo.managerTable m
              
where m.personID = @employeeID
              
and m.managerUserName = user_name()
       )
go


The above therefore accepts an employeeID and checks to see whether the current user is their manager. If so, then it returns. If not, then an empty set is returned and will not pass security.

We now need to add this as a policy to the sales.salesOrderHeader table to restrict access as required:

create security policy secPolicyManager
add filter predicate dbo.secManagerFunction(SalesPersonID)
on sales.salesOrderHeader
with (state = on)


The above simply tells SQL Server to apply the function to the sales.salesOrderHeader table and apply it to the SalesPersonID column.

This means that now, when we try to run the same query as “manager1”, we get filtered results in a completely seamless manner, which is exactly what we wanted…


Likewise, let’s try manager2:

execute as user = 'manager2';

select p.BusinessEntityID, p.FirstName, p.LastName,
              
count(s.SalesOrderID) noCustomers,
              
sum(s.TotalDue) totalDue
from sales.SalesOrderHeader s
join person.person p
on s.SalesPersonID = p.BusinessEntityID
group by p.BusinessEntityID, p.FirstName, p.LastName;

revert
go



See… it’s as easy as that.

Now for a little cleanup and we’re done:

drop security policy secPolicyManager
drop function dbo.secManagerFunction
drop user manager1
drop user manager2
go


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.