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.


Basics of Row Level Security
Published: Dec 03, 2020
As with most other people I know, I have been working with several companies lately around security in the wake of the GDPR law changes. As an offshoot of this I figured I would write a few posts surrounding some of the data security features within SQL Server that I’ve been playing with. The first of these is Row Level Security.

Let’s dive right in…

So what is Row Level Security?

It’s actually exactly as it sounds… You’re implementing security on your tables at the row level. This can be ideal as a methodology of protecting your data without having to produce a convoluted system using Schemas or Partitioning to physically separate your data into different objects.

This method is supported natively by SQL Server 2016 and above and is designed to achieve row level security in a more hidden and seamless fashion.

It runs on the principle of using a function to determine who does or doesn’t have access to what data, but it does it in a very different manner than if you were to use a function over a partitioning schema, for example.

In this scenario you create a function to determine row visibility (based on whatever rules you see fit, whether that be AD username, SQL username, or any passed in parameter) and you assign that function directly to a column in your table. This becomes a hidden meta-data based function which acts as a filter on the table to show only the data that the function allows.

For example, let’s have a look at the AdventureWorks database:

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



This is a nice, simple query which shows us a few aggregated order stats for our staff.

Now, let’s pretend each staff member is grouped up and given a manager for each group. We want the managers to be able to run the exact query above but to ONLY see their own data and not that of anyone else. How would we do this?

Well… we could wrap the code in a proc or use a view. However, what if we want to give them more freedom to write adhoc queries and across more tables? Or give them database access via an application, relying on their Windows Login to provide their data access and without having to write code which tacks “WHERE” clauses on to each statement? We want to implicitly tie the user to their data and only their data in the table in a manner in which they don’t even realise it’s happening.

In essence we want this type of access pattern to the data:


This is easy to do with Row Level Security and I’ll show you how in the next post.

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.