Dynamic security is one of those design patterns you hear mentioned every now and then by the seasoned SSAS pro’s. It is a design pattern that allows you to move the user management from the user and role management of the SSAS metadata to be part the data itself. This makes it much easier to add and remove users without having to update the cube each time, this is mostly very handy in larger implementations. In order to implement this in tabular models you had to know some tricks and learn some DAX (as you can read in this tutorial here) but in SQL Server 2016 the Tabular model (and Power BI as it is the same engine) make this easier using Bi Directional cross-filtering. How this works is described in a whitepaper that describes Bi Directional cross-filtering in detail. But in order to give this powerful feature a bit more attention I decided to also post this part on my blog.
Let’s see how row level security (RLS) plays together with the new bi directional relationships. More information on row level security you can find in its own whitepaper here.
Both RLS and bi directional relationships work by filtering data. Bi directional relationships explicitly filter data when the columns are actually used on axis, rows, columns or filters in a PivotTable or any other visuals. RLS implicitly filters data based on the expressions defined in the roles.
Let’s look at a small example:
Here I have three tables: Customer, Region and their Sales. I create a role that sets a filter to RegionId = 1. Now when I create a PivotTable and I start by dragging in Customers, I see all customers:
That’s to be expected, there are no RLS filters defined on the Customer table. Now when I drag in Sum of Sales we do see a filtered down list of customers to only those who have sales in Region with Id = 1.
Again, this is to expected as we defined security on the Region table. This will automatically filter down the Sales table to only the values for the region I have access to. I did not have to drag in any field from the Region table:
Now imagine row level security would also honor bi directional filters, securing the region table would automatically always filter out the Customer table. You would never be able to see Customer 2 and 3, even if you want to look at them in isolation from the Sales table. This is the reason that row level security only uses single directional filters by default.
Now there are some cases you do want to turn on bi directional cross-filtering when using row level security; for example, let’s look at adding dynamic security to our model. Dynamic security provides row-level security based on the user name or login id of the user currently logged on.
I want to secure my model not per region, but a grouping of regions called GlobalRegion:
Next, I add a table that declares which user belongs to a globalregion:
The goal here is to restrict access to the data based on the user name the user uses to connect to the model. The data looks like this:
Now we can create a row level security expression on the region table that will determine the global region the current connected user has access to. To do that we can create this DAX expression:
, Security[UserId], USERNAME()
, Region[GlobalRegion], Security[GlobalRegion])
This DAX expression will look up any values for GlobalRegion based on the username of the user connecting to the SSAS model. This is a pretty complicated scenario and also won’t work for models in DirectQuery mode as the LOOKUPVALUE function isn’t supported. For that reason, we introduced a new property in SQL Server 2016 that will allow you to leverage bi directional cross-filtering to enable the same scenario.
Note: when using Power BI you should use the USERPRINCIPALNAME() function instead of the USERNAME() function. This will make sure the actual username will get returned, USERNAME will give you an internal representation of the username in Power BI.
Let’s take a look at how this new property works. Instead of leveraging the DAX function to find the username and filter the table, we’ll be using the relationships in the model itself. I’ve extended the model with some additional tables to make this possible. Observe we now have a separate user table and a separate GlobalRegions tables with an intermediate table in the middle that connects the two. The relationship between User and GlobalRegion is a many to many relationship as a user can belong to many global regions and a global region can belong to many users.
The idea here is that we can add a simple row level security filter on the User[UserId] column like =USERNAME() and this filter will now be applied to all of the related tables:
There is just one issue here, as you can see the relationship between Security and GlobalRegions is set to bi-directional cross-filtering, and earlier we determined this will not be honored for RLS scenarios. Luckily there is a way to get this working for RLS scenarios as well. To turn it on I go to the diagram view in SSDT and select the relationship:
Here I can select the property that applies the filter direction when using Row Level Security. This property only works for certain models and is designed to follow the above pattern with dynamic row level security as shown in the example above. Trying to use this in for other patterns might not work and Analysis Services might throw an error to warn you.
For Power BI desktop this property looks like this:
This property only shows up when you have the preview feature called “Enable cross filtering in both directions for DirectQuery” enabled that you can find in the options menu.
That is all there is too it, one simple DAX expression and a single property. Done!