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:

SNAGHTML94b4182

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

SNAGHTML950446f

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:

image

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

image

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:

image

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.

SNAGHTML97def30

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.

image

and can see all the patient data including the PatientName.

image

The NoSensitive role can see all the facts, but cannot see any columns from the vPatientSensitive table

image

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:

image

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:

image

image

Or we could restrict the role to a particular state and deny access to the sensitive information

image

image

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: http://blog.westmonroepartners.com/a-workaround-for-column-security-in-the-sql-server-analysis-services-bism-tabular-model/
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

eg.

SELECT { [Measures].[Total Amount] } ON COLUMNS,
non empty
{ [vPatient].[PatientState].[PatientState].ALLMEMBERS
* [vPatient].[PatientGender].[PatientGender].MEMBERS
* [vPatientSensitive].[PatientName].AllMembers
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Model]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

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

Your comment:

 (will show your gravatar)