Metacoda Security Plug-ins – Page 9

Capability Reviewer Preview: who has access to a capability and how?

The next version of the Metacoda Security Plug-ins includes a new Capability Reviewer. This new feature provides the ability to review who has access to a specific capability and by what paths a user, group or role acquires that capability.

As an example of how this is useful I’ll step through a scenario where we want to assess what needs to be done to avoid granting a specific capability to a specific user. If you have ever tried to make sure a user doesn’t have a particular capability then I’m sure you have seen this type of scenario. Lets say Bob Baxter is our user and he has the Drill to Detail capability in SAS® Web Report Studio 4.3 but he shouldn’t have.

We have to find all the roles that provide that capability directly to him and make sure he isn’t a direct member of the role. We need to remember that capability acquisition is cumulative and capabilities can’t be denied. It only takes 1 role to provide him the capability for him to have it. He can also get access to the capability through his, possibly nested, group memberships if those groups are members of a role that provides the capability. He can also get access through capability contributions, from contributing roles, to a role he is a member of directly or indirectly.

So now lets say Bob has been removed as a direct member of any roles that provided the capability but he still has the capability. Chances are he has acquired the capability indirectly through one of the groups that he is a member of (either directly or indirectly through nested group members, or implicitly through SASUSERS or PUBLIC). That means we need to track down how those groups have the capability and either remove him from the group or remove the group from the role (taking into account removing him from the group and/or removing the group from the role could have significant impacts elsewhere).

So where do we start with this? How do you find out which users, groups and roles have access to a specific capability and how they have access to that capability? This is where the Capability Reviewer in the upcoming V2 release of our Metacoda Security Plug-ins shines. The following screenshots show how we can use the Capability Reviewer to find this information.

The first screenshot below shows the initial view of the Capability Reviewer. It shows a list of all capabilities. Clicking on a capability shows all of the users, groups and roles that have access to that capability. This is presented in a tree format on the left and a table format on the right. The tree shows the various paths from the capability, through the roles, to the groups and users (including nested groups and contributing roles). The screenshots in this post are quite small, but you can click on any of them to view them full size.

We are interested in a specific capability, so we type drill in the filter bar to limit the display to those capabilities that include the text drill in their path/name or description. The result is shown in the next screenshot …

In the screenshot above (click it to view full size) we can see the filter bar has been used to find the Drill to Detail capability in SAS® Web Report Studio 4.3. The capability has been selected and we can see in the tree and table below it who has access to that capability. There are quite a few identities listed, but we are interested in a specific user (Bob). The next screenshot shows how we can look specifically at Bob’s access to that capability …

In the screenshot above (click it to view full size) we can see the filter bar within the Roles & Members tab, has been used to find Bob. By default the tree and table only show the shortest path by which the user acquires that capability, but if we want to ensure a user doesn’t have a capability we need to find and eliminate all capability access paths for that user, so we also click the “Show Duplicates” button on the filter bar. The table then shows all 3 paths by which Bob acquires the Drill to Detail capability:

Bob Baxter is a member of the implicit PUBLIC group which is a member of the Web Report Studio: Report Viewing role which has been granted the capability.
Bob Baxter is a member of the implicit PUBLIC group which is a member of the Web Report Studio: Report Creation role which has been granted the capability.
Bob Baxter is a member of the Vegas Enterprises: Executives group which is a member of the Vegas Enterprises: Report Consumers group which is a member of the Vegas Enterprises: Report Consumer Role which has Web Report Studio: Report Viewing as a contributing role which has been granted the capability.

As you can see the Capability Reviewer allows us to find exactly how Bob acquires the capability through all of the potential paths. To make sure he doesn’t have the capability we need to ensure he is not in any of these paths. How that is done for you will depend on how you have set up your groups, roles and capabilities within your SAS platform installation. The simplest way will be to either remove Bob from the relevant roles and/or groups, or remove the capability from the relevant role(s). However, we also need to bear in mind that, depending on the changes you make, this can have significant impacts to roles, capabilities and access controls elsewhere. A more realistic outcome from a requirement to effectively remove a capability for a user is that the roles & capabilities implementation needs to be re-assessed and re-planned. Roles & capabilities needs careful planning but that’s a bigger story than we have time for here.

