Out of the box Analysis Services (both Tabular and Multi-dimensional) has great support for horizontal or row based security. An example of this is where you would give User1 access to all data where the Country is “Australia” and give User2 access to all data where the country = “United States”. This covers a large percentage of the security requirements that most people have.
But neither technology has great support for vertical or column based security. This sort of requirement is most common in privacy scenarios. One example of this would be a model with medical data. It may be acceptable to show all your users demographic data such as the state they live in or their gender. But only a specific subset of users should have access to see individual patient details such as their name or phone number.
One approach would be to simply create 2 models, one with the secure information and one without. While this works, it doubles your processing time and doubles any maintenance activities and takes up double the storage.
Looking at the features in SSAS you may be tempted to try using perspectives. At first glance they appear to do what we want - allowing us to hide a subset of columns. But perspectives are not a security feature. All they do is to show a subset of the metadata to the user, but the user still has to have access to the full model and the hidden columns are still fully query-able from DAX and MDX. Trying to use perspectives for security is like putting a "Keep Out" sign on your front door, but then not actually locking it…
To explore this issue further I created a very simple database in SQL Server which has a Patient table and a FactPatient table which look like the following:
What I want to do is to create a model where only specific people can see the PatientName column. So because we can't restrict access to specific columns in a single table I created 2 views over the Patient table - vPatient which has every column except the PatientName and vPatientSensitive which has the PatientID and PatientName
At this point I then created a tabular model bringing in FactPatient, vPatient and vPatientSensitive.
If you create your relationships in the default manner you will end up with something like the following:
This works great for the role which has access to the sensitive information, but if you create a role which does not give access to any of the rows in vPatientSensitive, these users can't see any data.
The reason for this is that the Filter Context flows down through the chain of one to many realtionships
So if a role has no access to any rows in vPatientSensitive, this flows through the relationships to also filter vPatient and FactPatient resulting in this role not being able to see any data.
Because the relationship between vPatient and vPatientSensitive is actually a 1:1 we can reverse the direction of the relationship as per the following diagram:
Now we are getting closer. Our secured role works again, but we've now introduced a problem with our role that has full access. When they browse the data they see the following with the same amounts repeated for every patient name.
If you take another look at our relationships you'll notice that it now looks like a many to many relationship. And there well established pattern of dealing with many to many relationships using CALCULATE( <expression>, <intermediate table> ).
So we could try something like CALCULATE( SUM( FactPatient[Amount], vPatientSensitive ) - however we can't just use this expression as if the vPatientSensitive is restricted then we will be back to our original scenario where restricted people can't see any data. So we need to check if the current user has access to the sensitive data before applying this expression. We can do this with COUNTROWS( ALL( vPatientSensitive ) ).
Then our full expression for a measure over the FactPatient[Amount] column becomes:
Total Amount :=
IF (
COUNTROWS ( ALL ( vPatientSensitive ) ) > 0,
CALCULATE ( SUM ( FactPatient[Amount] ), vPatientSensitive ),
SUM ( FactPatient[Amount] )
)
To test this design I setup a number of different roles.
The FullAccess role has no filters applied on any tables.
and can see all the patient data including the PatientName.
The NoSensitive role can see all the facts, but cannot see any columns from the vPatientSensitive table
So when they run the same query as the FullAccess role all they see is the following where the PatientName column from vPatientSensitive only shows blank values:
It's also possible to mix and match this approach with standard row based security. So we could limit a role to only seeing data from a particular state and also given them access to the sensitive data:
Or we could restrict the role to a particular state and deny access to the sensitive information
If you want to have a play with this solution yourself I've uploaded both the tabular project and a T-SQL script which will build the source database to a folder on my OneDrive.