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]

39 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?

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

Anonymous said...

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

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

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

Unknown said...
This comment has been removed by the author.
Unknown 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!

Unknown said...

Hi

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

Unknown 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.

mist hyderabad said...

Thank u for this information
http://www.mistltd.com

Unknown said...

This information is really awesome thanks for sharing most valuable information.
MS Power BI Online Training
Power BI Online Training
Power BI Training
Power BI Training in Hyderabad
MS Power BI Training in Hyderabad

python training in vijayawada said...

We as a team of real-time industrial experience with a lot of knowledge in developing applications in python programming (7+ years) will ensure that we will deliver our best in python training in vijayawada. , and we believe that no one matches us in this context.

GlobalEmployees said...

Find my blog post here
web designer
salesforce developer
laravel developer
web developer

python training in vijayawada said...

We as a team of real-time industrial experience with a lot of knowledge in developing applications in python programming (7+ years) will ensure that we will deliver our best in python training in vijayawada. , and we believe that no one matches us in this context.

Apextrainings said...

Good Blog & Thanks for sharing.
Best aws training in Hyderabad
Aws training institutes in Hyderabad

python training in vijayawada said...

We as a team of real-time industrial experience with a lot of knowledge in developing applications in python programming (7+ years) will ensure that we will deliver our best inpython training in vijayawada. , and we believe that no one matches us in this context.

Unknown said...

hanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
servicenow online training
best servicenow online training
top servicenow online training

varsha said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
AWS training in chennai | AWS training in anna nagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

keerthana said...

osm page
PHP Training in Chennai | Certification | Online Training Course | Machine Learning Training in Chennai | Certification | Online Training Course | iOT Training in Chennai | Certification | Online Training Course | Blockchain Training in Chennai | Certification | Online Training Course | Open Stack Training in Chennai |
Certification | Online Training Course



dhinesh said...

An overwhelming web journal I visit this blog, it's unfathomably amazing. Unusually, in this present blog's substance made inspiration driving truth and reasonable. The substance of data is enlightening.


Full Stack Course Chennai
Full Stack Training in Bangalore

Full Stack Course in Bangalore

Full Stack Training in Hyderabad

Full Stack Course in Hyderabad

Full Stack Training

Full Stack Course

Full Stack Online Training

Full Stack Online Course



Unknown said...

"abrigos de pelo mujer lefties
bolsas de hule transparente
nike cortez prm
ladies summer shorts
gants si assault factory pilot noir oakley
diffuseur par nébulisation à froid amazon
filtre pour aspirateur samsung sc4780
chargeur hp ordinateur portable amazon
moufles millet expedition 8000 m
kimono long femme grande taille
pull training nike
chaussure de basketball nike zoom kobe 11 femme
soldes chaussures homme caterpillar hiver cafétéria
tabouret pour toilette amazon
niveau à laser amazon surligner escorte
kiabi bottine femme
zalando nike zoom fly
pull lacoste col montant activer
chaussure carla moreau
bermuda femme en lin dor
polo ralph lauren jeanshemd damen
nike tanjun damen weiß 44
timberland herren 3 eye
parka mit farbigem
jabo 2 futterboot amazon
nike shirt langarm
all star converse star player
brokkoli kostüm
panini fußball sticker 2016 amazon
jeansjacke mädchen 164
adidas nmd wildleder herren
air max 97 capitao america
adidas cal surf
moleton nike preço
puma calçados femininos desvaneça"

Unknown said...

"chinelo slide da melissa
nike les halles
outlet nike floresta
sandália rasteira bottero
tende doccia milano amazon
tenis all star feminino branco couro
venda de cortina
nike air force modelo agotado
camisa ponte preta aranha
nike color block hoodie
sandalias courofeito a mao
damen lack schnürer
nike dual fusion tr iii
nike sb air max bruin vapor
nike t shirt tumblr
cappello fisi kappa tazza
adidas stabil x junior
brassiere garcon
nike sb zoom mogan mid 2 6.0
combinar vestido lentejuelas
jersey rombos hm
adidas boost 350 black
smartphone kleine abmessungen amazon
shirt mit schnürung am ausschnitt
schubkarre gestell amazon
skechers shoes for men online
leggings mit spitzenabschluss
spielzeug nach altersgruppen amazon
s oliver catie bell bottom
kugelbahn ab 12 monate amazon
beutel zipper amazon
hosen bei peek cloppenburg
nike jogginghose dunkelgrau damen
nike anzug nba rot
rückgaberecht adidas
nike air max blancas mujer baratas"

eddielydon said...

I am glad to be here and read your very interesting article, it was very informative and helpful information for me. keep it up. BLS Denim Vest

andrewjackson said...

Our the purpose is to share the reviews about the latest Jackets,Coats and Vests also share the related Movies,Gaming, Casual,Faux Leather and Leather materials available Ghost Modern Warfare Jacket

mrbobystone said...

I love to recommend you Where can crawl Exciting Products latest Jackets, Coats and Vests Click Here Rip Wheeler Jacket

George Mark said...

Incredible blog here! It's mind boggling posting with the checked and genuinely accommodating data. Fonzie Leather Jacket

Bape Hoodie said...

I'm truly glad to say it's an intriguing post to read. I get new information from your article. bape hoodie

yazzygaphoodies said...
This comment has been removed by the author.
Techworld said...

Thia post is really useful and informative.Thanks for sharing this amazing article. you can also check this: r certification course

Techworld said...

Thanks for sharing this amazing post. azure solution architect training

Chaitanya said...

Definitely, what a fantastic website and informative posts, I will bookmark your website. Have an awsome day!
SAP WM Training from Hyderabadg
Business Analysis Training from Hyderabadg
SAP FICO Training from Hyderabadg
<a href="https://viswaonlinetrainings.com/courses/oracle-bpm-online-training/>Oracle BPM Training from Hyderabadg</a>