Implementing User-Specific Security in SSAS

Implementing User-Specific Security in SSAS

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]

Later, 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 hierarchies 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 Reseller 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(“[DimUser].[UserName].[“+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).

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?

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

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

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.

Courtesy:  Bryan C. Smith