Sunday, August 19, 2007

Implementing User-Specific Security in SSAS

By Bryan C. Smith


 

You often have a requirement to restrict access to members of a dimension on a user-specific basis. You can accomplish this through the use of what is referred to as "dynamic security". The purpose of this blog entry is to demonstrate a standard technique for implementing this.

In this demonstration, you will limit access to the Reseller dimension of the Adventure Works DW multidimensional database. You will implement structures in both this database and the associated AdventureWorksDW relational database. It is assumed these databases are hosted locally, and it is recommended you backup both databases prior to making any of the required changes.

In addition, you will leverage two local user accounts, TestUser1 and TestUser2, created for the purposes of this demonstration. The name of your local system is assumed to be MyLaptop so that the full names of these accounts are MyLaptop\TestUser1 and MyLaptop\TestUser2. When these exercises are completed, these two accounts should be dropped from your system.


 

The User Dimension

Your first objective is to create the User dimension. This dimension simply houses a list of end-user accounts. You start by implementing the DimUser table in the relational database:

create
table DimUser (

    UserKey int identity(1,1) not
null,

    UserName varchar(256)
not
null

    )

alter
table DimUser add

    constraint PK_DimUser primary
key
(userkey),

    constraint AK_DimUser unique
(username)


 

We then add entries for our two test accounts:

insert
into DimUser (UserName) values
('MyLaptop\TestUser1')

insert
into DimUser (UserName) values
('MyLaptop\TestUser2')


 

TestUser1 and TestUser2 should have received UserKey values of 1 and 2, respectively.

In the multidimensional database, you now add the DimUser table to the DSV and create a new dimension, User, from it. The User dimension has a single attribute hierarchy, User, whose key and name are the UserKey and UserName fields, respectively.


 

The ResellerUser Fact Table

With the User dimension in place, you now need to construct a list of which resellers your individual users are allowed to see. In the relational database, you implement this through the fact-less fact table, FactResellerUser:

create
table FactResellerUser (

    UserKey int
not
null,

    ResellerKey int
not
null

    )

alter
table FactResellerUser add

    constraint PK_FactRsellerUser

primary
key
(resellerkey,userkey),

    constraint FK_FactRsellerUser_UserKey

        foreign
key
(UserKey)

        references DimUser (UserKey),

    constraint FK_FactRsellerUser_ResellerKey

        foreign
key
(ResellerKey)

        references DimReseller (ResellerKey)


 

For the purposes of this demonstration, let's say TestUser1 (UserKey=1) may see reseller's with ResellerKey values of 1 to 5:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,1)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,2)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,5)

Let's then say TestUser2 (UserKey=2) may see reseller's with ResellerKey values of 3 to 7:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,5)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,6)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,7)

In the multidimensional database, you now add FactResellerUser to the DSV. The relationships between this table and the DimReseller and DimUser tables should be reflected in the DSV as well.

In the Adventure Works cube, you then create a new measure group, Reseller User, based on the FactResellerUser table. It will contain a single measure using the count aggregation function. The BIDS Cube Designer should automatically add the User dimension to the cube and associate the Reseller User measure group with it. The Cube Designer should also automatically associate our measure group with the Reseller dimension. You will need to confirm this and manually update the cube if necessary.


 

Cleaning Up the Cube

The Reseller User measure group and the User dimension will provide the basis for your user-specific security, but these do not represent items with which you intend to have your users directly interact. Your objective now is to hide these items.

To hide the measure group, set the Visible property on its single measure to False. With no visible measures, the measure group will not be displayed to end-users. To hide the User dimension in the cube, set the Visible property on the cube dimension to False.

It is important to keep in mind you have only hidden these items. End-user's aware of their presence can still query them.


 

Setting-Up the Role

Now it's time to set up the role in the multidimensional database. You create a new role, MyRole, with Read access to the Adventure Works cube. You add TestUser1 and TestUser2 as members.

Your next step is to limit access to the User dimension. You don't want end-users to query the User dimension and see a list of user accounts with access to the database. To secure it, you set an allowed set on the User attribute of the User database dimension as follows:

STRTOSET("[User].[User].["+Username+"]")

The Username function returns a string which is the full name of the end-user's user account. For example, when your TestUser1 user connects to the multidimensional database, the Username function will return "MyLaptop\TestUser1".

The Allowed Set requires you to specify a set of members in the User attribute hierarchy. You concatenate the string returned by the Username function with other strings to assemble a definition for a valid, one-member set. You then convert that string into an actual set with the STRTOSET function.

Now it's time focus your attention on the Reseller dimension. You will leverage the relationship between the User dimension and the Reseller dimension captured in the Reseller User measure group. Of course, this relationship exists within the Adventure Works cube so you will be working with the Reseller cube dimension.

For the Allowed Set on the Reseller attribute hierarchy, you specify the following:

EXISTS(

    [Reseller].[Reseller].Members,

    STRTOSET("[User].[User].["+Username+"]"),

    'Reseller User'

    )

