9 thoughts on “SAS 9.2 OLAP Cube Identity Driven Member Level Security”

  1. Hi Paul,

    This helped us a lot. At the client we are working right now somebody got stuck exactly as you describe trying to implement this for the first time for cubes. However, we haven’t got it working in the way we wanted initially, so a question remains. See further below.

    One suggestion: at the start of your blog you mention the problem of getting to know how the value for SAS.UserId will look like. You can see that when you go into Information Map Studio and start defining a filter using Identity Values.
    When you get to the point in the dialog where you can select the different identity properties you will see the current values for your own identity.

    Also, we noticed that when you are in the Build Formula dialog and add Identity Values to the expression from the Data Sources tab the placeholder is surrounded by double quotes, something like <code {[DimSalesPerson].[All Sales People].["SUB::SAS.Userid"]}. You will have remove those quotes.

    Now our remaining problem.
    The cubes we are building are to be accessed by people who should be allowed only slices regarding their organisation. We will have several individuals per organisation. So our idea is to group identities in metadata groups, and grant access to slices through the groups. So we have tried to use the SUB::SAS.IdentityGroups construct in the formula.
    But we have not succeeded in creating MDX that works, probably because this will resolve in a list of values and we don’t know how to handle this in MDX.

    An alternative would be to grant all groups explicit access to each respective slice, but that would be a rather cumbersome task…

    Any suggestions welcome!

  2. Hi Frank,

    Thanks for your comment and the additional points about using the identity driven properties in SAS Information Map Studio.

    Regarding the use of SAS.IdentityGroups, I know what you mean about trying to construct an MDX expression from the list of string values. I have yet to find a way to do the required string manipulation/comparison in MDX. Perhaps there is a way, but I haven’t found it yet :)

    I would suggest you go down the path of setting multiple permissions conditions as appropriate for the relevant groups. If you were to do this manually it might get tedious especially if you had to repeat it at some later date. If you are running SAS 9.2 M3 or later then you could look into the batch permissions table facility. This allows you to apply all of the permission conditions from the contents of a SAS table. Being a SAS table you might be able to generate it from code if there is a pattern to the conditions and groups.

    I tried the batch permissions facility a little while back and took a few notes intending to write a blog post about it – but haven’t finished it yet ;). The documentation for permission tables in SAS 9.2 can be found in a short section Applying Batch Security with Permission Tables in the SAS 9.2 OLAP Server: User’s Guide under Modifying and Maintaining Cubes, Cube Security (look at the very end of the page). I spent a bit of time looking into the SAS dataset structure. I couldn’t find it documented for SAS 9.2 but the SAS 9.3 version of the same document details the table format and its looks very similar to the table I saw in SAS 9.2.

    There are also a couple of SAS usage notes you might be interested in:

    Problem Note 40424: Permissions tables for use with member-level security are not supported for UNIX libraries or for libraries that use relative paths – I had this issue on my Linux installation – there is a hotfix or you can switch to an absolute path.

    Problem Note 42603: The reload button is not available after using code to create a batch permissions table

    I hope this helps.

    Cheers
    Paul

  3. Hi Paul,

    Thanks for the suggestions.

    The permission tables were quickly implemented for a first cube, and can be used for all other cubes as well.
    We had already set up things in such a way that there is simple relationship between the group identities to which access is to be granted and the member names to be put into the MDX string.

    A look at the log after applying the permission table also gives you some nice insight in how these things are specified in the metadata – if you are interested in such things ;-).

    Cheers,

    Frank

  4. Hi Frank,

    No problem. I’m glad the permission table suggestion helped you implement your OLAP member level security requirements. It’s good that SAS provided a batch method for applying the permission conditions.

    Thanks for coming back to the blog and sharing your experiences.

    Cheers
    Paul

  5. I just want to add a warning when using the permissions per member utility. I haven’t investigated all the subtleties yet, but this is what I found so far, and as far as I understand it.

    Using a ‘permissions table’ you can very nicely grant a read per member. But before an identity can get there it has to have a read for the cube as a whole. That can be accomplished by granting a read for each group, but it is easier and more elegantly to do that through a larger group (that the users in all those smaller groups belong to). In our case SASUSERS seemed a good choice.
    That works, but if you have a user that directly or indirectly belongs to that larger group, but is not a member of any of the smaller groups, that user will by default get access to all the members!

    This is so because the dimension object to which you applied the permissions per subgroup also inherited the read for the larger group you assigned to the cube object. You cannot remove that, but you can and should change the grant into a deny.

    Alternatively you could use a separate group to which you grant the read on the cube, and make only the smaller groups member of the larger group. That way no user can ever get read access to the cube without being limited to particular member(s) of the dimension in question.

    This seems a safer way, but I have yet to figure out how to fit that in the larger security scheme without creating a set up that will be difficult to maintain.

  6. Hi Frank,

    Thanks for the follow up comment about your experiences with the permissions table. I can see how if you use SASUSERS as the group to grant the read permission on the cube (or the folder the cube is in) but not all SASUSERS should have access to the cube contents then, because of the lack of a permission condition for SASUSERS, they would get access to all of the cube data since none of the permission conditions are relevant for their identity. Have you tried adding a permission condition for SASUSERS (or PUBLIC) that filters out all of the data (equivalent to “where false”) for such cases? I imagine from a security modelling perspective it might be better to avoid such gaps in the first place and only grant the read permission to the same subset of SASUSERS that are present in the permission conditions (as you suggested).

    Perhaps it is worth making a SASware ballot suggestion to suggest that by default, as a conservative fail-safe security measure, in the event of such gaps, when there are permission conditions on a cube, if the user’s identity is not present in any of the cubes permission conditions, and they are not a member (direct or indirect) of any of the groups in the cubes permission conditions, then it returns an empty result set?

    Cheers
    Paul

  7. Regarding Paul’s first suggestion, to add a condition that denies SASUSERS access to all members, I haven’t tried that. It would create a conflict between two conditions. If the general strategy that a right ‘from nearby’ supersedes the right from further away works here as well, things would be OK in the case I described, but I haven’t tested it.
    Moreover, when the right to a member is indirect as well it might not work.

    The second suggestion, for a SASballot item looks worthwhile, but yet again it might create problems in more complex situations. And it should always possible to get back the current behaviour without complicated constructions, because that might be the desired situation.

    Another observation from working with the permissions.

    If you give users the right to several members of a dimension, by creating in list in the permission condition, like
    {[Dimension].[Dimension].[All members].[mem1],
    [Dimension].[Dimension].[All members].[mem2],
    [Dimension].[Dimension].[All members].[mem3]
    }
    it is the order in which you specify the members in this condition that determines the order in which the result is displayed in (e.g.) a web report. The specification in the cube metadata (formatted or unformatted order, asc or desc) no longer has effect.

    I don’t know what happens if an identity is a member of several groups, and thus has a collection of members that come from different conditions…

  8. Hi Frank,

    It has been a while, but I just noticed this quote in the SAS documentation which clarifies the situation when there is a conflict between multiple conditions due to multiple memberships. It can be found in the SAS® 9.2 Intelligence Platform: Security Administration Guide under Fine-Grained Controls for DataHow are Fine-Grained Controls Assigned?:

    A permission condition is applied only if it is on the setting that is closest to the requesting user. Other conditions that are relevant because of further-removed group memberships don’t provide additional, cumulative access. If there is an identity precedence tie between multiple groups at the highest level of identity precedence, those tied conditions are combined in a Boolean OR expression. If the identity precedence tie includes an unconditional grant, access is not limited by any conditions.

    Cheers
    Paul

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.