CODEWITHSUNDEEP
securityssas
jrnewman42
jrnewman42

Reputation: 108

Cube-level security using CustomData

I'm new to SSAS, but have been asked to create a product with three cubes (call them "A","B", and "C" for now). Another team is creating web parts "A","B", and "C" to access these cubes, and there are roles "A","B", and "C" which determine which users can access the cubes.

My problem

The customer's SSAS server is not guaranteed to be on the same box as the SharePoint server, and so we have had issues with a user logging on to SharePoint, and SP not impersonating that user. We can't guarantee that the client will let us install Kerberos authentication, or set up SharePoint as a trusted site, and so we are investigating using the CustomData variable to pass in the ID of the current user.

Question

However, this leads to the question of implementing cube access security. Do I

  1. Create a "SharePoint" role which takes the CustomData variable, and then either grants (through dimension security?) access to the cube (through all members of a dimension?)
  2. Create a "Security cube", encapsulating who has access to what, and have the web part determine whether or not it "should" have access,
  3. Rather than store the security in a user / cube form in a fact table store it in a user / members of a dimension form (I don't like this, as the incoming data is in user / cube form, and expanding it out to members of a dimension creates "unnecessary" rows).

More background info

Currently the security is arranged in fact table that is

| UserKey | SecurityItemKey |

where Personkey links back to a user table, and SecurityItemKey currently links to a list of cubes (we have a security item table available so that users can easily list their options and check what they want).

I am currently trying option 1. above, but when I create MDX along the lines of

Exists(
  {[Security Item].[Security Item Key].&[235]},
    StrToSet("{([User].[User].[User].[" +
            CustomData() + "])}"
    ),
    'Fact Security'
    )

for a dimension security, I get errors along the lines of "The '{1}' attribute in the '{2}' dimension has a generated dimension security expression that is not valid". I use the same MDX in a query, and it's just fine.

Note that the dimension I add this to is not referenced in the MDX, and I worry that this is part of the issue, but I can't find anything yet saying "yes" or "no".

So, what am I doing wrong, and what do you recommend doing differently?

Upvotes: 1

Views: 1610

Answers (1)

jrnewman42
jrnewman42

Reputation: 108

Follow up - here's what we did:

Rather than forcing a dimension data security to secure an entire cube, we made a "Security" cube which is queried by the SharePoint process, which is then responsible for granting or denying access. Only the SharePoint user has access to this role with access to all cubes.

Normal users are still granted access to each cube individually through separate roles.

So, the security issue is that there is one role with access to all, but we felt that was acceptable, and just have to trust that the role's members are assigned appropriately.

Upvotes: 1

Related Questions