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]
;
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] |
