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.
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 Dynamic 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 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
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
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$”
Exploding the AD Groups into Users
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.
DECLARE @NTLogin nvarchar(128)
CREATE TABLE #UserList
( [Account Name] nvarchar(128),
[Mapped Login Name] nvarchar(128),
[Permission Path] nvarchar(128)
DECLARE cur CURSOR FOR
SELECT DISTINCT UAG.[UserName]
FROM [AccessControl_UserAccess$_V] UAG
FETCH NEXT FROM cur INTO @NTLogin
WHILE @@FETCH_STATUS = 0
INSERT INTO #UserList EXEC xp_logininfo @NTLogin, 'members'
FETCH NEXT FROM cur INTO @NTLogin
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
The 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.
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.
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:
CREATE VIEW [dbo].[Users] AS
SELECT DISTINCT [UserName]
CREATE VIEW [dbo].[CompanyAccess] AS
,[MemberValue] AS [Company]
WHERE [Dimension] = 'Company' AND [MemberValue] <> '*'
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.
The Cubes Tab
Everything is now set up to implement the security in the individual cubes.
Preparing the cubes
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.
Figure 6: Hidden Measure for the CompanyAccess Fact Table
If you want to report on how many companies the user has access to you can leave the measure visible by ticking the Visible flag.
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.
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.
First add a new role. If the security should apply for all cubes, you can create one role called Company Restrictions and add Authenticated Users to the role.
Figure 10: Adding a Role to the OLAP Database
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.
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 select “NoInheritance”.
Figure 12: No Inheritance on cube dimension
The last thing to do is to restrict the Company level within the Company dimension.
Figure 13: Restricting the Company level
STRTOSET("[UsersCompany].[UserName].&[" + UserName + "]"),
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.
Deploy the rights to the server.
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, and 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 15: 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.