Solved

Data Permissions in a cube

Posted on 2007-11-19
11
400 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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