Hi
I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.
Questions
1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)
2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.
3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.
Thanks
Milind
A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.
In the cube dimension you want to restrict, you defined an allowed set something like this:
NONEMPTYCROSSJOIN(
[My Dimension].[My Attribute].Members,
STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),
[Measure].[MySecurityMeasureGroupCount],
1)
It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.
Bryan
|||Hi Bryan,
In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this
User Id, Dimension, Members
User123 Location [Location].[Some level member].(member].value
This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.
Can you please let me know how would you acheive this with performance taking into consideration.
Cheeeers
Milind
|||Hello!I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.
Radim
|||
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
|||
Bryan C. Smith wrote:
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
Bryan,
1. Can you please post the example of acheiving this...i mean some link to some help or some code.
2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)
I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.
Thanks in advance.
Waiting for your response.
Milind
|||Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:
Code Snippet
create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)
create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)
Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.
Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.
Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.
Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.
Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.
Now we're ready to set up security.
Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.
Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:
Code Snippet
{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}
This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)
Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.
Hope that helps get you started.
Bryan
|||Hi Bryan,
Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.
But solution you have provided still doesn't statisfy the requirement I have.
As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.
1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.
2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.
3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)
I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.
Thanks,
Milind
|||Sorry for the delay in the response. Long weekend here.
Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).
Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.
Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)
Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.
Good luck,
Bryan
Bryan
Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.
Thanks
|||What are you using to define your allowed set?
B.
No comments:
Post a Comment