Sunday, April 13, 2008

Dimension Security using SSAS Stored Procedure

Often time the business needs are such that the security restrictions of users change periodically due to modification of job roles, work location and/or other attributes.  A solution that can accommodate addition, update and removal of users seamlessly or dynamically in SSAS is critical to reducing latency and minimizing user security maintenance.  The idea of using SSAS stored procedure to enforce dimension security dynamically is rather straightforward: While all users belong to a single SSAS database role, individually, their data access is restricted based on the set of dimension members they have permission to as determined by the SSAS stored procedure dynamically.

A short time ago my team identified a requirement to refine our overall SSAS security scheme.  The data available in our cubes needed to be filtered to authorized users by their sales location(s).  The list of users and their authorized sales locations are maintained in a collection of M:N relational tables in one of our SQL Server databases.  Our ETL refreshes these tables regularly.  For simplicity, a database view is used to de-normalize the data in these tables into "user alias-to-sales location" pairs as shown in the mock-up here.

UserAlias SalesLocation
usera United States
usera Canada
userb United States
userc Hong Kong
userd United States
userd Germany
userd UK

To avoid the potential of SQL injection attacks, a SQL Server stored procedure is used to look up the data in the view.  The stored procedure (listed below) takes the alias of the current user as an argument and returns the corresponding sales locations.  The domain definition is stripped off for our purpose.

   

Our SSAS stored procedure (shown below) is implemented as a method from the UserSecurity C# class library - GetFilteredSet_SalesLocation().  The SSAS stored procedure employs the [usp_GetUserSalesLoc] SQL Server stored procedure to retrieve and return the unique name of the sales locations. 

The GetFilteredSet_SalesLocation() method takes three string-type arguments: user alias of the current user, name of the SQL Server where [usp_GetUserSalesLoc] is located (name of the database is hard-coded in the method - yes I know), and the dimension hierarchy level.  The dimension hierarchy level specification allows us to uniformly leverage the SSAS stored procedure across multiple SSAS databases and cubes with similar security filtering requirement but different dimensional schemas.  The method then enumerates through the data set returned by [usp_GetUserSalesLoc] and assembles the MDX set as an output string as such:

{[Dimension].[Hierarchy].[Level].&[MemberName], [Dimension].[Hierarchy].[Level].&[MemberName], [Dimension].[Hierarchy].[Level].&[MemberName], ...} 

Subsequently the SSAS stored procedure is compiled into a .NET assembly (SSASSecurity.dll) and deployed to the SSAS server.  I usually place the DLL in Drive:\Program Files\Microsoft SQL Server\MSSQL.#\OLAP\bin\ directory.  In this particular instance, I had the assembly registered within the server scope so that it can be used by any of the databases on the SSAS server.  It can certainly be registered within the database scope.

Note that the assembly must be registered with "External access" permission at the minimum in order for the GetFilteredSet_SalesLocation() method to function properly.  It also needs to run under an account that has permission to execute the [usp_GetUserSalesLoc] SQL Server stored procedure wherever it might be located. 

Finally we specified one SSAS database role with all user logins added to the membership collection.  The user members have read access to the cubes and dimensions.  For the dimension hierarchy of interest, we placed a call to the GetFilteredSet_SalesLocation() SSAS stored procedure in the MDX expression of the Allowed member set.  The STRTOSET MDX function converts the output string into a valid MDX set.  The USERNAME MDX function returns the login alias of the current user.   

The allowed member set can be defined for either database or cube dimension.  If a dimension is referenced by a number of cubes in the database, it might be desirable to define the allowed member set at database-level and propagate the filter to cube-level through inheritance.  By enabling the "Visual Total" option, we excluded the denied members from contributing to the aggregate values. 

This is it.  Users are now restricted to cube data for those sales locations they are authorized to see.  This security scheme works nicely with Excel when the workbook data connections are specified with integrated security.

There are few approaches to implement dynamic dimension security in SSAS - as you will find in number of writings both online and offline.  I found the SSAS stored procedure approach to be the most straightforward of them all and the least pervasive to implement.  Furthermore security restrictions are much more immediate because they are queried directly from the relational database each time.  However, we do need to ensure the SQL Server and the database where the [usp_GetUserSalesLoc] stored procedure resides is up and running at all time..

42 comments:

Michael Brönnimann said...

Thanks for the article. As I'm quite familiar with MDX and SQL but only know basics on .NET / C#, etc.. Your sample application will help me to bridge SSAS back to SQL, where it' can be used for security lookup and even in context with cube side related actions.

To avoid re-typing your lines of sample code, could you please send me the code as source-file to my google account?
Are all included libraries as listed in your code necessary?

with best regards
michael

Amos said...

Michael, I sent the CS file in separate email. Cheers.

Raj said...

Can I also request the same piece of information which Michael requested. I am fairly new to MDX but did worked a lot in ERP, and SQL. If possible please send me all the required files too.

-Thanks
Raj

Raj said...

Can I also request the code, and the attached files which I need. Thanks in advance for your help.

