Richard Lees recently blogged SSAS Dynamic Security. It reminded me that I had an outstanding blog post of my own on the same topic, with a slightly different twist involving PerformancePoint and Kerberos. Before we get to the twist, let’s recap on dynamic security within SQL Server Analysis Services (SSAS).
The concept behind SSAS dynamic security is quite simple. Like SQL Server itself, SSAS uses role based security as a means to restrict the user’s access to various parts of the cube. A commonly cited example is restricting sales people from a particular sales region dimensioning by other sales regions e.g.; Australian sales people should not be able to view sales figures for Canada.
Using an SSAS role, we can implement this quite easily. As shown below, I’ve created a new role, and on the Dimension Data page, restricted the Sales Territory Country to Australia. On the Membership page (not shown), I would then select the appropriate Windows login(s), and those users would then be restricted from dimensioning by countries other than Australia.
So far so good. Nice and easy. But, let’s imagine a situation in which there were thousands of sales territories. Creating a role for each one of them (and then managing membership over time) is simply not feasible.
Enter dynamic security. In essence, we use a many to many structure which maintains the mappings between users and allowed dimension members in tables. Using the AdventureWorks sample, we would create a table structure like this ...
The UserTerritoryBridge table simply maps users (Windows accounts) to Sales Territories. In a typical BI implementation, this data would be maintained by an ETL process from the source system that defines such mappings. Note that the structure allows for 1 user to be mapped to many territories and vice versa. A true "many to many" relationship.
The next step is defining the usage of these tables inside Analysis Services. In summary, we create a dimension using the dimUser table, and a measure group using the UserTerritoryBridge table. Such a measure group is commonly called a “factless fact table”. In both cases, we make their measures and attributes hidden; we don’t want users seeing them, they’re meaningless and there for security purposes only. Once defined, the cube structure looks like this ...
... and the dimension usage looks like this ...
With those structures in place, we can go back to our role, and modify it to be dynamic, i.e.; instead of creating a role per user/group, we now have a single role which handles ALL users. We do that by first selecting “Deselect all members” on the Basic tab, and then on the advanced tab use an MDX expression as follows ...
This expression uses the UserName() function to determine the current user, and uses that to filter out the Sales Territory Country members the user is not setup to view. Note the “Enable Visual Totals” checkbox down the bottom. This ensures the user can only see measure totals for the dimension members they can see. Without that selected, they will see totals for all countries, including those they cannot see when browsing the cube.
So that’s a basic implementation of SSAS dynamic security. Now comes the twist (with triple pike).
Consider an implementation of a PerformancePoint dashboard through SharePoint 2010 which uses the cube as a data source i.e.; a user browsing the cube through a PerformancePoint dashboard. In terms of user authentication/delegation, we have;
- The user logs onto Windows,
- The user connects to SharePoint,
- SharePoint launches PerformancePoint services to invoke a Dashboard,
- The Dashboard connects to Analysis Services
This is Kerberos heaven (or hell). There’s a lot of hopping there. More hops than a Kangaroo on a caffeine binge. There’s a lot that can go wrong, particularly when you add an ISA/Proxy server to the mix.
From a PerformancePoint perspective, there are now 3 authentication options for data sources;
Unattended Service Account (first option) is fairly straight forward – All users connect to the data source using the security context of the defined account. Simple, but not granular enough for custom security.
Per-User Identity (third option) solves this problem, assuming you have a working Kerberos environment.
An additional option is listed in the middle; “Unattended Service Account and add authenticated user name in connection string”. As per the description, this adds the user’s login details to the connection string, and can be consumed within Analysis Services by using the CustomData() function, instead of the UserName() function, which is a nice little workaround for environments that are “Kerberos challenged”.
Now, something important to point out here is that this does not mean we can forget about Kerberos. As Richard and I discussed in the comments on his post, someone with access to the SSAS cube through a different front end client could potentially circumvent the intended security by altering the CustomData component of the connection string to connect as a different user. This presupposes a sophisticated user, but it’s a concern nonetheless. So at best, this is a temporary workaround for environments that ONLY use PerformancePoint as the front end for their BI solution.
To finish, I just wanted to leave you with this blog post from Adam Saxton, titled “My Kerberos Checklist…”
Enjoy :-)
Cheers,
Rod.
Subscribe to this blog!