The EXISTS function returns the set of members from the first set, [Reseller].[Reseller].Members, associated with the members in the second set, STRTOSET("[User].[User].["+Username+"]"), as determined by a specified measure group, 'Reseller User'. It is important to note that in indicating measure group, you are must state the measure group's name, not the name of a measure within the measure group, and that name must be enclosed in single-quotes. Many first time user's of this variation on the EXISTS function are frequently confused by these points.


 

Testing Security

Once the MyRole is deployed to the SSAS instance, you need to test the set up. To do this, you run SQL Server Management Studio as one of our test users, TestUser1. (From the Start menu, select Programs then Microsoft SQL Server 2005. Right-click the SQL Server Management Studio icon and select Run As from the context menu. Enter the appropriate account information to start SSMS as TestUser1.)

Once started, you open an MDX Query window and issue the following statement:

select

    {} on 0,

    [User].[User].Members
on 1

from    [Adventure Works]

;

All

MyLaptop\TestUser1


 

You then execute this statement:

with
member [Measures].[Reseller Key] as

    [Reseller].[Reseller].CurrentMember.UniqueName

select

    [Measures].[Reseller Key] on 0,

    [Reseller].[Reseller].Members
on 1

from    [Adventure Works]

;

 

Reseller Key

All Resellers

[Reseller].[Reseller].[All Resellers]

A Bike Store

[Reseller].[Reseller].&[1]

Advanced Bike Components

[Reseller].[Reseller].&[3]

Metropolitan Sports Supply

[Reseller].[Reseller].&[5]

Modular Cycle Systems

[Reseller].[Reseller].&[4]

Progressive Sports

[Reseller].[Reseller].&[2]

24 comments:

RaphGre said...

Hello,
I try to implement your solution, which is what I need. But I have an error message when I add the filter in the Allowed Member of reseller : The [User] Dimension does not exist in the cube. Did you have this issue ? Thanks.

spkn said...

Hi! raphgre: I trying to implement Dynamic security on SSAS 2k5. Got error message when I used the following MDX query. Hope you could fix this.

EXISTS ( [Dim Account] . [Dim Account]. Members,
STRTOSET( "[Dim Employee] . [LoginID] . [ " + Username + "]"),
'Fact Secure Account'
)

Got struck here and not much help on googl search. Appreciate help.

Regards
spkn

spkn said...

Bryan:

Appreciate you help.
MDX Query:
----------
EXISTS ( [Dim Account] . [Dim Account]. Members,
STRTOSET ( " [Dim Employee] . [LoginID] . [" + Username + "]"),
'Fact Secure Account'
)
-------------------------------
Getting errors:

Check MDX script syntax failed because of the following error:
----------------------------------
An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[Dim Employee]' was not found in the cube when the string, [Dim Employee].[LoginID].[CORP\Name], was parsed.
-----------------------------------

Appreciate your help.

Sam said...

Yes, I'm having that problem too. I was using the following mdx

FILTER([Course Session].[Hierarchy].[Session].MEMBERS,
(
FILTER([Staff].[Login].MEMBERS,
instr([Staff].[Login].CURRENTMEMBER.NAME,USERNAME))
.ITEM(0),[Measures].[Access])=1
)

This runs just fine in Man Studio, but when I put it in the allowed member set pane, I get the following error:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (5, 10) The member '[Access]' was not found in the cube when the string, [Measures].[Access], was parsed.

I'm totally at a loss. [Measures].[Access] is working just great everywhere else.

I ripped off this approach, pretty much verbatim from the Wrox SQL SERver 2005 with SSAS book (Chapter 19). When I downloaded their sample database and solution, which had this example in it, that threw exactly the same error!!

cpnet said...

I was struggling with the same issue before I came across this post. I'm pretty new to SSAS, but it seems to me that when defining Roles in BI Studio, the MDX to defined the allowed/denied members for a dimension cannot reference other dimensions.

I was able to work around this. I used the same table structure as in the post. And, I defined the same allowed member set for DimUser (except I used:
STRTOSET("[Dim User].[User Name].["+Username+"]")
to be consistent with my naming).

I also checked the "Enable Visual Total" check box.

I did NOT define allowed/denied members for other dimensions.

When setting up my cube, I include "DimReseller" as both a Cube Dimension, and a Measure Group. FactResellerUser is inclucded as a Measure Group, and DimUser is included as a CubeDimension. I create 'Regular' relationships between the Cube Dimensions "Dim Reseller" and "Dim User" and the Measure Group "Fact Reseller User".
I also create a 'Many-to-Many' relationship between the Cube Dimension "DimUser" and the Measure Group "DimResller" using "FactResellerUser" as the intermediate Measure Group.

When I added FactResellerSales to my cube as a MeasureGroup, I found I also had to create a 'many-to-many' relationship between the CubeDimension "DimUser" and the Measure Group "FactResellerSales" using "DimReseller" as the intermediate Measure Group if I wanted to make sure I was only seeing Reseller Sales for the Resellers I was supposed to have access to.