Our other Metacoda Security Plug-ins reviewers can help you assess the potential impact of any changes you might plan to make. For example: the Role Reviewer can help you find out what other users and groups might be affected by changes to a role’s capability set; the Group Reviewer can help you find out what other users and groups are direct or indirect members of a group you might want to change role memberships for.

If you’d like to find out more about the Capability Reviewer, or evaluate a beta version when it becomes available soon, then you can either send me a message or contact us via the Metacoda contact form.

If you are attending SAS Global Forum 2011 in Las Vegas this week then you can also see the Capability Reviewer in action by visiting us at the Metacoda booth (#106) in the demo area.

Updated 08Sep2011: Metacoda Security Plug-ins V2.0, and the new Capability Reviewer, is now available to customers and evaluators. If you would like to try it out you can request a evaluation from the Metacoda web site.

User Reviewer V2: Sneak Peek

If you’re a SAS platform administrator who manages a SAS metadata security implementation then you might be interested in this sneak peek of some of the enhancements in the next version of our Metacoda Security Plug-ins (custom plug-ins that can be installed into the SAS Management Console). We’ve been hard at work updating our plug-ins to provide enhanced views of the great new metadata security improvements in SAS® 9.2 like roles and capabilities.

Roles and capabilities in SAS 9.2 let you control, via the SAS metadata server, user access to functionality and features in SAS client applications such as SAS Enterprise Guide, SAS Add-in to Microsoft Office, SAS Web Report Studio and SAS Management Console. For an excellent overview of roles and capabilities I’d definitely recommend reading Kathy Wisniewski’s SAS Global Forum 2010 paper “Be All That You Can Be: Best Practices in Using Roles to Control Functionality in SAS® 9.2“.

We’re improving our Metacoda Security Plug-ins User Reviewer by adding Roles and Capabilities tabs that provide extended information about the roles and capabilities for a user. This screenshot (click the thumbmail to view the full size image in a new window) shows a preview of our new Roles tab:

In the screenshot you can see that I have tracked down a particular user and am looking at all of the roles he is associated with. It shows:

direct role associations, where a user is a member of the role directly
indirect role associations, where the user is a member of a group (possibly nested) and that group is a member of the role
implicit role associations, where the user is associated with the role through the one of the implicit groups (SASUSERS and PUBLIC)
contributed role associations, where the user is associated with a role indirectly through that roles contribution to another role the user is associated with

Essentially this new Roles tab allows you to answer the question: is a user associated with a particular role, and if so, by what means are they associated?

Another question administrators want to answer for a given user is what capabilities do they have or not have, and why? That’s where our new User Reviewer Capabilities tab helps. Here is another screenshot (once again click the thumbnail to enlarge):

This screenshot shows the Capabilities tab where you can see a list of all the capabilities and whether or not the user has been granted access to them. If the user has been granted access to a capability it also shows which role is providing them with the capability and the membership path from the user to the role. If you’ve ever tried to track down why a user has an unexpected capability then I’m sure you’ll appreciate how useful this is.

That’s it for this sneek peak, but if you are going to SAS Global Forum 2011 in Las Vegas this year, and you’d like to find out more, then please pop by and visit us in the SAS Alliance Cafe for a demo – we’ll be in booth #106.

BTW if anyone out there is interested in trying out a beta version then we’re looking for a few more beta testers. If you have a SAS metadata server in a development, test, or sandpit environment and would like to test drive our plug-ins then let me know. You can contact me through this blog, Twitter, my LinkedIn profile, the Metacoda web site or even in person at the SAS Global Forum in a few weeks time :)

Authorization Manager Plug-in and Custom Metadata Permissions

Something I’ve only noticed recently, which I haven’t seen in the SAS® 9.2 What’s New documentation, is an apparent change relating to support for custom, or user-defined, metadata permissions in the SAS Management Console Authorization Manager plug-in.

I hadn’t noticed this change before because I’ve not yet had a need for custom metadata permissions myself. I don’t think they are used very much and personally I’ve only heard of a single site that has used them in a custom application.

