Introduction

Maintaining complex security in a cube can be a challenge and rather cumbersome, especially if there are a lot of different user roles that need to be accounted for. One example would be a company has 100 salespeople and each salesperson is only allowed to see their own data. If you want to do this using a non-dynamic approach you would have to create 100 roles in the JDM and then, for each role, select a single salesperson. With the dynamic approach this can be accomplished in a much more elegant manner.

This document will focus on setting security on the company dimension in our standard NAV cubes but the concept applies to any dimension where access has to be restricted.

The Concept

Since a record in a fact table typically can be viewed by many users, we cannot simply add the username to the fact table and then filter on that. There is a many-to-many relationship between users and fact table records where one record could be accessible to many users and any given user could have access to many records. Bearing this in mind, it is possible to relate users to fact table records by using a bridge table that will serve as an intermediate between a User dimension and the dimension we want to restrict. The figure below shows the data model that we want to achieve:

Figure 1: Data Model for Dynamics Security

The figure shows that we need a User table containing all users that should have access to the cube. We also need a table that can bridge our users over companies into our fact table. The easiest way to do this would be to maintain an Excel workbook containing a list of all users and which companies they are allowed to see.

If you have already created Windows Active Directory (AD) groups for each company, we can utilize this and explode the groups into users either by using an SSIS package or by using the xp_logininfo stored procedure on the SQL Server.

The Implementation

The implementation will be split into two groups: SQL Server and Analysis Services. In the JDM, this would be the Data tab and the Cubes tab respectively.

The Data Tab

1. The first step is to create an Excel workbook defining the access. Here you need to determine which approach you want to use: AD Groups or individual users.

   Table 1: Excel Workbook Format

   	UserName	Dimension	MemberValue
   Format	<DOMAIN\UserName>	<DIM_NAME>	<Member>
   Example	SWPROS\JetIncUsers	Company	Jet Reports Inc.

   The Excel workbook will have three columns. The UserName column contains the AD User or group, the Dimension contains the name of the dimension you want to restrict, and the MemberValue contains the key value of the dimension member the user or group is allowed to access. If a user or group has access to multiple dimension members, simply create multiple rows for that user or group.

   Once the Excel workbook has been created load the sheet into the JDM. The loaded table should contain all users and dimensions where restricted access is required. In this example, we will only restrict access on the Company dimension but the concept will be the same for multiple dimensions and this article will specifty where you need to replicate tasks in a multi-dimension setup.

   

   Figure 2: Excel Workbook in the JDM

   In the above example, the Excel connection has been named “AccessControl” and the sheet name in the Excel workbook is named “UserAccess”. This creates the staging table: “AccessControl_UserAccess$”

   
   

2. Exploding the AD Groups into Users

   If you use individual user names you can skip the next steps and jump to the section “ Creating Views for Users and Company Access ”.

   Since AD Groups are being used in the UserName column, there is a need to populate a table containing each individual username. We need this because the security is validated against the user browsing the cube and hence AD groups cannot be used.

   The first step will be to grant all AD Groups in the Excel workbook rights to the SQL Server where our staging database is located. This is done by connecting to the SQL Server from SQL Server Management Studio.

   

   Figure 3: Adding AD Groups to the SQL Server

   Right click on Security → Logins and choose New Login and add the AD group.  This will grant login rights to the server but will not give the users access to any databases which is fine for the purposes of this example as we only want to be able to extract the usernames from the groups using the xp_logininfo stored procedure.

   Once all AD Groups have been added to the SQL Server, you can then extract the usernames associated with the groups.  In order to have a place to store the users, you should create a table identical to the AccessControl_UserAccess$ table.

   The easy way to do this is to right click on AccessControl_UserAccess$ → Advanced → Copy Table Structure in the JDM.  

   Rename this new table to UserAccessUsers.

   To populate the table, we need to write a script that will convert the AD Groups into usernames.  The script for this is found below.

   Convert AD Groups to Usernames

   DECLARE @NTLogin nvarchar(128)

CREATE TABLE #UserList
(                 [Account Name] nvarchar(128),
[Type] nvarchar(128),
[Privilege] nvarchar(128),
[Mapped Login Name] nvarchar(128),
[Permission Path] nvarchar(128)
)
DECLARE cur CURSOR FOR
SELECT DISTINCT UAG.[UserName]
FROM [AccessControl_UserAccess$_V] UAG

OPEN cur

FETCH NEXT FROM cur INTO @NTLogin

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #UserList EXEC xp_logininfo @NTLogin, 'members'
FETCH NEXT FROM cur INTO @NTLogin
END

TRUNCATE TABLE [UserAccessUsers_V]

INSERT INTO [UserAccessUsers_V]
(
[UserName], [Dimension], [MemberValue], [DW_SourceCode]
)
SELECT UL.[Account Name], UA.[Dimension], UA.[MemberValue], 'Script'
FROM #UserList UL LEFT JOIN [AccessControl_UserAccess$_V] UA
ON UL.[Permission Path] = UA.[UserName]

DROP TABLE #UserList
CLOSE cur
DEALLOCATE cur

   The above script will loop through the Excel table and resolve the AD groups to usernames. These are then written to the UserAccessUsers table. This is the table that will be used going forward.

   If you have named fields or tables differently in your implementation, you will need to modify the script accordingly.