I'm not sure if this is the right way to do things, but it seems to work.

cpnet said...

I did a bit more playing around with this, and found that what's in the original post can work. While what I posted does seem to work too, I would guess that what's in the original post will have better performance (and it's a bit easier to set up too).

Here's what I did to get things to work (I'm including FactResellerSales because I want to limit access to that data too):

First set up the tables as suggested in the post.

When you're creating your cube, include the tables as follows (NOTE I used the BI Studio 2005 Cube Wizard and other than adjusting whether tables were used as Measure Groups and/or Cube Dimensions, I did not adjust the dimensions or facts or create hiearchies etc.):
- DimReseller -> Measure Group and Cube Dimension
- DimUser -> Cube Dimension
- FactResellerUser -> Measure Group
- FactResellerSales -> Measure Group

- Cube Dimension "DimReseller" should have a 'Fact' relationship to Measure Group "Dim Reseller", a 'Regular' relationship to Measure Group "Fact Reseller User", and a 'Regular' relationship to Measure Group "Fact Resller Sales"

- Cube Dimension "Dim User" only needs a 'Regular' relationship to Measure Group "Fact Reseller User".


When you set up your role, use the following MDX for the "Allowed member set" for the "User Name" attribute of the "Dim User" Dimension (not the Cube Dimension, the regular Dimension):
STRTOSET("[Dim User].[User Name].["+Username+"]")

You do NOT need to enable Visual Totals!


The other thing you need to set in your role is the "Allowed member set" for the "Dim Reseller" CUBE Dimmension. This is important. If you put the security on the regular "Dim Reseller" Dimension, it doesn't seem to work. You have to secure the CUBE Dimension for "Dim Reseller" (I guess this is because only the Cube Dimension knows about the relationships defined for the cube)? Anyway, set the allowed member set to:
EXISTS(
[Dim Reseller].[Dim Reseller].Members,
STRTOSET("[Dim User].[User Name].["+Username+"]"),
'Fact Reseller User'
)

This time you DO need to enable Visual Totals (at least if you want to restrict the FactResellerSales data).

cpnet said...

I did some more testing of the two solutions I've been posting about, and I've found an issue with the approach that I just finished suggesting was a better approach...

The problem is that the "Fact Reseller User" Measure Group isn't one you'd typically want end-users to see. It's just there to support the security. So I used BI Studio 2005 to hide the "Fact Reseller User Count" Measure from this Measure Group (this is the only Measure in the Measure Group). When I browse the cube, this Measre Group is no longer present, which is what I want. However, DimReseller no longer has any members at all - and that's not what I want.

I don't have this issue with the somewhat more complex solution I was originally following. I can make the "Fact Reseller User" Measure Group measures not Visible, and I still see the subset of DimReseller member that I should see when I browse the cube.

I guess the MDX expression to limit access to the DimReseller members is impacted by the visibility of the "Fact Reseller User" members. Maybe there's a different MDX expression to limit the allowed members of DimReseller that wouldn't have this problem?

lala said...

Thanks for your post and welcome to check: here.

Sam Kane said...

Here are this and some other articles on Analysis Services Custom Security:

http://ssas-wiki.com/w/Articles#Custom_Security

NealH said...

I had some problem but did manage to get it working by insert the & in the filter to express the tuple correctly of the username
[DimUsers].[UserName].&[+Username+].

Not sure if this was right but it seemed wrong not using the ampersand to reference an individual member of a dimension?

Cheers

Neal

Dheeraj said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit SEO Services Company

kvikas said...

nice post very well written. i appreciate quality of writing u haveMarkham Developer

craig pattinson said...

nice post very well written. i appreciate quality of writing u havefrases

flash said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Voip Service

hoston said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
windows virtual private server

vikas kukreja said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Greenville Plumber

craig pattinson said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Plumbing Greenville

shanewatson said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Houston Electrician

harrywatson said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Houston Electricians

neronjayson1 said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Birmingham website design

Pierre Osborne said...
This comment has been removed by the author.
Pierre Osborne said...

SOLUTION TO COMMON MISTAKE

For those that are getting the following error while configuring the role:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (5, 10) The member '[Access]' was not found in the cube when the string, [Measures].[Access], was parsed.


The problem is most likely because your select the dimension outside of the scope of the cube which is why its complaining it doesn't know about the User dimension.

When you select the dimension on the Dimension Data tab, keep scrolling down you will see additional grouping with the cube name. This is the one you want.

Good luck!

Preetham Ema said...

Hi

Is there a way we can implement the security for multiple dimension

Mindaugas Grigas said...

The dynamic roles are cool if you have the data to support it, but sometimes there is no ready link between a userID and dimension member.

Try using easyROLES for security management. With our tool you can add many ssas roles quickly and can even outsource role management back to the data owners in the business.