In this post I’d like to explain how role is saved in Analysis services database, so we can learn an important conclusion in permission process design: dimension data can be defined on attribute members that are not existing yet at role update time.
How role is saved in DB? If you’ll look at “Program Files\Microsoft SQL Server\...” folder of your server, you’ll see that roles files are saved as XML files, like all other structure info of SSAS database elements (find explanation how you can see a similar version of the role XML code from Management studio in this post).
Members selection of Dimension Data tab is saved, for each secured attribute, as a set. For example:
{[VERSION].[VERSION].&[650], [VERSION].[VERSION].&[660]}
You can see the set in Advanced tab of role properties screen, under Dimension data:
When cube is browsed or queried, the attribute members list is filtered on this set. Like in a regular MDX expression, if this set includes a member that doesn’t exist, it’s ignored and set is applied without it. But member is still saved in role source code! So if later this member will be added to the attribute, permission will be completely applied!
This fact can be important when we schedule our different processes. For example, if we want to secure “product” attribute, and new product will be added to dimension only after next process, we can already run permission process (or edit it manually in advanced tab) earlier when server is free and not only after process, when server CPU must be released as soon as possible for users.
2 remarks:
· If you look at Basic tab of your attribute, you will see only existing members. And if you edit the list from this tab, it will overwrite the set by a new one and members that don’t exist will be deleted.
· Membership tab of the role works differently. If you try to add a user that doesn’t exist (manually, or by AMO code), action won’t be done at all so he won’t appear in role XML code.