3. Next, you need to make sure that the script is executed when executing the project. This can be done by adding the script to the UserAccessUsers table as a post data cleansing script. Right click the table and go to Advanced -> Set Pre and Post Scripts.  Figure 4 below shows the setup in the JDM.

   

   Figure 4: Script implementation

   Now that the user names have been successfully extracted from our AD groups, we can then start creating views for users and for the company access.

4. Creating Views for Users and Company access

   As mentioned earlier, there needs to be a table containing all users and a table to bridge our users to the fact table we want to restrict. For this you will create 2 views.

   In the JDM create two custom views:

   Users View

   CREATE VIEW [dbo].[Users] AS
SELECT DISTINCT  [UserName]
FROM [dbo].[UserAccessUsers_V]

    

   Company Access View

   CREATE VIEW [dbo].[CompanyAccess] AS
SELECT DISTINCT
[UserName]
,[MemberValue] AS [Company]
FROM [dbo].[UserAccessUsers_V]
WHERE [Dimension] = 'Company' AND [MemberValue] <> '*'
UNION
SELECT UA.[UserName], C.[Name]
FROM [dbo].[UserAccessUsers_V] UA CROSS JOIN [dbo].[NAV_dbo_Company_V] C
WHERE UA.[Dimension] = 'Company' AND UA.[MemberValue] = '*'

   If some users will have access to all companies you can create an AD group for these users. In the Excel sheet you assign the member value “*” to this group (without quotation marks). The above view will then get all companies from the company table for these users. If you are restricting on another dimension you would of course need to modify the view to look at the table containing all values for this other dimension.

   If you are restricting on multiple dimensions you need to create an access view for each dimension you are restricting. Drag the access view(s) and user view to the data warehouse node in the JDM so that they can be used for the bridge table and user dimensions.

The Cubes Tab

Everything is now set up to implement the security in the individual cubes.

Preparing the cubes

1. First, add the table CompanyAccess as a new fact table to the cube.

   

   Figure 5: CompanyAccess as a Fact Table in the Cube

   Create a new hidden measure for the CompanyAccess fact table, i.e. CountCompany, as every fact table needs to have at least one measure created from it.

2. If you want to report on how many companies the user has access to, you can leave the measure visible by checking the Visible flag:

   

   Figure 6: Hidden Measure for the CompanyAccess Fact Table

3. The next step is to create a new dimension for the users.  This dimension, together with the CompanyAccess fact table, will limit what the user is allowed to see in the other fact tables. You can hide the dimension in the cube unless you want to report on who has access to which companies.

   

   Figure 7: User Dimension

   Add the Users dimension to the cube that you want to restrict.  Rename the Users dimension to UsersCompany and set up relationships for the Users and the Company dimensions.

4. If you are restricting multiple dimensions, you need to drag a copy of the Users dimension to the cubes for each dimension you are restricting.

   
   

   

   Figure 8: Dimension Relationships

5. The last thing to do before setting the security in the cube will be to set up a many-to-many relationship between the Users dimension and the fact tables in the cube.  For each fact table, you will create a many-to-many relationship with the Users dimension (not including the CompanyAccess fact table).  To do this, right-click on the dimension and click Add Many to Many Relation.

   

   Figure 9: Many-to-Many Relationship

   Make sure you relate the fact tables through the CompanyAccess fact table.  If you have multiple dimensions you restrict please do this for all of the user dimensions in the cube.

Adding the Security Roles to the Cubes

With everything prepared, it is now possible to set up the security roles that will restrict the access to the cube. Before the OLAP Server User Rights function is accessible, please make sure the project has been deployed and executed.

1. First add a new role. Then add the users who should be granted access through the role. Best practice would be to create an Active Directory Group for the role. The AD Group should contain the users you want to grant access to through this role. You would then add this AD Group as below.

   

   Figure 10: Adding a Role to the OLAP Database

2. Give the role access to all cubes.  By default, this will be set to Read access.  If you want to allow the users to drill through, you can change the setting to Read with Drillthrough.

   

   Figure 11: Cube Access

   Clicking on the Cubes node will give you a list of all cubes in the right-hand window and you can easily change the setting to Read with Drillthrough.  In the example above there is only one cube.

3. It is now possible to restrict the access on the Company dimension.

   First you need to make sure you do not inherit the settings on the global Company dimension.  You do this by clicking on the dimension Company in the cube dimensions and selecting NoInheritance.

   

   Figure 12: No Inheritance on cube dimension

4. The last thing to do is to restrict the Company level within the Company dimension.

   

   Figure 13: Restricting the Company level

   MDX Code

   EXISTS
(
{[Company].[Company].[Company].Members},
STRTOSET("[UsersCompany].[UserName].&[" + UserName + "]"),
"CompanyAccess"
)

   If you are using other names for your Company and UsersCompany dimensions, please modify the code accordingly.  This also applies for the fact table CompanyAccess.

5. Deploy the rights to the server.

Testing

The easiest way to test the security is to open SQL Server Management Studio (SSMS) and connect to Analysis Services. Next, open the cube to test and change the security context to mimic the desired user(s).

Figure 14: Simulating user access in SSMS

For this example, the test should confirm that:

• TestUser1 has access to the UK Company
• TestUser2 to the US Company
• TestUser3 has access to both companies

Figure 15: Tested Access in SSMS

This can then be compared to the data in the CompanyAccess table.

 Figure 16: Access Defined in the CompanyAccess table

As you can see in the examples above, everything ties up perfectly and the security has been implemented correctly.