Implementing Security using restricted dimensions

Implementing Security using restricted dimensions

Post by Rinki Ja » Wed, 27 Nov 2002 02:28:36



We have cubes built on physicians revenue data. Physician is one of
the dimensions. To implement physician level security i.e. where one
person can see only his or her data, we have created a database role
and put restrcitions on physician dimension like this:

{Filter([RevLoc].[DepProv].[Prov Name].Members,
Instr(1,
[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

where [RevLoc].[DepProv] is the physician dimension.
This works fine for a while but after some time starts giving error
'Com component error'.
I checked the event log of the analysis server it says
"User proclar1, logged in from computer <computer name>, does not have
adequate permissions to open cube RVU views in database
FinanceOutpatient_Prod_925, but tried to open it."

I don't think there is a problem in the formula as it works fine for
sometime.
Has anybody got this error before? Any help would be grealy
appreciated.

 
 
 

Implementing Security using restricted dimensions

Post by Richard Tkachuk [MS » Wed, 27 Nov 2002 06:11:27


Hi Rinki,

It may be an instance when a user has no access to any member in the
dimension. If the AllowedSet is an empty set, the user will not be able to
see anything in the cube. You need to trap this and decide what to do;
something like:

strtoset(

iif(Filter([RevLoc].[DepProv].[Prov Name].Members,
Instr(1,
[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName).count>0
settostr({Filter([RevLoc].[DepProv].[Prov Name].Members,

Instr(1,
[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))})
,
"{[RevLoc].[All RevLocs]}"

)

BTW, there is a flaw in this approach. What happens if you have a user with
the username of, say, myDomain.user and another with myDomain.userX? The
user myDomain.user will automatically see the members that myDomain.userX
can see because of the way the Instr() function is used.

Hope this helps,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.


Quote:> We have cubes built on physicians revenue data. Physician is one of
> the dimensions. To implement physician level security i.e. where one
> person can see only his or her data, we have created a database role
> and put restrcitions on physician dimension like this:

> {Filter([RevLoc].[DepProv].[Prov Name].Members,
> Instr(1,
> [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

> where [RevLoc].[DepProv] is the physician dimension.
> This works fine for a while but after some time starts giving error
> 'Com component error'.
> I checked the event log of the analysis server it says
> "User proclar1, logged in from computer <computer name>, does not have
> adequate permissions to open cube RVU views in database
> FinanceOutpatient_Prod_925, but tried to open it."

> I don't think there is a problem in the formula as it works fine for
> sometime.
> Has anybody got this error before? Any help would be grealy
> appreciated.


 
 
 

Implementing Security using restricted dimensions

Post by Rinki Ja » Thu, 28 Nov 2002 08:37:38


Thanks Richard. The problem is the error comes even when the user has
access to dimension member.
To describe our problem further.. if i make a dummy change in the cube
(like adding a comment in a named set and then removing it) and then
save it, the user can open it once w/o any problem and sees his data
but next time he goes in the "com component error" comes. And then i
change the cube again(dummy change) it works fine but only once.

We did one more thing, made a copy of the entire database and
processed the cubes there. The security on the new copy worked fine
until last week. As i wrote before it seems to work fine but only for
a while.


> Hi Rinki,

> It may be an instance when a user has no access to any member in the
> dimension. If the AllowedSet is an empty set, the user will not be able to
> see anything in the cube. You need to trap this and decide what to do;
> something like:

> strtoset(

> iif(Filter([RevLoc].[DepProv].[Prov Name].Members,
> Instr(1,
> [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName).count>0
> settostr({Filter([RevLoc].[DepProv].[Prov Name].Members,

> Instr(1,
> [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))})
> ,
> "{[RevLoc].[All RevLocs]}"

> )

> BTW, there is a flaw in this approach. What happens if you have a user with
> the username of, say, myDomain.user and another with myDomain.userX? The
> user myDomain.user will automatically see the members that myDomain.userX
> can see because of the way the Instr() function is used.

> Hope this helps,
> Richard

> --
> This posting is provided 'AS IS' with no warranties, and confers no rights.



> > We have cubes built on physicians revenue data. Physician is one of
> > the dimensions. To implement physician level security i.e. where one
> > person can see only his or her data, we have created a database role
> > and put restrcitions on physician dimension like this:

> > {Filter([RevLoc].[DepProv].[Prov Name].Members,
> > Instr(1,
> > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

> > where [RevLoc].[DepProv] is the physician dimension.
> > This works fine for a while but after some time starts giving error
> > 'Com component error'.
> > I checked the event log of the analysis server it says
> > "User proclar1, logged in from computer <computer name>, does not have
> > adequate permissions to open cube RVU views in database
> > FinanceOutpatient_Prod_925, but tried to open it."

> > I don't think there is a problem in the formula as it works fine for
> > sometime.
> > Has anybody got this error before? Any help would be grealy
> > appreciated.

 
 
 

Implementing Security using restricted dimensions

Post by Rinki Ja » Fri, 29 Nov 2002 02:04:36


One more thing - the dimension on which the security role is
implemented has multiple hierarchies.
 
 
 

Implementing Security using restricted dimensions

Post by Richard Tkachuk [MS » Fri, 29 Nov 2002 02:22:59


Give me a bit to take a closer look. While I'm at it, can you let me know
what service pack you're on?

--
This posting is provided 'AS IS' with no warranties, and confers no rights.


> Thanks Richard. The problem is the error comes even when the user has
> access to dimension member.
> To describe our problem further.. if i make a dummy change in the cube
> (like adding a comment in a named set and then removing it) and then
> save it, the user can open it once w/o any problem and sees his data
> but next time he goes in the "com component error" comes. And then i
> change the cube again(dummy change) it works fine but only once.

> We did one more thing, made a copy of the entire database and
> processed the cubes there. The security on the new copy worked fine
> until last week. As i wrote before it seems to work fine but only for
> a while.




Quote:> > Hi Rinki,

> > It may be an instance when a user has no access to any member in the
> > dimension. If the AllowedSet is an empty set, the user will not be able
to
> > see anything in the cube. You need to trap this and decide what to do;
> > something like:

> > strtoset(

> > iif(Filter([RevLoc].[DepProv].[Prov Name].Members,
> > Instr(1,

[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName).count>0
> > settostr({Filter([RevLoc].[DepProv].[Prov Name].Members,

> > Instr(1,
> > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))})
> > ,
> > "{[RevLoc].[All RevLocs]}"

> > )

> > BTW, there is a flaw in this approach. What happens if you have a user
with
> > the username of, say, myDomain.user and another with myDomain.userX? The
> > user myDomain.user will automatically see the members that
myDomain.userX
> > can see because of the way the Instr() function is used.

> > Hope this helps,
> > Richard

> > --
> > This posting is provided 'AS IS' with no warranties, and confers no
rights.



> > > We have cubes built on physicians revenue data. Physician is one of
> > > the dimensions. To implement physician level security i.e. where one
> > > person can see only his or her data, we have created a database role
> > > and put restrcitions on physician dimension like this:

> > > {Filter([RevLoc].[DepProv].[Prov Name].Members,
> > > Instr(1,
> > > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

> > > where [RevLoc].[DepProv] is the physician dimension.
> > > This works fine for a while but after some time starts giving error
> > > 'Com component error'.
> > > I checked the event log of the analysis server it says
> > > "User proclar1, logged in from computer <computer name>, does not have
> > > adequate permissions to open cube RVU views in database
> > > FinanceOutpatient_Prod_925, but tried to open it."

> > > I don't think there is a problem in the formula as it works fine for
> > > sometime.
> > > Has anybody got this error before? Any help would be grealy
> > > appreciated.

 
 
 

Implementing Security using restricted dimensions

Post by Rinki Ja » Sun, 01 Dec 2002 00:34:27


I am at AS SP2 both on the server and the client. The OS is Win 2000.
 
 
 

Implementing Security using restricted dimensions

Post by Richard Tkachuk [MS » Thu, 05 Dec 2002 11:24:28


I don't have good news. There seem to be occasions when the username is
returned as an empty string. I'm not sure yet when or why this happens, but
it does seem to occur. To workaround this problem, try something like this:

{
[RevLoc].[All RevLoc] +
Filter([RevLoc].[DepProv].[Prov Name].Members, iif(USERNAME=="", 0, Instr(1,
[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName) )

Quote:}

I test to see if the username is blank and return only the all member if it
is. This also would give access to only the all member if you don't have the
users account in the member property.

I'd appreciate it if you contact me offline if you have more comments on
this and/or details when the problem occurs.

(BTW, if you do try this approach, do change the expression so that someone


Regards,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.



> Give me a bit to take a closer look. While I'm at it, can you let me know
> what service pack you're on?

> --
> This posting is provided 'AS IS' with no warranties, and confers no
rights.



> > Thanks Richard. The problem is the error comes even when the user has
> > access to dimension member.
> > To describe our problem further.. if i make a dummy change in the cube
> > (like adding a comment in a named set and then removing it) and then
> > save it, the user can open it once w/o any problem and sees his data
> > but next time he goes in the "com component error" comes. And then i
> > change the cube again(dummy change) it works fine but only once.

> > We did one more thing, made a copy of the entire database and
> > processed the cubes there. The security on the new copy worked fine
> > until last week. As i wrote before it seems to work fine but only for
> > a while.



> > > Hi Rinki,

> > > It may be an instance when a user has no access to any member in the
> > > dimension. If the AllowedSet is an empty set, the user will not be
able
> to
> > > see anything in the cube. You need to trap this and decide what to do;
> > > something like:

> > > strtoset(

> > > iif(Filter([RevLoc].[DepProv].[Prov Name].Members,
> > > Instr(1,

> [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName).count>0
> > > settostr({Filter([RevLoc].[DepProv].[Prov Name].Members,

> > > Instr(1,
> > > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))})
> > > ,
> > > "{[RevLoc].[All RevLocs]}"

> > > )

> > > BTW, there is a flaw in this approach. What happens if you have a user
> with
> > > the username of, say, myDomain.user and another with myDomain.userX?
The
> > > user myDomain.user will automatically see the members that
> myDomain.userX
> > > can see because of the way the Instr() function is used.

> > > Hope this helps,
> > > Richard

> > > --
> > > This posting is provided 'AS IS' with no warranties, and confers no
> rights.



> > > > We have cubes built on physicians revenue data. Physician is one of
> > > > the dimensions. To implement physician level security i.e. where one
> > > > person can see only his or her data, we have created a database role
> > > > and put restrcitions on physician dimension like this:

> > > > {Filter([RevLoc].[DepProv].[Prov Name].Members,
> > > > Instr(1,
> > > > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

> > > > where [RevLoc].[DepProv] is the physician dimension.
> > > > This works fine for a while but after some time starts giving error
> > > > 'Com component error'.
> > > > I checked the event log of the analysis server it says
> > > > "User proclar1, logged in from computer <computer name>, does not
have
> > > > adequate permissions to open cube RVU views in database
> > > > FinanceOutpatient_Prod_925, but tried to open it."

> > > > I don't think there is a problem in the formula as it works fine for
> > > > sometime.
> > > > Has anybody got this error before? Any help would be grealy
> > > > appreciated.

 
 
 

Implementing Security using restricted dimensions

Post by Richard Tkachuk [MS » Thu, 05 Dec 2002 11:33:06


Hi,

I looked into the problem and I don't have good news. The problem is that
sometimes the Username function returns a blank string. I don't know the
condition when this occurs or how prevalent it is, but it does seem to
occur.

To work around the problem, you'll have to test for this condition.
Something like:

{[RevLoc].[All RevLoc] +
{Filter([RevLoc].[DepProv].[Prov Name].Members,  iif(username=="", 0,
Instr(1,
[RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName)))}

This allows access to the all member. It protects you if the username
resolves to a blank string.

BTW, if you do continue along this path, do defend yourself when from string
comparisons when someone's account is contained within another. For example,


Cheers,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.



> Give me a bit to take a closer look. While I'm at it, can you let me know
> what service pack you're on?

> --
> This posting is provided 'AS IS' with no warranties, and confers no
rights.



> > Thanks Richard. The problem is the error comes even when the user has
> > access to dimension member.
> > To describe our problem further.. if i make a dummy change in the cube
> > (like adding a comment in a named set and then removing it) and then
> > save it, the user can open it once w/o any problem and sees his data
> > but next time he goes in the "com component error" comes. And then i
> > change the cube again(dummy change) it works fine but only once.

> > We did one more thing, made a copy of the entire database and
> > processed the cubes there. The security on the new copy worked fine
> > until last week. As i wrote before it seems to work fine but only for
> > a while.



> > > Hi Rinki,

> > > It may be an instance when a user has no access to any member in the
> > > dimension. If the AllowedSet is an empty set, the user will not be
able
> to
> > > see anything in the cube. You need to trap this and decide what to do;
> > > something like:

> > > strtoset(

> > > iif(Filter([RevLoc].[DepProv].[Prov Name].Members,
> > > Instr(1,

> [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName).count>0
> > > settostr({Filter([RevLoc].[DepProv].[Prov Name].Members,

> > > Instr(1,
> > > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))})
> > > ,
> > > "{[RevLoc].[All RevLocs]}"

> > > )

> > > BTW, there is a flaw in this approach. What happens if you have a user
> with
> > > the username of, say, myDomain.user and another with myDomain.userX?
The
> > > user myDomain.user will automatically see the members that
> myDomain.userX
> > > can see because of the way the Instr() function is used.

> > > Hope this helps,
> > > Richard

> > > --
> > > This posting is provided 'AS IS' with no warranties, and confers no
> rights.



> > > > We have cubes built on physicians revenue data. Physician is one of
> > > > the dimensions. To implement physician level security i.e. where one
> > > > person can see only his or her data, we have created a database role
> > > > and put restrcitions on physician dimension like this:

> > > > {Filter([RevLoc].[DepProv].[Prov Name].Members,
> > > > Instr(1,
> > > > [RevLoc].[DepProv].CurrentMember.Properties("Login_Id"),UserName))}

> > > > where [RevLoc].[DepProv] is the physician dimension.
> > > > This works fine for a while but after some time starts giving error
> > > > 'Com component error'.
> > > > I checked the event log of the analysis server it says
> > > > "User proclar1, logged in from computer <computer name>, does not
have
> > > > adequate permissions to open cube RVU views in database
> > > > FinanceOutpatient_Prod_925, but tried to open it."

> > > > I don't think there is a problem in the formula as it works fine for
> > > > sometime.
> > > > Has anybody got this error before? Any help would be grealy
> > > > appreciated.

 
 
 

1. Restricting Dimension access across dimensions

Greetings all,

I have a cube which has three dimensions. I recently restructured one of the dimension's drill down path. This new path is fine for new data coming into the cube but the old data is not accurate at this new lowest level but is accurate at it's parent level.

Restricting access to the dimension via the cube role is not helping because I can only use it for 1 dimension at a time when I actually want to cross dimensions. (eg. {(sum.level3.patrick, month.may) where in June I would want that member visible.

Any ideas would be great and please let me know if this needs further information. I also looked at custom rollups but I'm not sure I'm using it right.
Thanks.
Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. =============== VC++/MFC - Profis gesucht fuer Support =======================

3. Dimension Editor artificially restricts Dimension design?

4. Ms acess reference object

5. How to restrict certain databases using NT security

6. Proc*C and C problem about "LONG"

7. Calculated measure restricted by dimension

8. Saving and retrieving image control picture property in MSSQL7

9. Restrict Dimension Members

10. Create Roles for restrict acces by dimension

11. Fully Restricted Dimensions and Drillthrough

12. Restrict access in dimension value

13. Binding to a cube with restricted dimensions for a pivot table