December 7, 2007 at 2:49 am
Hi there,
I am evaluating BI for our company and have come across a problem that I am hoping you can all help with.
We want to deploy reporting services for one of our big clients and will either deploy a model or a cube or both so they can build their own reports.
All our data is currently on one server however we want different levels of access to the data. Head office to see all the data, regional managers to see a subset and then individual sites to see their own data.
We have tables that describe the acess to certain sites, regions, etc so have implemented a view based on a windows login that seems to do the trick for models but need to do something similar for a cube.
When i build my cube based on the views it still returns all the data. Is there a way of allowing certain windows logins to see specific data ?
December 7, 2007 at 7:11 am
If you include a dimension that contains members describing the data individual users should be able to access, you can specify by user or group in the cube security that they can only see particular members.
So, if you have a dimension for state and you configured different user groups by state, you could set that user group to only have access to their state. To make it a bit more complicated, they can also be given access to see their members or the "all" group so they could compare their results to the entire aggregate, but they would not be able to see other individual results.
December 7, 2007 at 7:26 am
Granting access to only portions of the data is fairly easy if you are using SQL 2k, and this has supposedly gotten easier in SQL 2k5. The security is not granted in the database, however, but through the Analysis Services interface. You need to look there.
December 10, 2007 at 2:10 am
Thanks for this but its not quite what i need, although i have data that describes the group access to the individual sites the bit that is missing is the windows login. I used a view with a UDF to check the login against a table. How would i achieve this in a cube? The view i have at the moment returns everything regardless of who i login as, as i guess the cube is processed with all the data?
The view itself works fine, just not with a cube
December 10, 2007 at 5:25 am
How is your cube security currently configured in Analysis Services? Do you have just a single role with everyone having access to everything at the moment?
December 10, 2007 at 5:30 am
Yes there is just the one role
December 10, 2007 at 6:28 am
Configure your users grouped into roles that define the data they can access. I have attached a screen print from Management Studio that shows where you should be when configuring what data they should be able to access.
December 10, 2007 at 7:18 am
This is excellent and something i had not considered. I have now looked into it and surely this requires that you manually select the data for each role ?
I wanted something that is a bit more automatic and based on the data already in our tables.
Our security model is...
Table 1
NT Login
System Login
Table 2
System Login
Site
Table 1 joins to Table 2 on System Login and table 2 is multi row with as many sites in as the user requires
Our dimension is then built on a view on sites linked to table 1 and 2 so that depending on who you login as depends on which sites are returned
December 10, 2007 at 9:30 am
I believe on the advanced tab of that same dialog, you could write some custom MDX that does what you are suggesting.
However, I have never tired to ue it in this way. It seems a bit off of what the security model of AS is supposed to do. You may want to see if you can add into the process of configuring a user adding them to an appropriate active directory group. If you can get your administrators to allow this, you will be able to set up an Active Driectory group for each data group you want and solve the problem in the traditional manner.
It does not seem like a lot of overhead to add to the proces of adding a user unless your users come from some automated process like a web page.
I suppose another potential process would be to create a CLR procedure that adds the user to an appropriate active directory group and run the procedure when a user is added to your database - it would take the people out and still just manage this security through their NT login.
December 11, 2007 at 2:22 am
Thanks again for your help... It seems that it cant be done the way i would have liked, nice to know it wasn't me!
I have had do to a real nasty to achieve what i was looking for if anyone is interested. I have used an openquery statement in tsql to a linked server and pull the results of an MDX statement into a tempoary table which i then joined to my view and then built my report on that. It has met my immediate requirement, but not sure how useful this will be long term.
Anyone got any better ideas?
December 11, 2007 at 8:00 am
Darren,
The correct (and easiest) way to go about this is to create the necessary roles within the MSAS cube. You probably "could" implement cell level security (join in you data, test it at the cell level), but I wouldn't recommend it, unless you absolutely needed it.
The roles within MSAS are intuative and generally work the way you'd want them to. Of course, you need to ensure that the correct dimensional information is represented in the cube. Some notes:
- Roles are cumulative. (Users / groups granted access to multiple roles combine their access.)
- The "Enable visual totals" checkbox is quite useful, when the user must not have access to the grand totals. Try out this option when testign your security. (Be advised, this setting crosses roles.)
- You can generate role definitions easily via XMLA queries. I'd recommend pulling out a template, and doing a transform via SSIS. We do this as part of our automated weekly data delivery.
Utilizing the roles capability is the most powerful approach, and will give you the best performance characteritics. Basically, it restricts your users to specific "slices" of the cube. The definitions can be as complex or simple as you'd like.
December 12, 2007 at 9:46 am
Hi Darren,
I've been reading through the MCTS 70-445 BI Implementation & Maintenance book. Reading the posts I think I understand what you are trying to acheive.
A similiar requiement is mentioned in the book ( a real word solution I will add!) and the solution was to create a 'factless' table with two columns, users domain identity and site. Along with a measures securitry group filter and an allowed set MDX statement for the relevant dimension(s), you'll be able to use the SSAS roles and windows authentication to determine what the user gets to see in the cube.
I would advise that you buy the book and read Chapter 16, Page 458 in detail -. Its a very good book, and I think it's the answer your looking for!! I'm not going to copy in here because of copyrights etc etc, but for £40 / $60 I think it'll save you a lot of pain trying to find a workable solution.
Let me know if it is what you need and if you have any luck.
:w00t:
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 13, 2007 at 2:01 am
This sounds like it might be what i need.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply