Looking for examples of large security implementations for
cube and dimension access. Anyone out there dealing with
situations where over 100-200 distinct roles are needed?
Or very fine-grained security control is needed over a
large dimension (> 500K members)?

If so, please reply via email to "davef (AT) intellinet (DOT) com" so
that we can discuss approaches and challenges. I'm trying
to deal with a situation with multiple large dimensions
and requirements for over 500 distinct security roles...

Thanks!
Dave F.

ProClarity supplied two whitepapers on security at their latest conference.
The first possibility is to use cubes that store security information. That
means filter everything that is not explicitly allowed. I have tested that
but it means a lot of security cubes. Another approach is to use userdefined
functions, ie program these functions in VB or other COM-compliant
languages. This approach is the one that scale best but I have not tried it.

HTH
Thomas Ivarsson DW-consultant Malmö Sweden




"Dave Fackler" <davef (AT) intellinet (DOT) com> wrote

I've found several limitations to the dynamic security
approaches that have been offered in various forms over
the past 2 years, but I have not seen any information on
the user defined function approach. Could you (or someone
from Proclarity that happens to watch the newsgroups),
send me a copy of the whitepaper mentioned?

Dave F.

Sorry. I cannot do that. It is for partners only. But one of their managers,
Russ Whitney has a column i SQL Server Magazine. I suggest that you contact
him or any other representative from the company. Or attend their
conferences. I have not tried this method yet, only the security cube
approach, so I cannot give you any hints.

Regards
Thomas Ivarsson


"Dave Fackler" <davef (AT) intellinet (DOT) com> wrote

I've found several limitations to the dynamic security
approaches that have been offered in various forms over
the past 2 years, but I have not seen any information on
the user defined function approach. Could you (or someone
from Proclarity that happens to watch the newsgroups),
send me a copy of the whitepaper mentioned?

Dave F.

There is no 'official' documentation concerning using UDFs in dynamic
security available yet, since the functionality to support UDFs in roles was
only introduced in a post-SP3 hotfix. Take a look at the following thread
for more information:
http://groups.google.ie/groups?hl=en...sqlserver.olap
(note that some line breaks will be inserted in the URL after posting which
will need to be removed)

I'm not sure it necessarily scales or performs better than the security cube
approach, but it certainly involves much less complex MDX and has many other
obvious advantages. However, as with all hotfix functionality, I would be
very cautious about using it in a project without extensive testing to make
sure it meets your needs.

To answer your original question, when you are talking about implementations
with large numbers of distinct roles or very large dimensions using
dimension security (note: not cell security), the chief limiting factor will
be memory on the server. Dynamic security is definitely recommended from a
manageability point of view (you probably don't want to have to manage all
those roles through Analysis Manager) but in terms of memory usage it is
exactly the same as normal roles. After the Analysis Services service is
started, the first time a user who is a member of a role using dimension
security opens a connection, a replica dimension is created in memory
containing all of the members on the dimension that that user can see, plus
all of those members' siblings. This replica then stays in memory even when
the user has closed their connection and stays there until the service is
stopped and started, or until the dimension is reprocessed. When another
user who can see the same members on that dimension - even if they are a
member of a different role - opens a connection that user uses the existing
replica. However, when a user with a different set of permissions logs in,
another, different replica dimension is created. You probably already know
that all dimensions are held in memory anyway, so you can see that if you
have a large number of distinct roles on large dimensions and all of those
users log in, you can quickly use up your available memory.

This then raises the question of how much memory each dimension takes up,
and for answers I would suggest taking a look at this thread:
http://groups.google.ie/groups?hl=en...sqlserver.olap
Overall, if you are worried about memory usage you should try to avoid
adding member properties you don't really need, and to avoid large, flat
dimensions. You should also consider moving to 64-bit as this removes the
limitations on memory that the 32-bit platform involves.

HTH,

Chris



--
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and
confers no rights.

"Thomas Ivarsson" <TI_Nospam (AT) hotmail (DOT) com> wrote

Hi Dave,

I would like to thank Chris for his help. Analysis Services security can be
a challenge when we have to deal with large numbers of users who each have
to see different parts of your cube. With SP2, Analysis Manager became very
slow when there were more than 150-200 roles - and although its performance
has been improved in SP3, managing large numbers of roles through Analysis
Manager can still be difficult simply because of the number of items in the
treeview.

Dynamic security offers us the chance to avoid these problems by storing
our security permissions in relational tables which are then used as the
source for a cube. We can then have only one role in our main cube, which,
when the user logs on, queries for their permissions from this
'permissions' cube and dynamically sets their access rights. Dynamic
security is only possible with Analysis Services SP1 or higher, and can be
used with either dimension or cell security.

For additional information regarding Dynamic Dimension Security, please
refer to the following article below:
This Support WebCast session discusses how to implement dynamic Dimension
Security in Microsoft SQL Server 2000 Analysis Services. It reviews some
popular approaches for use during implementation. It also talks about
common issues that may occur and how to resolve them.
828343 Support WebCast: Dynamic Dimension Security in Microsoft SQL Server
2000
http://support.microsoft.com/?id=828343

However, dynamic security is only going to help with the manageability of
large numbers of roles and does not offer any advantages in terms of memory
usage over non-dynamic, standard roles, so it will be useful but it isn't
the whole answer. We need to consider the size and structure of the
dimension to be secured so that we can check to see if we are going to run
in to any issues with memory. If we are, then we need to either go to
64-bit to get the extra memory; or use a solution like Panorama's web-based
client which implements its own (more scalable) security; or we should
redesign the dimensions so they are smaller and/or consider processing the
cube security regularly to drop any replica dimensions in memory.

Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default...S;PHONENUMBERS

Thanks for using MSDN newsgroup.

Best regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.