-Many Thanks
Raj

Amos said...

Raj, do you have an email account where I can send the files?

Raj said...

veryverma@yahoo.com will the best account where I can get these files.

-Many Thanks
Raj

Amos said...

Raj, I sent the files in separate email. Cheers.

carlweb said...

I too have the same request for the sample code. Is it possible someone might forward that to me as well? Many thanks!
Also, is there any security risk using the service account to run the assembly as in this example? Would there be a risk of having a SQL injection attack for instance?
Thanks again.

Stef said...

Thanks for this article.

We are currently facing the exact same needs in a big reporting platform project.

Do you use the described approach in a productive environment? If yes, did you experience any issues (in particular performance and non accurate access rights)?.

During our test we experienced some strange behavior with the deployed assembly, which had sometimes to be reloaded in order the get the latest access rights.

Thanks a lot for your answer

Arvind Ravish said...

Can I also request the code, and the attached files which I need.

My Email id is arvindravish@hotmail.com

Thanks in advance for your help.

Amos said...

Arvind, I sent the files in separate email. Cheers.

Unknown said...

May I also request the codes and the attached files? or somebody else can forward them to me? I am facing this in my current project.

My Email is
hzhao79@gmail.com

many thanks in advance for your help.

Amos said...

H, I sent the files in separate email. Cheers.

Anonymous said...

Hi Amos. Please may I trouble you for the complete sample code as well? I would really like to take a look at this option. Thanks. My e-mail is shaun_tetley@hotmail.com

Unknown said...

Can you send me the files also?

Anonymous said...

Sorry for the same request again! Would you be able to email me the code sample too? It looks very interesting for a pilot project I am working on. My email address is netsmurph@yahoo.co.uk.

Many thanks


Andrew

Amos said...

Andrew, I sent the files in separate email. Cheers.

Amos said...

Shaun, I sent the files in separate email. Cheers.

Amos said...

Ivonne, do you have an email address where I can send the files?

Dayanand said...

Please can you send me this code
on dayanandlanguti@gmail.com

Amos said...

Dayanand, I sent the files in separate email. Cheers.

Unknown said...

Hi
I have a similar task to be done , datawarehouse(dw) is all set but as per new business rules, the users are created in the oltp db,but in dw(whose source is the oltp), i have no dimension refering to the users.what is the best way to confront the problem of creating users in the dw,or is it that i have to connect to the oltp user table and bring in the datasource view of the cube. Could u help me out on the problem
Thanks
Mary

Anonymous said...

It still handy, could you please e-mail me the code? The best address is johndimo@yahoo.com

-John

Amos said...

John, I sent the files in separate email. Cheers.

Unknown said...

Hi,
Great post. Please could you email me the code to:
richard.tennant@ridgian.co.uk

many thanks

Amos said...

Richard, I sent the files in separate email. Cheers.

Anonymous said...

This is what I need so far, Sorry, Could you email me the code sample too?
My email address is rujimethut@yahoo.co.uk.

Many Thanks
Watta

Amos said...

Watta, I sent the files in separate email. Cheers.

Amit Gupta said...

your post is really very helpful... can you please send me code used in post on amit.jhansi80@gmail.com

Amos said...

Amit, I sent you the files in a separate email. Cheers.

Unknown said...

Hi Amos, can I request sample code file and please send me file to my email address superdec@gmail.com Thanks...

Amos said...

Raju, I sent you the files in a separate email. Cheers.

Anonymous said...

Amos,

I would like the sample code too...Can you pls send to shivani.gold@gmail.com

Thanks very much
Shivani

Nick Losier said...

This looks great! Any chance you can send me the code as well? Regards,

Brett Flippin said...

I'd also love to see your sample code. Working on a few different ways to do this and it definitely looks the most simple.

How well does it work for multi-level hierarchy? Say a something like a Sales Division->Sales Region->Sales State hierarchy?

ajvgl said...

Can i get the sample code please?

Email: ajvgl@yahoo.com

Thanks

Siva Gudavalli said...

i would like to implement a similar one... here my dimension and attribute for which i need to apply security is also varying..

user1 needs to see only [policy].[US]

user2 needs to see only [submission].[US]

besides, can you also send me the code at my email

thanks
shiv

Anonymous said...

Good Article.
USERNAME function is not listed in my MDXBuilder. Could you please tell me why it is not listed? I am using SQL 2008 R2.

STRTOSET(SSASSecurity.SSASSecurity.UserSecurity.GetFilteredSet_SalesLocation(USERNAME,"YourSQLServer","[YourDimension].[YourHierarchy]"))

RAj B said...

could you plz send me the Code/sp to my emaild id.

rajeshwar_baddam@yahoo.co.in

Anonymous said...

Can I get the sample code please?
I am also working on the similar integrated security project.
email.viresh1485@yahoo.com

Amos said...

Viresh1485, I sent you the files in a separate email. Cheers.

Anonymous said...

When adding the allowed set to the dimension, do we need to apply it to all attributes or just one (maybe the key attribute)?

Thanks in advance...