SQL Server 2016 Row Level Security - A Practical Example

  • Article

How can I use Row Level Security to control which users see certain data in my data table?

 

Here’s what I want to achieve.

I want to create an SSRS report which multiple users from different teams can access.  The report will use the same underlying table for each user so I need to be able to secure the data so that only permitted users can see their own data.  Unfortunately, with SSRS there isn’t anything straight out of the box to achieve this, so without coding some crazy logic we are a little limited!

 

I have a table of data containing some assessment data, the table contains information like InstanceName, DatabaseName, DBOwner and a bunch of assessment values.

I want to be able to control who sees this data based on either a username or active directory group name.

Quick note - The DBOwner column is not "dbowner" in a SQL Server sense, but which group or person manages / "owns" the database.

 

My table data.  (Ignore the fact it’s not the best DW model! – it’s in POC stage right now).

image

 

This is easily achieved using Row Level Security

 

I want to allow a single user or a user that’ s part of a group to access specific rows within a table.

 

First I need to create a Row Level Security filter predicate.  This will run per row returned by my query.

[sql]

CREATE function [sec].[fn_SecurityPredicate1](@username AS VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

    SELECT    1 AS fn_SecurityPredicateResult
    WHERE    SUSER_NAME() = @username
        OR IS_MEMBER(@username) = 1
GO

[/sql]

 

This essentially checks 2 things.  First, does the value passed into the function match the current user executing the query?  Second, does the user running the query belong to the AD group value which is passed into the query.

Where does the @username value come from i hear you say?

Well, once the function is created we now need to create a Security Policy which will “Bind” the function to a table based on my defined column.

[sql]

CREATE SECURITY POLICY sec.factAssessmentFilter
ADD FILTER PREDICATE sec.fn_SecurityPredicate1(DBOwner)
ON dbo.factAssessment
WITH (STATE=ON)

[/sql]

 

Now when I query my fastAssessment table the function will run against each row in the output passing in the DBOwner column to the function. This is the value which is evaluated.  If the function returns 1 (True) then the user is allowed to see the rows.  If the function returns 0 (false) then the user is NOT allowed to see the rows.

 

Here’s an example

I’ll first create an AD group with a user to test with.

image

I've added this group into SQL Server as an administrator (I know not good practice but its a POC!)

 

So at this point I'm going to use 2 users to test this with.  The administrator (LOUNDY\Administrator) and my new user (LOUNDY\sqluser01).

Before I go ahead and apply my security policy, I want to assign owners to some databases.  To set a bit of context here is a list of distinct databases and row counts;

image

 

Now to assign some DBOwners:

image

So, now once I apply my security policy on this table, LOUNDY\Administrator can only see values for AdventureWorks2012 and nothing else, and LOUNDY\SQLUsers can only see values for HR and nothing else.  Any other user which queries this table will get 0 rows back, even if they are sysadmins!

 

Query Executed as LOUNDY\Administrator

image

 

Query Executed as LOUNDY\sqluser01 – Successful because the user is part of the SQLUsers group.

image

 

Learn More:

Row Level Security -  https://msdn.microsoft.com/en-us/library/dn765131.aspx

Channel 9 talk on RLS - https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-Row-Level-Security