Stefan Grigat

In this Blog I’ll show you how to use the new SQL Server 2016 feature „Row Level Security“ in a Data-Warehouse-Environment where the Security is not directly applied to the operational table/ fact table but a Dimension-Hierarchy.
Last time at customer site I had to evaluate the new SQL Server 2016 feature „Row Level Security“ for his environment and use case.
To be prepared for this scenario I made up an example where we do have a normal star schema as used in Data-Warehouses extended with a User-Security Table.
I use a fact table with a date column, the StoreId as reference to DimStore, ArticleId as reference to DimArticle and a column with the fact „Amount“. So far this is a normal star schema.
In the DimStore-Table we have a hierarchy Store -> Region -> Country.
The new Table UserSecurity now has a row with the Username and the region the user shall see. So our goal is to reduce the lines of the DimStore and so the fact-rows depending on the connected user.
Data Model for the Row Level Security Example
Let’s start to build our scenario.

Now we add some sample data…

As you can see we do have 3 users who have RegionIds assigned. There is one row for every user and region. If a user should see more than one region it has to appear one time for every region.

To demo the behavior I now add the 3 Users and grant them to read the database objects.

So every user could now read every object (tables, views,…) in the database.
To achieve row level security before SQL Server 2016 you could implement a view and only grant select to this view for users.
This is such an example view:

Querying this view as user1 looks like that:

Query Result of the view

The user can only see his region. But as soon as he queries the fact-table itself, he can immediately see every row

Query Result of the Table queried directly

Now, with the new SQL Server 2016 feature we can define and apply row level security with a function and policy on the table itself:

This function returns a table and when the expression evaluation is true, it returns 1 for the row. In this case we evaluate the connected username and we defined the evaluation column. In this case the StoreId of the DimStore.
Here I used a trick which is not directly described in books online but was necessary to test for the customer scenario. I did not apply the row level security to the fact table itself but used a double join via DimStore and UserSecurity.

Now we can use this function in a policy, which is applied on our fact table.

Using „with(STATE = ON)“ activates it just in the same statement.

Let’s test it:

Query Result with Row Level Security activated

Query Result with Row Level Security activated

The User can directly query the fact table itself or join it with any table she likes but gets only the allowed rows.
(Even Access with other Clients like Excel or PowerBI-Tools will always deliver only the rows the connected user is allowed to see)

What a great feature. :-)

Now, let’s tidy up:

Have much fun with this new feature.
The Full Script can be downloaded here: RowLevelSecurity_FullScript