rs, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows userid. It would be very tedious to set up a security role for every individual. Not to mention the maintenance in adding and deleting roles. Although this could be somewhat automated with AMO scripting.A technique I like to employ is to set up just one SSAS role, which essentially contains everyone that might have some access to the cube (integrated security only). So, in the Dimension Data security tab, I restrict the "Allowed member set" to just those members where the member name is equal to the username(). Username() is an mdx function that returns the Windows userid.
The Allowed member set might look something like
{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}
The default member could be
StrToMember("Branch.BranchManager.&[" + UserName() + "]")
You can add other roles, for example, one with read access to the entire cube. So the users that can only see their Branch, would be in the first role (they would only be able to see their own branch) and head office users in the second role would be able to see the entire cube.
This is a very elegant solution. As you can see, it requires no maintenance, as new/old users will automatically be able to see their own data. If the branch manager changes, the old user will have no read access and the new user will have read access to that branch.
There are many ways to extend this security. I have combined it with application tables that list users' access. This can be a many-to-one or many-to many relationship. No problem, just create a dimension (possibly many-to-many) , and grant read access to the dimension in the same way. Also, it might be that you only want part of the username, or that you want/don't want the domain. Again, that is no problem, you can use VBA functions in the MDX expression to get the substring that you want. You could also use this technique to manage a black list (Denied member set) instead of a white list (Allowed member set).
A classic use of this technique would be to support sales persons to see information relating to their customers. This technique might be combined with other restrictions. For example the dynamic security role might let browsers see a subset of their data, such as quantities, but not dollar values. And only for their customers.
Note, there is an overhead in Dimension Data security, but in my experience it is almost undetectable. Of more concern, if you needed to use it, would be the many-to-many dimension. Be wary of performance if your m2m dimension and/or m2m fact table is large.
Also note, you probably want to check the "Enable Visual Totals" check box, so that users can only see the total for members they are allowed to see.
The use of this dynamic security technique can be used in conjunction with conventional roles in the same cube.
Nice post Richard. I've implemented a variation of this with PerformancePoint using the CustomData() function instead of UserName().
ReplyDeleteCheers,
Rod Colledge.
Yes, that's interesting, CustomData can be useful. However, since it is part of the connection string, how would you prevent someone from connecting directly to AS, specifying their own CustomData string?
ReplyDeleteYep, that's true, and well understood. It's a temporary workaround until Keberos issues are revolved in the client's environment :-)
ReplyDeleteI understand. It is about time that the Windows team made it easier to enable Kerberos. It seems that even with Windows 2010, you still need a really good Windows engineer to enable Kerberos.
ReplyDeleteI have a problem related to this. The allowed set I'm using is
ReplyDeletecase when [Role Req Level].item(0) is [Resource].[Req Level].&[Manager]
then [Measures].Allmembers
else {[Measures].[Total Hours]}
end
[Role Req Level] is a named set which returns the Req Level based on username. This works but only with the first user that opens the cube. E.g. if the first user that opens the cube is a manager he will get all the measures. After that all the users will get all measures even though they are not manager level. If the first user that opens the cube is not a manager he will get only total hours measure. After that all the users will get only total hours measure even though they are manager level.
Do you have any suggestions to this problem? I have tried cell data also but it is very slow in Excel. In BIDS it works ok.
Tomi, sets are only created once. You should take the set out of the expression and replace it with the set expression. Another option, might be to use the new Dynamic keyword in the set declaration.
ReplyDeleteThank you Richard. I moved the named set from calculations to the dimension data security. It works well.
ReplyDeleteRichard,
ReplyDeleteHave you ran across an issue where the dimensional data attribute permission you set stops allowing everything (including what you've defined to be allowed)? Neither that I have defined are numeric fields.
Curiously,
Brian Weber
No, I can't say that I have had that issue. However, I tend to run the MDX expressions in a query window to ensure that I am getting the correct result/set etc.
ReplyDeleteWhat suggestions would you have for this issue? Both of these have made the SSAS 2008 system very cumbersome to the success of the project.
ReplyDeleteHere are this and some other articles on Analysis Services Custom Security:
ReplyDeletehttp://ssas-wiki.com/w/Articles#Custom_Security
I found the answer to my issue. It is a bug within SQL Server Enterprise 2008 SP1. I updated it to SP2 and it is now smooth sailing.
ReplyDeleteThank you,
Brian Weber
When adding the allowed set to the dimension, do we need to add it to all attributes or just one (maybe the key attribute)?
ReplyDeleteThanks in advance.
GAGuy, the simple answer is that you only need to add permissions onto one attribute. If you grant read permissions to all "Gold" status customers, then you won't need to add read permissions to "Sydney" city customers to allow reading of "Gold" status customers in city "Sydney". Of course, if you only wanted to grant permissions on "Gold" status customers in "Sydney" city, then you would add permissions on two attributes.
ReplyDeleteHope I haven't missunderstood your question, as it seems like a simple answer.
Thanks Richard. Our scenario is for a school system and we need to secure the Student dimension so that teachers can only see their students. If we only add an allowed set to one attribute (say Student Name) will that secure the dimension if there's ever a report/MDX query that doesn't use Student Name but does pull other fields from the Student Dimension (i.e. SSN, Address, etc.)? Or do we need to add the same allowed set to all the attributes on that dimension?
ReplyDeleteThanks in advance...
That's right. You restrict by student dimension, it doesn't matter what dimensions are in the user query, they will only see data from allowed students.
ReplyDeletePlease set it up and try it out. There are a couple of things to know, for example "visual totals" can be enabled or disabled. You are likely to want to disable "visual totals" so that cube browsers only see totals for the students they are permitted to see.
Hi Richard, I'm in a similar situation that GAGuy1, I've several customers and I want to give them access only to their content, using this approach I can limit the reports they see, but the thing is that if I only secure CustomerId, they still be able to see the other customers names when they drill down the customer dimension, that contains all the customers (all the other members). So do I need to secure every attribute on the dimension so they can't drill down and view the members on the tree?
ReplyDeleteHi Ricardo,
ReplyDeleteYou will prevent users from seeing customer names if the "customer name" attribute has a relationship through CustomerId attribute. You can do this on the Attribute Relationship page in the dimension editor.
I've been told that won't work (i.e. only securing CustomerId and not All attributes) because of the following reasons:
ReplyDelete1. The attributes can still be accessed via MDX if not secured directly.
2. It goes under the assumption that there are no additional attribute relationships and member properties defined. This isn’t a best practice. It’s the least effective method of processing attributes.
3. It goes under the assumption that there is no user-defined hierarchy in the dimension, which is not a best practice in large dimensions.
4. It goes under the assumption that each attribute contains roughly one value for each distinct value of the key attribute.
Does that sound right?
FYI, here's an article by Chris Webb that says all attributes need to be secured (2nd paragraph):
ReplyDeletehttp://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/
Any thoughts?
GA Guy, Chris explains well the issue of revoking access to an entire dimension. I couldn't agree more with Chris on this issue.
ReplyDeleteWhat I was referring to above is securing dimension members and their dependent attributes. Let me give you a simple example. You have a Customer dimension where CustomerId is the dimension key. If you grant access to a role to see a particular CustomerId, then that role will only see attributes that are found on that CustomerId. If TelephoneNo was a property, the role's users will only see TelephoneNo for the CustomerId they have been granted access to. You will not need to secure TelephoneNo as well as CustomerId. This is because TelephoneNo is an attribute of CustomerId. If you granted access to the dimension through CustomerName, and TelephoneNo was an attribute of CustomerId, role users will be able to see the TelephoneNos for all customers (although they would only see measures against TelephoneNos for the CustomerName they were granted access to). The user defined hierarchies have no impact on security. Attribute hierarchies do.
Hope that makes it clearer for you.
Hello!
ReplyDeleteI'm working on developing a SSAS cube for absenteeism. The company consists of many departments. Departmental managers should have access to see detailed information about their department but will not see the details of the other departments. I need tips to create a model in SSAS that has good performance and is easy to maintain. Do you have any tips or references to articles that describe something similar in a good way?
Nice if you could send reply message g-forsmo@hotmail.com
Regards Geir
Hello Richard...
ReplyDeleteI have been asked to implement dynamic security on my cube.
I have seen your post in ur BI bolg .It is similar to my requirement.
(http://richardlees.blogspot.in/2010/10/ssas-dynamic-security.html)
I am facing some issue in my cube.I am posted it in the following link
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/ae951897-527f-41bd-a4e5-785e002bff9d/
Please follow this link and please help me out to solve this problem..
Hi Nirosha,
ReplyDeleteI have left a comment on your msdn thread. You don't appear to be using Username() in your expression. This won't be causing the issue, but I don't see how it can discriminate users without it. Use Profiler to see the mdx request with error.
I am intrigued that your mdx expression doesn't include Username(), which is can be used to get the current user. It is the username() that is typically used as a filter on a 'user' dimension.
Having said that, I would use SQL Profiler to trace the MDX causing the issue. I think you will find that the security mdx expression will fire each time a user connects.
Very nice thread one question though. This solution works if we're using windows credentials , however if we have custom security in the company
ReplyDeleteCan this still be done ?
Also, some cubes might be deployed on a server that is outside our network domain (in case there is a way to link custom credentials to windows group .. which if there is a way id like to hear how)
i know it sounds convoluted but would like any help, even if the answer is no possible
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Abed,
ReplyDeleteThe only way to authenticate to Analysis Services is using a Windows logon account. There is no concept like "SQL Server authentication" like there is for SQL Server relational engine.
You need to follow Windows domain rules regarding logging on from another domain. Ie, the domains should have a trust relationship.
hmm i was afraid as such ... was hoping there was something similar to dll authentication override present for SSRS .. i guess maybe i can do something similar to creating a many to many dimension linking windows user accounts to some local user dimension and from that create link to dimensions and properties as to which local users can see what
ReplyDeletebut then i guess all communication with cubes has to be done with a middle layer application that can impersonate the proper windows account based on the local user role ... or something like that
anyway thnx for the quick response very helpful
We are currently trying to set up a connection through a reporting engine like LogiXML to the SSAS using ADFS. I will give more information as it goes on.
ReplyDeleteAs for authentication, it is strictly from your domain. You actually want it that way, due to the ability to get granular with the data for security. We have a few fields set up with sales person's name and sales manager's name so that they can dynamically be assigned information, not just by me. I hope this helps.
Hmm ya that might be an idea
ReplyDeleteany full up information now how you are approaching this process and results you are getting would be great
Thank for sharing the insight
Hi Richard,
ReplyDeleteI implemented security in the following manner (PerformancePoint front ent):
Using the CustomData() mdx function and the OLAPSecurity DLL. The PerformancePoint (PPS) data source connects to Analysis Services using the SharePoint Unattended Service Account, but passes the text value of the User Id of the currently Authenticated User (“domain\jsmith”) in the CustomData field within the request. This value is extracted by Analysis Services and leveraged to apply security within the Analysis Services cube.
The user id that is passed to Analysis Services is utilized within the dimension data tab of an Analysis Services Role. The CustomData() MDX Function passes the User Id to the OLAPSecurity DLL, along with a Level name. The DLL looks this up in a table and determines which networks the user has access to. This is secured by a SQL Role which limits visibility.
But we have experienced dramatic performance issues in that when a user logs in, SSAS must cache all of the dimensional security for the user. This seems to be limited by the single threaded nature of the Formula Engine. The result is that when many users (50+) try to access the PerformancePoint dashboards at roughly the same time, SSAS queues the users while it builds the security cache - so the last guy in may have a significant wait.
Have you experienced this or know a workaround (does you solution of UserName() avoid this)?
Thanks
JM
Hi JM,
ReplyDeleteI haven't noticed that the security setup was single threaded. That really does surprise me. I do know that you incur the cost of setting up security at login, and I know this was improved a lot as in an earlier version (Pre 2005, but can't remember which one) each login used to have the secured dimension in cache. This doesn't happen any more.
So I'm not sure what the answer is. I would investigate a few things. For example, you say that each connection caches the dimension structure, so you might trim down the structure using Perspectives, so the connection is much faster. Also, I wasn't sure if the users come in on the same Windows user with their userid in CustomData. If so, I wonder if you can improve performance by using Windows pass through authentication and avoid CustomData.
We do have a large structure, and have looked to modify to reduce the number of calculated measures, etc.
ReplyDeleteYou say, 'This doesn't happen any more' referring to caching the secured dimension - do you know what version that occurred in? We are running SQL 2008 R2 - do you know of any enhancements in this area in SQL 2012?
We have multiple cubes in our solution and dashboards that connect to each. Sales, Labor, and Inventory data - all tied to a store id. Store is the secured dimension.
Do you think using the Username() function instead would avoid the security caching that we're experiencing?
thanks!
Don't worry about my reference to older versions holding dimension structures for each user. This was definitely pre SQL 2008.
ReplyDeleteI can't tell you that username() would be more efficient than customdata(), but since you are having trouble with customdata() I would definitely try moving to username(). Username is a more secure anyway, as users cannot fake their username, but they can put whatever they like in customdata() when they make a connection.
Hi Richard Lees,
ReplyDeleteThanks you nfor the wonderful clear post.
We implemented the custom data with unaatnded service using connection string. Gave sales agents permissions on the cube role which limits each agent to see his own data only.
I would like to understand how this is unsecure- when I try to connect as a sales agent to the cube from excel I do not see any data at all. (only dimensions etc but no data). Not even my own deals data.
If my cube gives sales agents permissions only to one role via custom data- where am I unsafe?
Please Advise
Hi namnami,
ReplyDeleteIt's only insecure to the point that someone could connect from Excel etc using a connection string with a CustomData string that allows them to see data that they shouldn't. No one can "inject" values into username().
Thanks, but I don't fully understand since when trying to connect from Excel to the cube as a user which only has permissions of customData to his records but I don't see ant data at all! (even not my own which I do see using the same connection with role in PPS). Perhaps I'm missing where to indicate the role in Excel?
ReplyDeleteWould greatly appreciate your help.
This solution is cool, but there are times when a dimension containing all userid's is not available.
ReplyDeleteFighting a couple of these manual cases was our path to creating easyroles.com, a tool making role management fast and easy and even giving the possibility to outsource security maintenance back to key users within business.
Richard, what would it take to have you review our tool on your blog?
I've implemented the same in my cube. but this is only at one level.
ReplyDeleteI want it to the drill-down level.
e.g.: zone head --> Sales Manager --> Field Guy
currently all sales manager can see "their" Field guy. problem is how to map zonehead to "their" sales manager.
Hi Manish,
ReplyDeleteI think you can do that. At least I have done something similar by including a measure group with the zone hierarchy, then in your cell level security, you can navigate up and down the hierarchy.
Hi Richard
ReplyDeleteThis is George Zhao from Department of Employment.
I had a problem in implementing Dimension Data Security in SQL 2008 R2 under Windows 2008 R2 Intel Architecture Server.
We have tried two different ways to implement Cube Dimension data security in SSAS. Neither of them works as expected according to Microsoft SSAS documentation and practise from internet.
(1). Proactive Cache, build a user dimension into the cube, using UserName function (or custdata() For Performance Point App) to return user Id and implement MDX queries in Cube role to expose cube by only that org code to that User.
(2). Develop an assembly (stored procedure) for Cube to call a stored procedure or a function by passing user id and sql instance name to return Org code. Then implement MDX queries in Cube role to expose cube by only that org code to that User.
In both (1) and (2), we had the same problem that
(Assume User A from org ABC and User B from org XYZ)
If User A is the first user who access the Cube right after Org Dimension is processed/updated, User A will be able to access Org ABC data of the Cube (Dimension data works for User A). But User B will only see User A’s data which is ABC org’s data even User A is from org XYZ. (In fact, all the other User C, D, E… will also only see User A’s data.)
Until Org Dimension is re processed/updated again, the first user X who accesses the Cube right after the dimension is updated, all other users will only see User X’ data.
All Users will see the User X’s data because User X is the first user who accesses the Cube after the Org dimension is updated.
In summary, Dimension Data security only works for user who is first access the cube after dimension is updated. It does not work for rest of users who have different org codes.
My contact is George.Zhao@Employment.gov.au
If you know how to fix it, can you please drop me a line in email, I will ask my manager to contact you.
Maybe someone help me with my problem, I have dynamic security based on dimension ProductId, everything works fine but when User open a cube and expand Product dimension he see all member from dimension,why don't he see only product with is join with fact table ?
ReplyDeleteYou users will be able to see all the Product dimension members that they are allowed to see, irrespective of whether there are facts. If you want to hide (restrict) dimension members from users, you will need to employ dimension data security.
ReplyDeleteRemember, security can be on the facts (cell data security) or on dimension members (dimension data security).
This comment has been removed by the author.
ReplyDeleteHello Richard,
ReplyDeleteFirst of all, thank you so much for your support.
I had follow this step,
--- The Allowed member set might look something like
{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}
The default member could be
StrToMember("Branch.BranchManager.&[" + UserName() + "]") ------
but i am stuck at one point that "i can not see the MEASURES of the particular zone in my filter condition "
StrToMember("Branch.BranchManager.&[" + UserName() + "]") looks good to me. If you're having trouble with it, create a measure with just the string. Then select the measure and see what the string is.
ReplyDeleteRichard,
ReplyDeleteHoping you can help me as I am trying to learn the dynamic portion of this. I ahvve a cube that has a few dimension but the only one requiring security is the Account Manager Dimension. in this Dimension there is a ERP ID (Primary Key of table), Common name and the domain user id in the format of domain\first.last.
I have create a role called Dynamic Security and for the Account Manager Dimension I am doing the following MDX statement - STRTOMEMBER( "[Account Managers].[Domainid].&["+USERNAME() +"]" )
The Enable Visual Totals is checked
When I browse the data in the cube browser as one of the users, they are able to see all of the account managers and all of the data, not just theirs.
Any suggestions would be great
Hi Walter,
ReplyDeleteFirstly, the Account Managers dimension needs to be connected to all the relevant measure groups. I assume it is?
I would start debugging this by writing a simple mdx query on your relevant measures with a WHERE clause using your StrToMember. Your mdx query should only return data for that particular account manager. Does it? Another test I would do is use the WITH MEMBER Measures.x as "[Account Managers].[Domainid].&["+USERNAME() +"]". Show this measure on columns and check that it is exactly the same as the member code that is generated for you by dragging over the user into the query.
Hope that helps. I can't tell exactly what you have done wrong, although my first guess would be not having account managers connected as I mentioned above.
Hi,
ReplyDeleteThis post is very informative and very useful for .ssas beginners
Thank you
Hi Richard,
ReplyDeleteI've carefully read through the entire thread but I'm not certain I see an example of exactly what I'm hoping to achieve and I'm wondering if it's feasible.
I have a dimension containing a list of Insurers (Payor dimension) to which I want to assign access dynamically by User ID exactly as you've described and using one Role. I have a specific User ID value (Managed Care Representative User ID) setup for each Payor.
However, my small wrinkle is that if a user is NOT in the set of Managed Care Representative User IDs, then I want them to see ALL rows.
Would something like this work in the Allowed Member set?:
iif ( isempty({StrToMember("Payor.[Managed Care Representative User ID].&[" + Mid(USERNAME(), Instr(USERNAME(), "\") + 1) + "]")})=True,
[Payor].[Managed Care Representative].[All Payors],
{StrToMember("Payor.[Managed Care Representative User ID].&[" + Mid(USERNAME(), Instr(USERNAME(), "\") + 1) + "]")})
kind regards,
David
Quick update on the Managed Care Representative User ID requirement.
ReplyDeleteI found that the dynamic security worked perfectly. I am able to allow full access to those users not defined in the Managed Care Representative User ID column and restrict access to the appropriate dimension rows for those that do have their USER ID in this column. All using 1 SSAS Role and 1 AD Group.
Great site.
David
Terrific. I'm pleased you found it useful.
ReplyDeleteHello Richard,
ReplyDeleteI am a newbie to SSAS but I am looking to implement dynamic Row level security in my multidimensional SSAS cube model. But the problem is I don't have a domain. So, can I implement it for local users as well or do I need a domain necessarily for this as I could read in some of the posts above that they used 1 SSAS rola and 1 AD group.
Thanks a lot,
Pragya Mishra