Solved

Data Permissions in a cube

Posted on 2007-11-19
11
397 Views
Last Modified: 2008-02-01
I have a database with customer subscription data in it.  CusA and CusB.  I have built a cube for it and currently have it set up for internal staff to use, so they can see everything.

Could someone point me in the direction of how to restrict the data in the cube so each customer can only see their own data.  i.e.
If they log into the cube using Admin, all of the subsctiptions fact table is used.  If they log in using CusA only data where owner = CusA is displayed.  If they log in using CusB only data where owner = CusB is displayed.

Any help would be much appreciated.  I'm currently trying to find out using MDSNs website, SQL Server 2005 Analysis Services Step by Step (isbn 0-7356-2199-3) and Delivering Business Intelligence with Microsoft SQL server 2005 (isbn 0-07-226090-4), but I cant seem to work out how to apply their examples to my project and i've managed to read the information differently so it conflicts, i.e. should i be setting the permissions in the DB so the cube inherits it or set up the permissions in the cube.  Any help would be much appreciated as i'm very stuck!  Thanks.


0
Comment
Question by:kinton
  • 7
  • 4
11 Comments
 
LVL 18

Accepted Solution

by:
PFrog earned 500 total points
ID: 20334485
The key here is roles.

Create two roles, and make sure all facts have a link to a customer dimension. You then just need to give permissions for each role to see the appropriate members of the customer dimension.

Do this from Management Studio, create a role then edit the Dimension Data tab on the role properties to select the appropriate customer.
0
 
LVL 2

Author Comment

by:kinton
ID: 20349301
Thanks PFrog.  I've not forgotten this post, i'm just having trouble getting my head round roles at the moment.  My knowledge of Cubes isn't great.  I jumped in halfway through a project and I should really do a step by step guide at least so appologies for the delay.   Before your post I thought that the primary sources of the numbers in a cube came from the measures which come from the fact tables - hence I thought the permissions had to be set up there rather than dimensions.  If its okay i'll try and get my head round it a bit more before I close the question.

0
 
LVL 18

Expert Comment

by:PFrog
ID: 20349319
Sure no problem. If you need more info or help figuring things out please shout.
0
 
LVL 2

Author Comment

by:kinton
ID: 20426939
I've managed to get back to this for a bit.

I've had a go at putting permissions on a dimension role and it works great. However, 1 thing concerns me.

If I have a measure such as 'total subscribers'  and drag it over without using any dimensions, surely that does its count on the whole list?  If so, how do I make it so the main fact table only shows what a user is allowed to see?

Regards,
Phil
0
 
LVL 2

Author Comment

by:kinton
ID: 20427351
I've checked the numbers and it would seem that by adding permissions to a dimension, even if the dimension is not used, all of the data has the criteria from that permission applied to it.  Which makes sense I guess.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18

Expert Comment

by:PFrog
ID: 20427370
Yes, and no  (!)

Lets say you have a dimension called 'customer', with 3 members, one for Customer A, one for customer B and one for C. All facts have a direct link to this customer dimension.
You want each customer to only see their data.

You should create a role (Role A) set permissions on the customer dimension, using the "Deselect all members" option. This means you have to manually add any member that this role should be able to see (i.e. Customer A). This option means that when you add another customer, by default no other customers will be able to see it.

As this role stands, if the user includes the customer dimension in their query then they will only see their own data. However if they don't include the dimension they they will see the whole database - not ideal!
To fix this, go to the advanced tab of the Role's Dimension Data security window, and tick the "Enable Visual Total" box. This now means that the customer dimension security will always be applied no matter what dimensions are included in the query.

Have a play with the different options, and you'll see the different effects.
0
 
LVL 2

Author Comment

by:kinton
ID: 20427814
Oh yeah so it does!  You'd have thought that would have been ticked by default, I can't really think of a scenario where that would be useful.  If the user can't see half the data, including the missing stuff in the total isn't useful in most situations i'd have thought.

Had a bit of a play with the roles.  Not sure how I managed it but at one point I had the following;
Product dimension with products & clientID in it.
Role1
Deselect all members selected at database and cube level, with 1 of the client_ID memebers ticked.
Role2
Permissions left alone so they should be able to see everything.

For some reason if I just selected product from the product dimension and total subscriptions, it only displayed the products that had a client_ID of 0.  I had to bring in the Client_ID member from the product dimension to be able to see the products with a client_ID of 1.
I scrapped the role and tried to build it again and it worked as it should have, so I have no idea what happened there!

Thanks for the info PFrog, I think I now have a basic grasp of Roles.

Regards,
Kinton.
0
 
LVL 2

Author Comment

by:kinton
ID: 20428752
In theory then, I should only ever have to set up permissions for 1 of the dimensions i.e.

If I have the following dimensions and those with an * have client data...
client*
product*
country
status
contacts*
....I should only need to set the permissions up for client and the rest will fall in place automatically?
0
 
LVL 18

Expert Comment

by:PFrog
ID: 20428828
That's right in terms of the fact data, although do you want customers to be able to see other customers' dimension members?

If a user specifies a NON EMPTY clause in an MDX statement, all members will be returned even if they have no data, so one customer could see all clients belonging to all customers, even though he would not be able to see the fact data associated with the other customers.

So in short, you should really secure every dimension that contains customer specific members
0
 
LVL 2

Author Comment

by:kinton
ID: 20429213
Ah right I see, i'll have another look and get back to you - thanks for the warning :)
Have a good weekend - i'll post back on monday for this one.
0
 
LVL 2

Author Comment

by:kinton
ID: 20440912
So it does.  I was hoping that because I included Client_ID in the relationship to the other dimensions it would restrict then from seeing them too.  Glad you pointed that out as I hadn't seen that!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now