Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Dynamic Security in Tabular

  • 5 June 2013
  • Author: Chris Schmidt
  • Number of views: 16498
  • 0 Comments

Tabular databases, like multidimensional, have the ability to create roles for additional security against our databases for individual business users. What’s more, it’s even easier to set up. Like other things in tabular, gone are the 9 or 10 different tabs, basic/advanced toggling etc. Roles in tabular and DAX use two functions to establish and manage security: the USERNAME DAX function and the LOOKUPVALUE DAX function. Lookupvalue is quickly becoming one of my favorite DAX functions, but that’s a different story. :) Something important to consider when

First we’re going to go into our database and add a table that we can use to map to what we’re securing by. For this example, I’m going to map between the employee dimension and the sales territory dimension. To keep it simple, though, I’m going to list out the employee names, rather than having a EmployeeKey/Sales Territory key bridge table. Start by creating a table called Dynamic Security with the employeekey, names, and userlogon fields. My script is below:

CREATE TABLE dbo.UserSecurity
    ([EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    )
GO

 

Then let’s input some values into it.

INSERT INTO [dbo].[UserSecurity]
VALUES    (1, 'Administrator','','DATA0\administrator')
INSERT INTO [dbo].[UserSecurity]
VALUES    (2, 'Administrator','','DATA0\administrator')
INSERT INTO [dbo].[UserSecurity]
VALUES    (3, 'Bob','Williams','DATA0\bob')
INSERT INTO [dbo].[UserSecurity]
VALUES    (4, 'Steve','Smith','DATA0\steve')

 

Now that we have our table created, we can do the actual security implementation. Open up the tabular database that you wish to apply the security too, and import your table. We’ll select and open our existing connection:

image

 

Then select the user security table that we created in Management studio and click Finish.

image

 

Once it’s imported, we then want to join it to the appropriate tables so we can relate to our security table. For this example we’re just joining to the Sales Territory. A snapshot of this piece of the model looks like:

image

 

Right click on the User Security table and click “Hide from Client Tools”. Now we can create our role. Scroll up to the top and click on Roles: (screenshot below if you need it)

image

 

Create a New Role, and give it a name. In this case, I’m calling it “Sales Territory Users”, giving read access under the permissions. Once you’ve done that, click on the Members tab, and add the users we have in our table. Below is a screenshot of what mine looks like with the 3 members I have in my table:

 

image

Go back to the Row Filters tab, and find the User Security table listed. Under the DAX Filter column, type in the following:

= FALSE()

Now find the Sales Territory table, and type in the following formula:

='Sales Territory'[SalesTerritoryKey]=LOOKUPVALUE(UserSecurity[SalesTerritoryKey], 'UserSecurity'[UserName], USERNAME(), UserSecurity[SalesTerritoryKey], 'Sales Territory'[SalesTerritoryKey])

What this formula does is to say, “for each value in the UserSecurity[SalesTerritoryKey] column, find the current logged in Windows user name, and match it to the UserSecurity[SalesTerritoryKey] column. And only show rows where they match”

 

My Row FIlters section looks like the following:

 

image

 

Click Ok, and it will validate and finish your changes. Then go up to “Analyze in Excel”. Select “Other Windows user” and pick one of the users you have in your list. For this example, I picked Bob:

 

image

 

Click Ok, and then drag over your sales territory in the pivot table that appears. Drag over a measure so you get some data, and WHAMMY! some dynamic security awesome-ness:

 

image

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.