Data level security issues

Question

Hello,

I have a working SSAS cube. I implemented data level security on his cube by using 2 tables that join to the dimension tables. One is called users (list of all users) and assignments (shows which data the user has access to). The 3 levels of security are Country, Agency and Media Type. So, for example, if we have a user named John Doe we can assign him all of the Agencies and Media Types in the USA. So John Doe should only be able to see the country USA.

Let me try to explain my issue...

We are using excel and Performance point to access the cube. When we open the reports, using our John Doe example, we can see all of the countries, BUT the data security is working because we can only see data for the USA. Is there a property or something that I can change that will prevent this from happening? I need John Doe to only see countries that he has access to, and not see all countries but only see data in the countries that he has access to.

Thank you all in advance and please let me know if I was clear enough.

Dave

---

Dave SQL Developer

Answer 1

There's a couple of ways to do so and probably none of them are all that good. You could create roles and use an Allow Set on the cube dimension to specify which countries should be displayed. You'd have to create a separate role for each possible case.

I believe there's other ways to do so by creating a custom assembly and overriding the SessionOpen or CubeOpen events. I'm finding a hard time finding an example of that approach though.

Martin

---

http://martinmason.wordpress.com

Answer 2

Hello Martin,

Thank you for your response. I truly find it hard to believe that I have to go through all of this just to ensure that a user can only see the countries that he has access to when the data level security that I created works. Perhaps you are over thinking this or I wasn't clear enough.

Lets break this down.... Lets say that my cube only has country level security. John Doe has access to USA data only. When John Doe logs into excel or Performance point he can see all of the countries but only the data from USA. I need John Doe to only see what countries he has access to.

I was talking to one of the SSAS experts here at my office and he said that there is a property available that can prevent this from happening, but he doesn't remember where it is. So, I was hoping someone on this forum would know.

Thanks again for your response Martin.

Dave

---

Dave SQL Developer

Answer 3

Keeping it really simple, does the following satisfy your requirements?

• Create a new role in the database, USA User.
• Grant the role access to your cube.
• On the Dimension Data tab of the role editor, select the Country cube dimension (not the database dimension as both will be shown, though you may also want to restrict access on the database dimension)
• Select the "Deselect All Members" option on the right and check the USA member so only the USA member will be displayed.

Thought you had a requirement for cross-dimensional access requirements and that requirement is much harder to solve.

HTH, Martin

---

http://martinmason.wordpress.com

Answer 4

Hi Martin,

Unfortunately, I have dynamic data level security and the privileges are assigned through the tables and not simply by going through the roles and manually changing them. We have over 200 users at this time, so this approach would not work. I wish it would be this easy :)

Thank you again

---

Dave SQL Developer

Answer 5

Hi Dave,

You're probably going to be best off addressing your requirements by creating an assembly that handles the SessionOpened event of the Microsoft.AnalysisServices.AdoMdServer.Server object http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.server.sessionopened or the CubeOpened event of the Microsoft.AnalysisServices.AdoMdServer.AdoMdConnection object. http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.adomdconnection.cubeopened.aspx

Unfortunately, it's just not going to be that easy. Alternatively, you could define a single role that calls an SSAS stored procedure to return back a set of the allowed members. Either way, I don't any way to address without building a custom .NET assembly, at least with Excel.

With PPS, you could possibly use a MDX query to create a filter that only returns NonEmpty members based on a selected measure assuming you're using Per User Security. SSRS would address the situation the same way. Excel, though, there's no way I can think of where you could address without a custom assembly.

Martin

---

http://martinmason.wordpress.com

Answer 6

Thanks for your help. I will look into this.

Dave

---

Dave SQL Developer

Answer 7

Does anyone else have any ideas? Is what Martin proposing the only option for this? I am surprised that only 1 person replied to this post. Is that because this is the answer?

Thanks

---

Dave SQL Developer

Answer 8

Rather than replying to this thread, you may want to start a new thread and reference this one. You're much more likely to get someone else to respond when no one has yet replied.

---

http://martinmason.wordpress.com

Answer 9

Hi Martin,

I used your assembly approach and it worked!

Thanks

---

Dave SQL Developer

Answer 10

Hi Dave,

I have the exact requirement as yours. but having hard time to get the solution. Can you share the approach and steps if possible as to how you accomplished this?