A while back someone asked me about support for custom permissions in our Metacoda Security Plug-ins product. It was this request that originally alerted me to the custom permissions feature in SAS 9.1.3. Whilst planning a new release of our plug-ins, I had been thinking about ways to add support for them, but when I went looking for custom permissions in SAS 9.2 they appeared to have vanished.

Below is a screenshot fragment of the SAS Management Console 9.1 Authorization Manager plug-in. Notice the Permissions folder where, with a right click, you can create your own custom permissions.

Compare that with this next screenshot fragment for the Authorization Manager plug-in from SAS Management Console 9.2. Notice there is no longer a Permissions folder.

It looks like the ability to add custom permissions has been removed – at least via SAS Management Console anyway. Considering all of the many metadata security improvements in SAS 9.2 relating to applicable permissions, effective permissions and changes in inheritance paths, I think I can understand why. To provide support for custom permissions in the SAS Management Console Authorization tab in SAS 9.2 would require gathering much more information about how the custom permission should be handled than the simple name and description collected in SAS 9.1.3 would provide – you would have to consider what objects types it applies to and how the permission should be inherited from object to object.

So, for now, it looks like we won’t be needing to add support for custom permissions in our Metacoda Security Plug-ins product. However, if you have any information about the potential for custom metadata permissions in SAS 9.2 and/or would like to see them supported in our plug-ins then please let me know. I’d also be interested in hearing from anyone who might have used custom metadata permissions in SAS 9.1.3 and finding out about your experiences in migrating your custom applications that used them to SAS 9.2.

You can find more information about custom metadata permissions in the SAS Management Console 9.1.3 User’s Guide > Managing Authorizations > Managing Permissions

SAS 9.2 OLAP Cube Identity Driven Member Level Security

Identity-driven member level security for SAS 9.2 OLAP cubes, as the name suggests, uses the identity of the requesting user to restrict access to specific members of a dimension, and in so doing control the subset of cube data that the user has access to. Imagine that you have an OLAP cube containing sales data for all sales people, but an individual sales person is restricted to only seeing a subset of that data. Among all of the other dimensions, perhaps one of the dimensions in the cube has the sales person’s user id contained within it. We can construct an identity-driven MDX expression that can be used to filter the cube data on that dimension to only include members that match an identity attribute such as user id. Of course there are also other ways of using other identity attributes to limit access to cube data but I’ll keep it simple in this example.

To quote from SAS® 9.2 OLAP Server: User’s Guide > What’s New in the SAS 9.2 OLAP Server > Security For Cubes documentation:

Identity-driven security enables you to substitute identity values in a permission condition. It enables you to insert a placeholder into the permission condition that, at query time, gets resolved to a string that represents the user identity.

You can see the various identity value placeholder properties available to you in: SAS® 9.2 OLAP Server: User’s Guide > Modifying and Maintaining Cubes > Cube Security > Identity-Driven Security. Some of the ones that we might use with an individual user include:

SAS.Userid – the normalized user id for the person querying the cube (e.g. BILLY@MYDOMAIN)
SAS.IdentityName (or SAS.PersonName) – the name of the person querying the cube, as seen in the SAS Management Console User Manager plug-in (e.g. Billy Baxter)
SAS.ExternalIdentity – a site specific identifier for people that have been bulk-loaded into metadata from directories such as Active Directory, OpenLDAP etc. This could be an employee number for example (e.g. e12345678)

In this post I’m going to highlight a simple example for a couple of reasons. Firstly, whilst the documentation includes lots of general examples, I couldn’t find many that demonstrate the use of an identity-driven member level security filter. Now I can refer to this post when I need to remember how it’s done. Additionally, during my investigation, I kept getting stumped by an MDX syntax error dialog during the definition of the permission condition. It took me a while to discover that I could just accept the error because at run-time, when the identity substitution happens, the MDX will become syntactically valid. Publishing my experiences might help others save themselves a bit of time and frustration too.

My example cube was built from a SAS table that included a sales person user id column. The value of that column for each row was the user id for the sales person that had access to that data – i.e. that row would contribute to the cube subset that the sales person would be able to see when they query the cube. The format of the user id in that column was quite specific. It was in a normalised format, as explained in the SAS documentation. The user id was converted to uppercase and included a domain suffix on Windows. In Windows environments it would most likely be in the format USERID@DOMAIN (e.g. BILLY@MYDOMAIN), whereas in some UNIX environments it might be in the format USERID (e.g. BILLY). If you don’t know what the format is for your environment you can probably work it out by looking in the SAS Metadata Server logs or turning on some of the debugging options for the SAS OLAP Server. If you don’t get the format correct you are likely to end up with an MDX filter that filters out everything so there is nothing to display!

Information on how to set up member level security on a dimension can be found in the SAS® 9.2 OLAP Server: User’s Guide > Cube Building and Modifying Examples > Setting Member Authorizations On A Dimension. I won’t duplicate that information here, suffice to say that in my example cube I want to apply an identity-driven member level security filter on normalized userid members in the lowest level of a DimSalesPerson dimension using the SAS.UserId property. I want it to apply to all known SAS identities (i.e. SASUSERS group) so I start out by adding an explicit grant of the Read permission to the SASUSERS group on the DimSalesPerson dimension in the cube. This enables the Edit Authorization… button that I click to open the Add Authorization dialog. I select the radio button for Create an advanced MDX expression using the expression builder then click the Build Formula… button. I can then enter the MDX expression to filter the cube data for the current user identity.

When constructing the MDX filter expression you can insert placeholders for Identity Values (such as SAS.Userid) which will be substituted at run-time with the appropriate value corresponding to the user making the request. These Identity Values need to be in a specific format SUB::IdentityValueName (e.g. SUB::SAS.Userid). Whilst I couldn’t find the format documented anywhere, you will see an example of it if you use the Build Formula dialog’s Data Sources list to add an Identity Value into the expression. There is also an example screenshot showing SUB::SAS.IdentityGroups at the bottom of SAS® 9.2 OLAP Server: User’s Guide > Cube Building and Modifying Examples > Setting Identity Driven Security. It doesn’t show it used within a larger MDX expression however.

In my example I want to use the normalized user id to choose the appropriate member of the DimSalesPerson dimension, so I type the following into the Build Formula dialog’s Expression Text field and click the OK button.

{[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]}

Tip: if you want help in working out the syntax for this filter, open the OLAP cube in SAS Enterprise Guide, use point-and-click to filter in the slicer on an example user id in the appropriate dimension. When the table looks like it displays data for a single user, view the MDX:

SELECT CrossJoin({[DimTime].[All Time].Children }, {[Measures].[SaleAmountSUM] }) ON COLUMNS,
{[DimGeo].[All States].Children } ON ROWS
FROM [TransactionsCube]
WHERE ([DimSalesPerson].[All Sales People].[BILLY@MYDOMAIN])

You should see what you need in the MDX WHERE clause and you can replace the user id with the identity property – SUB::SAS.UserId in this case.

This is where, initially, I got stuck for a while. I saw the following error dialog because it was not valid MDX since SUB::SAS.Userid was not actually a real member.

It turns out this was ok, because although it was not itself a valid member, at run-time when the user queried the cube it was replaced with the real member value (e.g. BILLY@MYDOMAIN) and became valid MDX (assuming of course that BILLY@MYDOMAIN was a member!).

Tip: How can I tell whether the syntax error is due to the placeholder or the rest of the MDX? One method that I use is to start out by using a constant for a valid member (e.g. {[DimSalesPerson].[All Sales People].[BILLY@MYDOMAIN]}) instead of the placeholder. Verify the MDX syntax is ok for the constant value and that the filter works as expected when you open the cube (i.e. you see the subset of data for that constant user). If all is ok then go back and replace the constant with the placeholder (e.g. {[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]})

So even though I had this error I could click the OK button. I then saw the following warning dialog where I could confirm I did indeed want to save the invalid MDX expression as a permission condition by clicking the Yes button.

With the identity driven member level security filter applied, now whenever the cube was queried the user would only see the subset of data they had been granted access to.

