Returning actual values for a measure that is protected by OLS
In a previous example, I discussed a hypothetical where we hid Customer[Gender] via object level security. The genders present in that model are Male and Female, and that isn’t neccessarily exactly a secret. We are looking to do is hide what gender each customer is, not hide the fact that genders exist.
A security role like this isn’t the most logical scenario – rather than securing a person’s non personally identifiable attribute, you should secure the personally identifiable information (name, address, etc) and allow access to the attributes that don’t identify people in the aggregate. This is especially true if you actually want people to see the aggregate data.
The previous post left off at the below, where a custom label was defaulting to the default value because the measure was unavailable due to referencing Customer[Gender], unavailable to this security role:
There is a solution to this, and it comes back to the physical data model. Let’s create an inactive relationship with a Gender dimension table, which is all distinct genders. Because this is calculated at model refresh, there is no security issue. Let’s also leave this table available to our restricted gender role.
As configured below, it will not impact the report’s default behaviour.
The physical data model is unaffected by any OLS you may apply. If we activated this relationship and did not secure the Genders table, a secured role user could make a visual with Genders[Gender] and Customer[CustomerKey] to see each person’s gender, even though Customer[Gender] is secured.
If someone had knowledge of the data model, they could create a measure like below:
Customer Gender =
CALCULATE(
SELECTEDVALUE(Genders[Gender]),
USERELATIONSHIP(Genders[Gender], Customer[Gender]),
CROSSFILTER(Genders[Gender], Customer[Gender], BOTH)
)
This is an interesting quirk: references to restricted objects within an argument that modifies physical relationships (in this example, USERELATIONSHIP and CROSSFILTER) do not trigger the downstream disappearing act of any dependent object. A user can manipulate relationships they can’t see!
This could be dangerous if an unsecured user had access to author on the model and understood it’s structure, as they could write something like the above themselves. That said, if you had strict access controls that would prevent a person from doing anything more than viewing a report, you could adjust your data model and measures to display results that OLS would otherwise block.
Gender Sales Amount =
CALCULATE([Sales Amount], USERELATIONSHIP(Customer[Gender], Genders[Gender] ) )
Sales Amount By Gender =
DIVIDE ([Gender Sales Amount], [Sales Amount])And change your visual to use the Gender Sales Amount measure, as well as the Gender field from our dimension table:
Then your restricted role can now see all the restricted data in whatever aggregates you allow them to, while keeping each individual customer’s attributes secure in a basic scenario:
What’s really interesting is how the user genuinely doesn’t know for certain how the relationship is configured. If they live connect to the model with the security role, they can see there is a relationship but can’t confirm on what column:
If they convert it to DirectQuery/a local model, the relationship is deleted entirely and cannot be restored.
So, if there was ever a very specific requirement to hide one attribute from a dimension on a per person basis but still return it’s results without pre-aggregating anything, here you go!