The Additive Design of SSAS Role Security
SSAS security roles are additive – that is, a user gets permission to access data allowed in any role to which the user belongs, even if another role membership for the same user does not allow access to the same data. This can cause confusion in some circumstances, and has been incorrectly reckoned a defect in the product in the past when discovered by administrators. The product group considered the question in crafting the existing design though, and opted to use additive security ultimately. The scenarios in which this problem occur are not as common as scenarios wherein additive security must be used to produce the expected result, and several workarounds exist to mitigate the issue.
The most common overlapping role security scenario
Consider the scenario where one role is applied to users for one geography, such as within North America, but another role is also applied to the same user for a different geography, which overlaps the North American geography. The user may be explicitly granted access to [All North America] in the role covering North American users, but in the second geography covering role, may contain only permission to some subset, say [USA], which does not cover other members like [Canada], which would be included in the [All North America] permissions from the first role. In this case, by using additive security the user will have access to [Canada], through the [All North America] permissions granted in the first role. The second role may only grant access to data for [USA], but [Canada] will not be denied even so. This is generally what is intended with this type of scenario, and is the reason the additive design was adopted.
The less common and problematic overlapping role security scenario
The less common scenario, in which the problem referenced above can surface, would be that of two roles used to secure attributes in two different dimensions, which might both apply to some users simultaneously. In the sample provided with this post, there are only two dimensions – one for Sales Reps who enter sales into a database, and the other for Takers, who take inventory from shelves to fulfill orders. The Sales Rep role secures the [Sales Rep] dimension, such that each representative will only have data to see his own sales:
Likewise, the Taker role secures the [Taker] dimension, such that each Taker will only see values reflecting inventory taken by him:
It so happens that some Takers may also be Sales Reps, and so in those cases, a user will be a member of both the Taker and Sales Rep roles simultaneously. Since the Taker role only secures the [Taker] dimension, this means the entire [Sales Rep] dimension has open permissions within that role:
And conversely, the Sales Rep role leaves the entire [Taker] dimension unsecured, though it secures the [Sales Rep] dimension. Because of the additive design of role security in Analysis Services, administrators may be dismayed to find that any user in both roles will have complete access to both dimensions in this scenario, including data neither taken from shelves nor sold by the user in question.
The user will effectively have no security applied from either role then. Fortunately, there are two ways to work around this issue.
Simplest workaround
The simplest workaround is to combine the two roles into a single role applicable to all members of each former role, enforcing the overlapping security all in one place. This will work for most cases, but there may be some cases where for some reason or other, the administrator will need or prefer to keep the roles separate. In the original real world case necessitating the alternate workaround – there was just such a reason.
Alternate workaround
In those cases where the simplest workaround to combine the overlapping security roles will not suffice, an alternate workaround exists – not quite so straightforward as combining the roles, but still very simple to implement. The workaround does require a custom assembly, and so can only be used on traditional multidimensional databases. Tabular databases do not support the use of custom assemblies.
To avoid the additive consequence when applying security, each role must detect whether the current user is present in the other affected role(s), and selectively deny access then to the entire affected dimension(s) from the other role when the current user is found in both. This is done by adding attribute security on the same attribute secured within the other role(s), to express a conditional Allowed Member Set:
When performing this check, if the user is not found in the other role (the other role being Sales Rep in the example above), then no security is applied to the other secured attribute. The Allowed Member Set returns all of the members in the attribute hierarchy with [Sales Rep].[Sales Rep Key].Members. The attribute remains unsecured then as it normally would be.
But if the current user is also found in the Sales Rep role when security for the first role is evaluated, instead of leaving the attribute unsecured, rather, all access is denied to those dimension(s) then within the first role. The returned Allowed Member Set is empty, denoted with the empty set expression {} above.
This change, when applied to both roles correspondingly, allows role security from each role to selectively grant access to its corresponding secured attribute(s), without inadvertently allowing access to everything in the other role(s)’ secured attributes, due to their being unsecured within it. In this way, each dimension can be secured according to the security expression assigned in its proper role, and the roles may be applied to multiple users without suffering a loss of security applied in any of the affected roles.
Custom assembly for alternate workaround
While the alternate workaround proves simple enough to achieve, it does require us to create a custom assembly. No function exists within the MDX language to directly retrieve the current user’s role memberships in the way the built-in UserName() function allows us to do for the current session’s login name.
Included in this post is a very simple custom assembly called RoleDetector, containing two functions:
using Microsoft.AnalysisServices.AdomdServer; using System.Linq;
namespace RoleDetector { public sealed class RoleDetector { private RoleDetector() {
}
public static bool IsUserInRole(string RoleName) { AdomdCommand cmd = new AdomdCommand( "SELECT ROLES FROM SYSTEMRESTRICTSCHEMA ($System.dbschema_catalogs, [CATALOG_NAME] = '" + Context.CurrentDatabaseName + "')"); if ((cmd.ExecuteScalar() as string).ToLower().Split(',').Contains(RoleName.ToLower())) return true; else return false; }
public static string Roles() { AdomdCommand cmd = new AdomdCommand( "SELECT ROLES FROM SYSTEMRESTRICTSCHEMA ($System.dbschema_catalogs, [CATALOG_NAME] = '" + Context.CurrentDatabaseName + "')"); return cmd.ExecuteScalar() as string; } } } |
IsUserInRole() accepts a single string parameter containing the name of the role to test, and returns true if the user is found to be in that role, false otherwise. Roles() simply returns a string containing a list of the roles applied to the current user context.
The very simple code uses the DBSCHEMA_CATALOGS Data Management View (DMV), executing a query against this to retrieve the active roles for the current user’s session, restricted for only the current database name using SYSTEMRESTRICTSCHEMA as described in the documentation on using the DMVs.
Once the assembly is built, it must be registered for the database, before an expression may reference it. We can do this in the Assemblies folder in Management Studio for the SSAS database, right clicking to choose New Assembly, and then browsing to the location of the .dll generated when we built the assembly in Visual Studio. The default most restrictive settings for the assembly are sufficient for it to provide the necessary role membership check, so nothing needs to be changed on the Register Database Assembly dialog then:
Once the assembly is registered, we can call RoleDetector.IsUserInRole(“Taker”) and RoleDetector.IsUserInRole(“Sales Rep”) from within our security expressions defined in each role (as shown above) to take action accordingly, to deny access to the alternate role’s secured attributes, whenever the user exists in both.
Confirming the result
The sample database and assembly attached at the end of this post demonstrate the issue. After restoring the .abf Analysis Services database backup (requiring SQL Server Analysis Services 2012, SP2) and registering the assembly, connect to the database as a non-administrative user to browse its measures, dragging measures and both dimensions from the browser window onto the query window. No data is visible, since the workaround is implemented in the sample database, and the current user is not listed in the corresponding secured dimension attributes (unless the current username happens to be johndoe, janedoe or alicealison, since those users do exist among the sample database’s secured attributes’ members, and so corresponding data will be visible in each dimension respectively for those users):
To verify the troublesome behavior if the workaround is not implemented properly, remove the Allowed Member Set expression on the [Taker Key] attribute for the [Taker] cube dimension on the Dimension Data tab for the Sales Rep role, and correspondingly, remove the Allowed Member Set expression on the [Sales Rep Key] attribute for the [Sales Rep] dimension on the Dimension Data tab for the Taker role, and execute the query again:
Sample database and assembly binary and code
The sample includes the .abf database backup for the sample database, as well as the assembly binary, RoleDetector.dll, and its underlying source code project for Visual Studio 2012, including references that must be updated, pointing to the Analysis Services ADOMD Server and AMO libraries, at the following respective default locations on a system with Analysis Services installed:
C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msmgdsrv.dll
C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.AnalysisServices.DLL
References
Permissions and Access Rights in Analysis Services:
https://technet.microsoft.com/en-us/library/ms174786(v=sql.105).aspxCreating a custom assembly for Analysis Services:
https://technet.microsoft.com/en-us/library/ms175340(v=sql.110).aspxData Management Views (DMVs) in Analysis Services:
https://msdn.microsoft.com/en-us/library/hh230820.aspx
Jon Burchel | Microsoft SQL Server Escalation Services
Comments
Anonymous
July 30, 2014
thanksAnonymous
July 30, 2014
thank youAnonymous
August 03, 2014
Hello! Thanks for post. Have a question. In this example if I would be a taker only, then I could see all data from all salesRep. If I would be a salesRep only, then I could see data from all takers. But if I would be both, taker and salesRep, I could see only data where I was taker and salesRep at the same time. All others records are missed for me. Is it desirable or my understanding is wrong?Anonymous
November 12, 2015
What would the MDX look like for the simplest workaround using a single role. I am battling to get the results I need without overlapping.Anonymous
April 02, 2017
Hi, I am working with ssas multidimensional instance, and wanted to establish the dynamic security through username function. It seems that the username function works, when every user has their individual distinct access defined. But in the scenario, when multiple users need to have access to same data, the username function stops working. Any ideas will be of great help. I am kinda stuck hereAnonymous
August 02, 2017
I have implemented the suggested solution for the alternate workaround, but it looks like the denied access is not working.We have two cubes within the same Project, sharing dimensions...1. The 1st cube is available to Everyone and so Role A has the Built-in Security Principal EVERYONE2. The 2nd cube is available to Role B, its members should not have access to the cube 1st Cube3. For the Role A, I set, under Dimension Data for the 1st Cube > Measures dimension > Advanced > Allowed member Set > IIf( RoleDetector.IsUserInRole("Role B"), {}, {[Measures].AllMembers} )4. A username entered as Domain\username under Role B > Membership, when accessing the 1st Cube through Excel, he gets all the dimensions without any denied access. What could be the issue, could you help? Thanks in advance