Row Level Security using Microsoft Analysis Services Cubes in an External- Facing Environment

Later versions of Microsoft Analysis Services (MSAS) allow you to configure user and role based data security within the cube itself. However, this functionality only works when that particular user is logged in directly to the cube. In Tableau, this can be accomplished via Kerberos.

What about when you are using MSAS cubes in an external facing solution, with users who are not in the local domain? Cube connections in Tableau don’t have the equivalent of a Data Source Filter the way relational database connections do, and there is no way to pass the USERNAME() function into a Calculated Member the way you can in a relational calculated field.

In this case, the manual “User Filter” functionality can achieve a reasonable solution.

In the Server menu of Tableau Desktop, there is a menu option called “Create User Filter”.

Cube Row Level Security 1

This menu works even with Cube data source. You choose the field you want to filter upon, and then the following dialog appears

Cube Row Level Security 2

You can manually select the different members of the field you want to be available to that user or group. Create each rule that you need, then press okay.

This security rule will show up under Sets with whatever name you gave it. But it is a special set that is this Row Level Security rule. Bring it out on the Filters shelf, and now you have Row Level Security on a MSAS cube.

Limitations of this method: Because the Filter just sits on the Filters shelf, this method does not provide security when using Web Edit or connecting to the published Workbook using Desktop. It will work for any published views that don’t have web edit enabled.

Automating the Rules

The process outlined above works pretty well, but it’s definitely manual. Let’s look at what Tableau is actually doing in the TWB XML to see if we could automate setting and updating the rules.

Here’s what the XML of the filter itself looks like (Oddly enough, it is in a group tag):

 <group name='[User Filter 1]' name-style='unqualified' user:ui-builder='identity-set'>
	<groupfilter function='intersection'>
	  <groupfilter function='level-members' level='[Customers].[Region].[Region]' />
	  <groupfilter function='union'>
		<groupfilter expression='false' function='filter'>
		  <groupfilter function='level-members' level='[Customers].[Region].[Region]' />
		</groupfilter>
		<groupfilter expression='ISMEMBEROF(&apos;L1&apos;)' function='filter'>
		  <groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[West]&quot;' />
		</groupfilter>
		<groupfilter expression='ISMEMBEROF(&apos;L2&apos;)' function='filter'>
		  <groupfilter function='union'>
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].[All].UNKNOWNMEMBER&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Africa]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Canada]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Caribbean]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Central Asia]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Central]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[East]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[EMEA]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[North Asia]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[North]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Oceania]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[South]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Southeast Asia]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[West]&quot;' />
		  </groupfilter>
		</groupfilter>
		<groupfilter expression='ISCURRENTUSER(&apos;ariel.einstein&apos;)' function='filter'>
		  <groupfilter function='union'>
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Africa]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[Canada]&quot;' />
		  </groupfilter>
		</groupfilter>
		<groupfilter expression='ISCURRENTUSER(&apos;hunter.jones&apos;)' function='filter'>
		  <groupfilter function='union'>
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[North Asia]&quot;' />
			<groupfilter function='member' level='[Customers].[Region].[Region]' member='&quot;[Customers].[Region].&amp;[North]&quot;' />
		  </groupfilter>
		</groupfilter>
	  </groupfilter>
	</groupfilter>
  </group>

 

 

From this pattern, I believe would be possible to construct / add valid rules programmatically. The groupfilter tag with function=’filter’ contains a second groupfilter tag which identifies the field level which will be filtered on. Then the subsequent groupfilter tags with an expression that includes “ISCURRENTUSER()” or “ISMEMBEROF()” calculations to identify which user or group matches to which set of elements.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s