Recently, while discussing role-based security in SSAS Tabular models with a few colleagues, the following question came up: Is it possible to write a DAX query in such a way that row-level security is bypassed?
At the time, we all agreed that the notion seemed ridiculous…if that were possible, what’s the point of even having row filters in roles? On the other hand, I’d just finished Rob Collie’s (b | t) excellent book in which I learned that the CALCULATE function can be used to “remove existing filter context”.
Hrmmm….can it be? Let’s find out!
Hypothesis
Since row-level security in a SSAS 2012 Tabular model is implemented as a “Row Filter” configured with a role:
…and the CALCULATE function can be used to modify the filter context in which the expression is evaluated (per MSDN):
If the data has been filtered, the CALCULATE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
…then, it just might be possible for a DAX measure to be written such that the row filter defined in the role is ignored (or removed from the current context) and thus rendered useless.
Experiment
To test my hypothesis, I’ve put together the following experiment.
Step 1: create a basic Tabular model:
Step 2: create a security role with a row filter on one of the dimension lookup tables (that’s right, tabular models have “lookup” tables, not “dimensions”):
Step 3: create two DAX measures based on a CALCULATE function call with a filter argument referencing the same attribute as our security role row filter:
TotalResellerSalesAmount_NoSecurity01:= CALCULATE( SUM(FactResellerSales[SalesAmount]), ALL(DimSalesTerritory[SalesTerritoryGroup]) )
TotalResellerSalesAmount_NoSecurity02:= CALCULATE( SUM(FactResellerSales[SalesAmount]), ALL(DimSalesTerritory) )
In the first measure (TotalResellerSalesAmount_NoSecurity01), the use of the ALL() function in the filter clause of the CALCULATE function basically says, drop any existing filters on the SalesTerritoryGroup column/attribute before summing up ResellerSalesAmount. The second calculated measure (TotalResellerSalesAmount_NoSecurity02) is the same as the first except this time the ALL() function is applied to the entire DimSalesTerritory table (not just the column/attribute) and will therefore remove any existing filters from any of the columns/attributes in that lookup table (not just the ones on SalesTerritoryGroup).
Step 4: build/deploy the model
Step 5: test the hypothesis by executing the following DAX query via SSMS:
EVALUATE ( SUMMARIZE ( 'FactResellerSales' ,'DimSalesTerritory'[SalesTerritoryGroup] ,"TotalResellerSalesAmount",[TotalResellerSalesAmount] ,"TotalResellerSalesAmount_NoSecurity01",[TotalResellerSalesAmount_NoSecurity01] ,"TotalResellerSalesAmount_NoSecurity02",[TotalResellerSalesAmount_NoSecurity02] ) )
If my hypothesis is correct, when members of the NorthAmericanUser group (created up above) execute this query in SSMS they should see Reseller Sales for all Sales Territory Groups (North America, Europe, and Pacific) in the “TotalResellerSalesAmount_NoSecurity01” and “TotalResellerSalesAmount_NoSecurity01” columns but only see Reseller Sales for the North America sales territory in the “TotalResellerSalesAmount” column.
Results
The screenshot below is simply for comparison…I snagged it before removing my current user account from the server administrators list. Since permissions are cumulative and server administrators have full access to all models on the server the role-based security is completely ignored and we have reseller sales amounts for all 3 sales territory groups for each of the measures.
After removing my account from the server admin list, my only access to the model is via the NorthAmericaUsers role which has a row filter set for NorthAmerica. So when I run the query now:
And boom goes the dynamite…Hypothesis DISPROVED!
I’m actually a little relieved.
byoBI.com 
2 replies on “Is it possible to circumvent role-based row-level security with a well-crafted DAX query?”
But what about when you actually need an organisation total? For example, how do my expense figures compare against the organisational average? How do my expenses per head compare with those in Bob’s team?
My experimentation to-date shows that this level of analysis is unachievable once row-level-security is in place.
Looking at it from this perspective, it feels like the ALL() function should do exactly that, allowing totals to be generated from rows to which the end-user does not specifically have access.
We’ve been trying all sorts of workarounds, and have recently settled upon having a ‘shadow’ fact table that mimics every aspect of the ‘actual’ table on which the measure sits. The sole difference is the absence of any relationship with the Employee table. It’s cumbersome but it seems to be working…
LikeLike
I’ve unfortunately come to a similar conclusion. I like your “shadow fact table” idea…especially when there’s a lot of calculated measures that depend on “non-visual totals”.
Another option is to pre-store the “AllMember” values in calculated columns of the various tables.
LikeLike