I was asked an interesting question today about cell security – how can you get it to work with calculated members defined in the WITH clause or the session? If, for example, you create a role on the Adventure Works database with the following expression in the Read Permissions box for cell security:
[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
AND
( [Measures].currentmember IS [Measures].[Reseller Sales Amount]
OR
[Measures].currentmember IS [Measures].[Reseller Order Count])
If you then run the following query when connecting via the role:
WITH
MEMBER MEASURES.TEST AS
[Measures].[Reseller Sales Amount] * [Measures].[Reseller Order Count]
SELECT
{MEASURES.TEST, [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Count], [Measures].[Reseller Gross Profit]}
ON 0,
[Geography].[Geography].MEMBERS
ON 1
FROM [Adventure Works]
You’ll see the the following results:
This is pretty much what you’d expect – you only see values for Australia for Reseller Sales Amount and Reseller Order Count. The problem here is how you can grant access so the user can see the measure TEST which has been defined in the WITH clause. If you try the following expression in the role:
[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
AND
( [Measures].currentmember IS [Measures].[Reseller Sales Amount] OR [Measures].currentmember IS [Measures].[Reseller Order Count] OR [Measures].currentmember IS [Measures].TEST)
You see the following error in a few cells in the query above:
#Error CellPermission (3, 162) The member ‘[TEST]’ was not found in the cube when the string, [Measures].[TEST], was parsed.
And in any case, how can you know in advance what the name of the calculated measures you want to grant access to are going to be anyway?
What we actually want to do is retain control of all measures, calculated or otherwise, that are defined on the cube, but allow access to any calculated measures defined in the WITH clause or the session. The way to do it is to rely on the fact that cell security is evaluated after the MDX Script and do the following. First define a named set on the cube at the end of the MDX Script something like this:
CREATE SET CURRENTCUBE.ALLMEASURES AS MEASURES.ALLMEMBERS;
This set contains all of the measures, including calculated measures, that have been created on the MDX Script – except those that have their Visible property set to False (these you’d have to add to the set manually).
Then use an expression such as this in your role:
[Geography].[Geography].currentmember is [Geography].[Geography].[Country].&[Australia]
AND
( [Measures].currentmember IS [Measures].[Reseller Sales Amount] OR [Measures].currentmember IS [Measures].[Reseller Order Count] OR
Count(Intersect({[Measures].currentmember},{ALLMEASURES}))=0)
The last line is the new part: it checks to see whether the currentmember on Measures is included in the set we’ve just defined. If it is, we can be sure it’s defined on the cube. If it isn’t, it must be defined either in the WITH clause or in the session so we can grant access to it. If you then run the query again, you get the following results:
As you can see, we can now see the value for TEST. Of course you also need to be extremely careful you’re not using a security hole here by doing this, test thoroughly and use Read Contingent permissions as appropriate.
Nice trick, Chris! I like this solution, it can save you many headaches.
Ingenious, I must say
Hi Chris, I have Geography dimension and 4 measures ( A, B, C and D ) out of which only 2 needs Geography security (A and B ) and rest don’t based on who refresh the report. If I apply role based security on Geography , user can see all 4 measures only for selected area.. whereas I want user to be able to see C and D measures for all area and A and B measure for only for those areas where he has permission. Could you please suggest how to implement it. Thanks.
That sounds like a scenario where you have to use cell security and not dimension security.
Thank you Chris for your quick response . I am trying to use Cell security but here is detail of my cube and the problem:
1. I have userSecurity table ( This is hidden from user’s report) (useralias, AreaName ) which tells which user has access to view which area data for selected A and B measures.
2. Then I have Geography dimension (GeoKey, AreaName) on AreaName.
3. Last: 4 fact tables as (GeoKey , A ) ; (GeoKey , B) ; (GeoKey , C ) ; (GeoKey , D ) .
4. First I join usersecurity to Geodimension ( its one to many relation) on AreaName and add security that based on which user is running report he will see data for only those specific areas.
By doing that it applies security to all 4 fact tables.
5. In order to cell security I can go in celldata tab and apply security on only A and B measures but do I still need to join usersecurity with GeoDimension ?
and if answer to this question is yes.. a ) how do I say that for C and D measures it should show data for all Areas to that user and not just selected ones.
b) And if while doing cell security I don’t need to join usersecurity and GeoDimension then how do I specific for A and B measures to show data for required area only based on useralias.
Thanks..
Thank you Chris for your quick response . I am trying to use Cell security but here is detail of my cube and the problem:
1. I have userSecurity table ( This is hidden from user’s report) (useralias, AreaName ) which tells which user has access to view which area data for selected A and B measures.
2. Then I have Geography dimension (GeoKey, AreaName) on AreaName.
3. Last: 4 fact tables as (GeoKey , A ) ; (GeoKey , B) ; (GeoKey , C ) ; (GeoKey , D ) .
4. First I join usersecurity to Geodimension ( its one to many relation) on AreaName and add security that based on which user is running report he will see data for only those specific areas.
By doing that it applies security to all 4 fact tables.
5. In order to cell security I can go in celldata tab and apply security on only A and B measures but do I still need to join usersecurity with GeoDimension ?
and if answer to this question is yes.. a ) how do I say that for C and D measures it should show data for all Areas to that user and not just selected ones.
b) And if while doing cell security I don’t need to join usersecurity and GeoDimension then how do I specific for A and B measures to show data for required area only based on useralias.
Thanks..
If we forget about security for the moment, it sounds like you would normally join the Geography dimension to all four measure groups. Is this right? You should not need to change this for any security implementation. It sounds like your problem is that you are setting up dimension security and cell security – if you are using cell security, you don’t need dimension security so you can skip step (4).
Thank you Chris for the response once again.
Yes I have GeoKey in all the fact table which is needed to slice on Area field.
So if I skip step 4 and create cell security for A and B measures.. how will I know which user has permission to see which area data..as that detail is in UserSecurity table which is like a bridge table between users and geography. Is there a way to write MDX in which when we are creating logic for A and B cell security measures we can join that time usersecurity with geoDimension and then check useralias as to who is running using username() function and then show the data for those specific area..
Also does this process show poor performance when we are talking about 10-15 million rows in fact table.
Is there any other approach to do this..?
Thanks
Hi Chris, Please let me know your opinion on this request or if there is a link similar to his problem which I can follow as I tried to read some articles but couldn’t get how to get both things together.
Thanks.
Yes, there are a few examples out there showing what you need: Google for “dynamic cell security”. Unfortunately all cell security comes with a performance penalty, but you’ll have to test to see how much of a problem it is for you.
I have been looking that but couldn’t find something similar to my problem..Do you think we can join user security and geography dimension while creating cell security for A and B measures… using mdx in celldata tab of the role; so that it only applies to these measures and not to C and D.
Yes, it’s definitely possible, but the MDX will be fairly complex. One other place to look for an example of how to do this is in the chapter on security in the book “Expert Cube Development”.
Hi Chris,
Thanks for book reference, Its really nice an helpful. I am able to use it and write my role.
But I think I did some minor mistake which I am unable to catch and was wondering if you can take a look at it.
Below is the structure of my dim tables
As said earlier I had A,B,C and D measures
where I want to have Geography security applied to A and B only and for C and D user should be able to see data for all area
dUsers
UserID UserAlias
1 Laxmi
2 Chris
UserSecurity
UserID AreaID
1 1
2 1
2 10
dGeography
GeoKey AreaId AreaName
1 1 UnitedStates
2 10 Germany
I created user table then bridge table called usersecurity and linked all three of them.
All Fact table have Geokey
Plus created a fact table of UserSecurity which has only count as measure(as discussed in your dynamic dimension security chapter) which is hidden from users view.
And there is no logic in dimensiondata tab as you said earlier we don’t need to do that.
Now while creating Role I added below code to celldata tab
According to which If I query the cube I should see Measure A for US only and for C and D I can see data for all areas. ( For B right now it will show data for all area as we didn’t applied security yet )
What when I run the cube I don’t see data for any measures.
It shows #Value everywhere.
Please suggest what is wrong in my query.
Also in order to secure measure B do I need to write same code replacing A with B below it or have to use union ?
Logic added to celldata tab:
NOT (
Measures.CurrentMember IS Measures.[A]
AND (
StrToSet(“[d Users].[User Alias].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]”), [Measures].[User Security Count] ) = 0
OR [d Geography].[d Geography].CurrentMember IS [d Geography].[d Geography].[ALL]
)
Thank you .
Hi Chris, I changed the code to
Measures.CurrentMember IS Measures.[A] AND
NOT (
StrToSet(“[d Users].[User Alias].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]“), [Measures].[User Security Count] ) = 0
OR [d Geography].[AreaName].CurrentMember IS [d Geography].[AreaName].[ALL]
and this shows be B , C and D measures but for A it doesn’t show me data for US it shows #Value! for all areas..
Please suggest what’s wrong with my query..
Also when we create scope to improve performance.. do we need to create one per each measure that you want to secure…
So if there are 2 measures that needs security..
first I create 2 hidden measures.. and then 2 different scope for each or can me union them both in one scope ?
Please advise.
Thank you.
It’s hard to say, but the first thing I see is that you’re using strtoset when you should be using strtomember.
Ok, I will try to switch that but don’t think if that will make difference as I used same syntax when applying dynamic dimension security and it worked fine there, But in this project as I need cell security its not throwing error either showing data.
Besides that could you suggest regarding scope .. do we have to create one for each cell that we are trying to secure ?
Hi Chris, Sorry to bother you again.. I tried with StrToMember function and it shows me data but for all area and for all measures. Instead of showing only B, C and D measures for all area and A only for United States..its showing measure A also for all area.
Which means it is not applying my geo security based on who is login..
Do I need to add dynamic dimension security on areaname along with this one or this should take care of it ?
Measures.CurrentMember IS Measures.[A] AND
NOT (
StrToMember(“[d Users].[User Alias].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]“), [Measures].[User Security Count] ) = 0
OR [d Geography].[AreaName].CurrentMember IS [d Geography].[AreaName].[ALL]
And when I tried to use the formula the way its given in book.. it makes data as #N/A for all area and for all measures..
NOT (Measures.CurrentMember IS Measures.[A] AND (
StrToMember(“[d Users].[User Alias].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]“), [Measures].[User Security Count] ) = 0
OR [d Geography].[AreaName].CurrentMember IS [d Geography].[AreaName].[ALL] )
Any idea.. why ?
No, this is just a problem with your logic I think. Unfortunately, it’s very difficult for me to work out what the problem is by just looking at the code.
I tried using scope but giving me same problem..
I created hidden measure for A
CREATE MEMBER CURRENTCUBE.[Measures].[HideA]
AS False ,
VISIBLE = 1 ;
SCOPE ([Measures].[HideA]);
SCOPE (StrToMember(“[d Users].[User Alias].& [” + Right(UserName(), LEN(USERNAME())- InStr(USERNAME(),”\”)) + “]”));
THIS = ([Measures].[User Security Count] = 0 );
END SCOPE;
SCOPE ([d Geography].[Area Name].[All]);
THIS = True;
END SCOPE;
END SCOPE;
Also added data to cell tab of role as described in book as
NOT (
Measures.CurrentMember IS [Measures].[A]
AND HideA )
When I browse the cube for measure A it shows data for all area instead of US only..
When I check measure HideA as I have set to visible = true..
It shows value as False for all areaname and for GrandTotal it shows True.. where it shows #N/A for Measure A..
Also I check the [UserSecurity Count] bridge fact table measure it shows blank for all area except for US where it shows 1..
Which is correct as I have permissions to US.
as shown below
AreaName A HideA UserSecurityCount
US 10.00 False 1
Germany 20.00 False 0
GrandTotal #N/A True 1
Which means one of the scope is not working as it should have value True for Germany as I don’t have permission as per bridge security table.. which one.. still digging.
Laxmi 🙁
I’m sorry, I don’t think I can help you – I’d have to spend some time working on the cube direct in order to work out what’s going on here.
no problem, thank you Chris.. I figured out finally and so far its working.
Hi Laxmi,
I am facing a similar issue now. Can you please explain how did you fix this?