Implementing Column Security with #SSAS Tabular and #DAX

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.

Print | posted on Tuesday, April 22, 2014 11:20 PM

Comments on this post

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
Great article! Definitely going to have a play with the solution :)
Left by Michael Amadi on Apr 24, 2014 6:33 AM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
One other option that may work in some cases is to replicate the full cube as a more restricted cube (with the sensitive data columns removed). The following link explains in more detail:
Left by Jonathon on Jul 10, 2014 1:46 AM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
This Security Model and roles only works as expected in Excel. But when tried in Management Studio by browsing as soon as you bring Sensitive Table column no rows are displayed and also not works in ssrs report. Any idea what needs to be done?
Left by Praveen on Apr 10, 2015 11:55 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
OK so the "problem" here is that SSMS and SSRS are generating queries for the [vPatientSensitive].[PatientName].[PatientName] level which is an empty set in the secured roles and if you crossjoin an empty set with any other set it returns an empty set. What Excel is doing is using the [vPatientSensitive].[PatientName] hierarchy and drilling down from the ALL member so it never gets the empty set.

So for SSRS you will have to use the hiearchy, not the level in your queries


SELECT { [Measures].[Total Amount] } ON COLUMNS,
non empty
{ [vPatient].[PatientState].[PatientState].ALLMEMBERS
* [vPatient].[PatientGender].[PatientGender].MEMBERS
* [vPatientSensitive].[PatientName].AllMembers
FROM [Model]

If you want to dynamically filter out the ALL members depending on whether the user can see the sensitive information or not you would probably have to do a filter in the dataset in SSRS - you could test if someone has access to the sensitive information by creating a measure with a definition something like the following

CanSeeSensitive:=IF(COUNTROWS(ALL(vPatientSensitive)) > 0,1,0)

And then you'd just filter out the rows with All and CanSeeSensitive = 1
Left by Darren Gosbell on Apr 11, 2015 5:35 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
This security is working fine in Excel, only empty fields are showing up as expected but when tried to browse the cube in SSMS, it is displaying data in the secured Columns also.

Please advice.
Left by Neeraja on May 11, 2015 6:40 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
Please see my answer to the previous person who asked this question directly above your comment.
Left by Darren Gosbell on May 11, 2015 6:42 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
Is there anything like "Analysis server admin will see all the data in the cube, even though we hide sensitive data(using above column security) from that user/admin"?
Left by Niharika on May 11, 2015 7:30 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
@Niharika - Yes - anyone who is a server admin will always see all data in the cube by default. But a server admin can also test a give role by adding "Roles=<role name>" on to the end of the connection string to get the same view of the cube as members of the specified role (which is how I got the screenshots for the article above)
Left by Darren Gosbell on May 11, 2015 10:18 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
In your example, I have a need for a user to have access to sensitive data for their state but also have access to other states where they would not have access to sensitive data.
Left by Gene Furibondo on Jun 19, 2015 6:10 AM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
@Gene - I'm not entirely sure how this would be implemented. I think what you might have to do would be to not restrict vPatient at all, but then put a hidden copy of the state in the vPatientSensitive table so that you could restrict the rows in there based on the state.
Left by Darren Gosbell on Jun 19, 2015 7:10 AM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
I have only one fact table and dimension and trying to implement column security on dimension , will it work?

user with restricted access ,seeing no data in the report in any table

Thanks in advance.
Left by Niharika on Jun 24, 2015 5:10 PM

# re: Implementing Column Security with #SSAS Tabular and #DAX

Requesting Gravatar...
@Niharika - no, if you've only go one dimension table this will not work. You need to follow the instructions in this blog post to split the sensitive data into it's own table.
Left by Darren Gosbell on Jun 24, 2015 8:09 PM

Your comment:

 (will show your gravatar)