As I mentioned earlier this is a relatively simple example. In reality you would probably want to apply the identity-driven filter to a suitable sales-people group and allow some management-level people to see larger or complete subsets of the cube. You might also want to filter based on group memberships and probably make the filter a bit more robust in case the user id doesn’t exist in the cube. Also, if you are running the third maintenance of SAS 9.2 you could also apply the permission conditions in batch using a permissions table.

The method I use to review all the SAS OLAP cube member level security permission conditions that are present in metadata is to use our Metacoda Security Plug-ins ACE Reviewer. You can click on the image below if you want to see it full size:

Finally, the following SAS usage notes can be very helpful for debugging or troubleshooting with the SAS OLAP Server:

BTW: The inspiration for this post came from a question asked on the SAS Discussion Forums > SAS Stored Processes > Thread: Role based security as input parameter, data read from oracle db on demand

Update 02Feb2011: If you’re reading this post then you might also be interested in a related post on Angela Hall’s Blog: Implementing OLAP Member Level Security for All Vantage Points

Update 03Feb2011: In some testing I found the following permission condition useful for filtering on a user id level (that has unique member values across the entire dimension) in a cube at an arbitrary level (i.e. not knowing/specifying how deep the level is within the dimension/hierarchy or what it’s parents are) and then including all unknown ancestors above it.

Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESPERSON_ID' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.UserId')).Item(0))

I’m likely to forget this so am jotting it down here in case I might need to use it as the basis for future OLAP member level security work. I don’t know about you but I always find MDX work is like mental gymnastics, better than sudoku :) , so I like to keep examples of MDX functions in action. I hope it might give you some ideas too.

Update 04Feb2011: Thanks to a post from Bob in the SAS discussion forum thread linked above I discovered the SAS MDX <!–CONDITION–> ‘operator’ today (looks like an XML comment!). It is used for specifying member level security permission conditions when you have more than one hierarchy in the dimension you are securing. I can’t find it in any of the SAS reference docs but there are a couple of usage notes that mention it:

Identity Hierarchy

In my upcoming SAS Forum Australia & New Zealand 2010 presentation Best Practices with SAS® 9 Metadata Security, I mention that is important for platform administrators to have an understanding of the Identity Hierarchy concept. In this post I provide a bit more information about the identity hierarchy, and associated identity precedence, than time allows in the presentation.

Learning about the Identity Hierarchy

If you want to lean more about this topic, I would suggest any or all of the following:

Read the SAS® 9.2 Intelligence Platform Security Administration Guide, Chapter 3 Users, Groups, and Roles, specifically the section titled Identity Precedence
Read the SAS® 9.1.3 Intelligence Platform Security Administration Guide, Second Edition, Chapter 2 Understanding Authorization, specifically the section titled To Whom Can Permissions Be Assigned?
Attend the SAS training course SAS Platform Administration: Fast Track. I think this is one of the best courses available from SAS and is an essential foundation for any platform administrator. I should point out that I do teach this course from time to time for SAS Institute Australia so I might be a little biased ;)

Visualizing the Identity Hierarchy

The identity hierarchy consists of the user, all of the groups that they are a direct member of, all of the groups that they are an indirect member of via nested group memberships, as well as the SASUSERS and PUBLIC groups that they are implicit members of. All of these identities can be arranged in a hierarchy. The following screenshot shows the identity hierarchy for a fictitious user Kate Knowles:

This screenshot was generated and extracted from the identity hierarchy tab available in our Metacoda Security Plug-ins User Reviewer. This tab provides a visualization of the identity hierarchy for any selected user in metadata. If you are wondering what the various icons mean:

represents a user (in this particular case a user bulk-loaded into metadata from AD or LDAP);
represents a normal group;
represents a group that has been bulk loaded into metadata (from AD or LDAP); and
represents a group that ultimately contains itself (via a circular reference)

At the top of the identity hierarchy (level 0), with the highest identity precedence, is Kate Knowles herself. Underneath Kate (at level 1), and with the next highest identity precedence, are the groups that Kate is a direct member of ( Northern Region HR for example). These level 1 groups are those where you will find Kate listed on the group’s Members tab. Underneath those level 1 groups are the level 2 groups (e.g. Northern Region), that have the level 1 groups as direct members (i.e. you will find the level 1 group listed on the Members tab of the level 2 group). Kate is an indirect member of the level 2 groups. This process continues until we exhaust all of the nested groups, which is at level 3 for Kate. Finally, at the deepest levels are the implicit groups: SASUSERS for everyone with a metadata identity; and PUBLIC for everyone with valid credentials.

You can see the screenshot shows two different views of the identity hierarchy: 1) the tree view shows the member relationships between the groups as well as the shortest path by which the user is a member of any given group; and 2) a flattened table view which just shows the user and the direct, indirect and implicit groups in order of identity precedence.

The identity hierarchy shown above is a simplified smaller representation of a complete identity hierarchy. It has had redundant duplicate groups that have the same or lower identity precedence removed, so that groups are only shown once at their highest level of identity precedence. To illustrate this a simpler identity hierarchy is shown below which includes a duplicate group reference. In this example, Tara Thompson is a direct member of both the Australia and the Asia/Pacific groups – these are shown at level 1. The Australia group is itself a direct member of the Asia/Pacific group too, and so the Asia/Pacific group is also a level 2 group and shown greyed out below – ordinarily we hide this lower precedence group from the identity hierarchy. You might notice that there is no need for Tara to be a direct member of the Asia/Pacific group because she already has that membership indirectly through the Australia group membership. In fact this multi-level membership has the potential to cause unwanted conflicts. I plan to post about this type of identity hierarchy issue later on.

Importance of the Identity Hierarchy

The identity hierarchy is important because it is used to determine identity precedence which plays a key role in resolving conflicts. It can also be used to find any shared logins accessible to the user, via their group memberships, by walking the tree looking for logins on any of the groups.

To quote from the SAS® 9.2 Intelligence Platform Security Administration Guide:

Identity precedence affects authorization decisions when a user has more than one relevant permission setting because of the user’s group memberships. Identity precedence affects login priority when someone has more than one login in an authentication domain. Identity precedence is not relevant for roles

In SAS 9.1.3 the identity hierarchy is only used to resolve authorization decisions and find shared logins. Its use in choosing a higher priority login is new in SAS 9.2.

A good understanding of the identity hierarchy allows you to understand and troubleshoot conflicts, and hopefully help you plan to avoid them where possible.

Potential Identity Hierarchy Issues to Avoid

Most of the time the identity hierarchy is quite straightforward, but you can also get into some odd situations. Some of the potential issues to avoid include:

As shown above, in the identity hierarchy for Tara, a group can potentially appear at multiple levels in an individual’s identity hierarchy. Whilst this can be normal, care may need to be taken with access controls to avoid potentially unwanted outcomes. I plan to post an example soon which provides an example of an unwanted outcome due to an identity hierarchy conflict and “non-implicit-group permission denials”.
It is possible to end up with circular references in the identity hierarchy. This occurs when a group contains itself as an indirect member through a nested group membership. The Metacoda Security Plug-ins User Reviewer detects these and tags the group with a special icon . The identity hierarchy shown earlier for Kate provides an example of this: Kate is a direct member of the Human Resources Managers group which itself is a direct member of the Human Resources group and that group in turn is a direct member of the Human Resources Managers group (it’s own parent).
Whilst it’s possible to add the implicit groups SASUSERS and PUBLIC as direct members of other groups, this is not a recommended practice. It is however quite normal to add the implicit groups to roles with SAS 9.2. In another quote from the SAS® 9.2 Intelligence Platform Security Administration Guide:

To avoid introducing unnecessary complexity, don’t make PUBLIC or SASUSERS a member of another group. For example, if you make PUBLIC a member of GroupA, then a user who is an indirect member of GroupA (through his automatic membership in PUBLIC) has GroupA as his lowest precedence membership. This contradicts the usual expectation that every user’s lowest precedence membership is PUBLIC

To be continued …

I am planning on following up this post with some additional examples and practices that build upon this discussion of the identity hierarchy, but that’s it for now. I hope this post is useful to those who may want to find out more than I can discuss in my forum presentation. Please let me know if you have any comments or questions.