Category: Security

Stopping Some Users Seeing Certain Columns Or Measures In Your Power BI Report With Object Level Security And Field Parameters

If you have sensitive data in your Power BI dataset you may need to stop some users seeing the data in certain columns or measures. There is only one way to achieve this: you have to use Object Level Security (OLS) in your dataset. It’s not enough to exclude those measures or columns from your reports or to hide them, because there will always be ways for enterprising users to see data they shouldn’t be allowed to see. However the problem with OLS up to now is that it didn’t play nicely with Power BI reports and so you had to create multiple versions of the same report for different security roles. The good news is that there’s now a way to create one report connected to a dataset with OLS and have it display different columns and measures to users with different permissions.

Let’s say you have a dataset and report that looks like this:

As you can see, it displays the names and addresses of employees along with sales and bonus data.

Now let’s say that the address and bonus data should only be visible to HR and everyone else should only be able to see the names and sales values. As I said, the only way to achieve this is to create a role that uses OLS to deny access to the address and bonus columns. Gilbert Quevauvilliers has a great post showing how to set up OLS using Tabular Editor here so I won’t go into detail about how to do this, but here’s how I configured the role in Tabular Editor 3:

If you publish the report and test the role in the browser, you’ll see that you get a “The visual has unrecognized fields” error because the table in the report uses the Address and Bonus fields which, of course, the user cannot access because of the OLS:

Security is working as expected but wouldn’t it be great if, instead of seeing an error here, you could build a single report that displays all the fields when the user has permission to see them and only displays the Name and Sales fields to users who are members of the role with OLS applied?

Well, now you can thanks to the new field parameters feature. The intended use of field parameters is to enable the end users of your reports to choose the fields displayed in visuals using a slicer. Behind the scenes when you create a field parameter a table is added to your dataset with one row for each field you have chosen; this effectively makes the fields used in a visual data-driven, and you can use Row Level Security (RLS) on the table created for your field parameter to control which fields are displayed in your visual and solve the problem.

Going back to our report, the next step is to create a new field parameter with all the columns and measures used in the table:

Notice that the “Add slicer to this page” checkbox is deselected because you don’t need a slicer on the report here. Here’s what the table created for the field parameter looks like:

With the field parameter created it can be used instead of the individual fields in the table definition:

You can then edit the role that already has OLS in it to apply RLS on the field parameter table, so only the rows for the fields that are allowed by the OLS are returned:

'FieldsForMyTable'[FieldsForMyTable] = "Name" ||
'FieldsForMyTable'[FieldsForMyTable] = "Sales"

It’s important that the OLS and RLS are defined in the same role because of the restriction on combining OLS and RLS from different roles.

With all this done, when you view the report through the role you only see Name and Sales displayed:

It’s important to stress that the OLS is still securing the data here – the RLS is just preventing the errors.

One downside of this technique is that things could get complicated if you have multiple visuals that need to display different combinations of secured and non-secured fields in a report. There could also be a performance penalty: when a visual uses a field parameter an extra DAX query is run on the field parameter table to determine the fields to display, and while these queries should be extremely fast most of the time there’s always a risk that they somehow slow your report down.

In conclusion, this workaround isn’t ideal but I think it’s the best way to work with OLS in Power BI reports that’s possible at the moment.

[Thanks to John Vulner for background information on how field parameters work]

Is Power BI’s “Show Data Point As A Table” Feature A Security Hole?

In the last few months the following issue has been escalated up to the Power BI CAT team several times: customers have deployed reports into production and then found that users are able to see data they should not be allowed to see by using the “Show data point as a table” feature. The question is: is this a security hole? It isn’t, and in this blog post I’ll explain why and how you should think about security as something that happens on the dataset and not in the report.

Here’s a simple example of the problem. Say you have a dataset with the following table in it:

It contains sales data but the text in the Comments field is sensitive and should not be visible to everyone. If you have a report with a matrix visual in it, put Employee on columns and drag the Revenue field into values and sum it up (ie create an implicit measure rather than defining an explicit measure) like so:

…then an end user will be able to view the report, select a cell in the visual, right click and select “Show data point as a table” and see a table that contains unaggregated data including some of the fields from the underlying table that go to make up that value – including the Comments field.

Ooops! Of course it’s bad when an end user sees something they shouldn’t but this isn’t Power BI’s fault. As a Power BI developer it’s important to understand that visibility and security are not the same thing and that data security is something that is defined on a dataset and not in a report. You need to use features such as row-level security and object-level security to stop users seeing data they should not be allowed to see – or you should not import that data into your dataset in the first place. You can stop the “Show data point as table” option from appearing by changing the visual you use in your report or by using an explicit measure (ie one defined using a DAX expression), but that’s still not secure and there’s no guarantee that users would not be able to see the same data some other way.

In our example, with object-level security set up to deny access to the Comments field you can be sure that users will not be able to see that data unless they have permission. When viewing the report via a role with OLS defined then the Comments field will not appear when you use “Show data point as a table”:

Advanced SSAS Multidimensional Security Tips & Tricks Webinar This Thursday

In association with the nice people at SQLRelay I’ll be presenting an hour-long webinar on advanced SSAS Multidimensional tips and tricks this Thursday July 9th 2015 at 1pm UK time (that’s 8am EDT for you Americans). It’s free to attend and open to anyone, anywhere in the world. You can join the meeting by going to

http://t.co/apht1IhJlg

In the webinar I’ll be covering topics such as:

  • The difference between Allowed Sets and Denied Sets in dimension security
  • Handling security-related errors in your MDX calculations
  • The different ways of implementing dynamic security
  • Why you should avoid cell security, and how (in some cases) you can replace it with dimension security

…and lots more.

If you’re in the UK, you should definitely check out SQLRelay, an annual series of one-day SQL Server events that happens at a number of different places around the country each autumn. For more details, see http://www.sqlrelay.co.uk/2015.html

I’m presenting this webinar in my capacity as a sponsor of SQLRelay, so expect me to spend a small amount of time promoting Technitrain’s autum course schedule. There are some cool courses on SSIS, MDX, SQL Server high availability and data science/machine learning coming up, you know…

UPDATE: you can download the slides and demos from the webinar at http://1drv.ms/1LYk1k8 and watch the recording at https://www.youtube.com/watch?v=cB9F6IVo7MA

For whoever was asking about using a measure group to store permissions for dynamic security, this blog post has all the details: http://bifuture.blogspot.co.uk/2011/09/ssas-setup-dynamic-security-in-analysis.html

Replacing Cell Security with Dimension Security

Cell security, as you probably know, is best avoided because of the impact it can have on query performance; dimension security is much easier to work with and its performance impact is much less. Luckily even when it seems as though cell security is the only option, in at least some scenarios with a bit of extra cube modelling work dimension security can still do the job. Here’s how…

Let me give you an example that I worked on with a customer recently, translated to Adventure Works. The Internet Sales measure group in the Adventure Works cube contains three role-playing dimensions based on the Date dimension: Date (which represents the Order Date), Ship Date and Delivery Date. Let’s imagine that we want to implement dynamic security so that a user can only see data if either the Date or the Ship Date is in one particular year. For example if a user has access to the year 2003 they should be able to see data if the year on the Date dimension was 2003 or if the year on the Ship Date dimension was 2003, as shown in the cells highlighted in the following screenshot (note that the grand totals should also reflect only values from the two 2003 years as well, so on the last row below we should see a grand total of $387662.64 for the CY 2004 column):

How can dimension security be used to implement this? The trick is that it can’t without creating a new, hidden dimension to apply security to. To create this dimension, go to the DSV and create a new named query called DimSecurity with the following SQL:

select distinct OrderDateKey, ShipDateKey
, d.CalendarYear as OrderDateYear, s.CalendarYear as ShipDateYear
from
dbo.FactInternetSales inner join DimDate d
on OrderDateKey = d.DateKey
inner join DimDate s
on ShipDateKey = s.DateKey

This gets all the distinct combinations of order date and ship date from the fact table plus their related calendar years. Obviously this exact query might not be practical on a large fact table, but you get the idea – in order to apply dimension security to the range of cells we want, we need to create a new dimension designed so that its members can be mapped onto those cells.

Next, create an SSAS dimension from this table as follows, with three attributes:

  • A key attribute called OrderShipDate that has a composite key based on the OrderDateKey and ShipDateKey columns. I used the OrderDateKey column as the name column, but that’s not too important because this dimension will be hidden anyway. This attribute represents the distinct combination of order date and ship date.

  • Two other attributes, Order Date Year and Ship Date Year, based on the OrderDateYear and ShipDateYear columns.

This new dimension should now be added to the Adventure Works cube and a relationship added with the Internet Sales measure group as follows:

You can now set this dimension’s Visible property to False in the Cube Structure tab of the Cube Editor.

Then create a new role, grant it access to the Adventure Works cube, go to the Dimension Data tab, select the Security dimension and the OrderShipDate attribute and go to the Advanced tab. Then enter the following MDX in the Allowed Member Set box:

union(
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS, {[Security].[Order Date Year].&[2003]})
,
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS, {[Security].[Ship Date Year].&[2003]})
)

This grants access to all combinations of Order Date and Ship Date – all the members on the OrderShipDate hierarchy – that are either in the Order Date year 2003 or the Ship Date year 2003. Don’t forget to check the Enable Visual Totals box too.

Deploy then go to the cube browser and test the role. You should see the following results:

So we have the basic role working, but how can we make it dynamic? We don’t want to create a factless fact table that contains rows for every combination of Order Date and Ship Date because that could get very large very quickly; we only want to grant access at the year level.

Going back to the DSV, create a new named query called FactSecurity using the following SQL (inserting your own username as appropriate):

SELECT        ‘Mydomain\Myusername’ AS UName, 2003 AS CalendarYear

This will be our factless fact table that grants a user access to the year 2003. Then build a new dimension called User from the UName column to give you a dimension containing all your users, as you would in any normal dynamic security implementation; also build a new measure group in the Adventure Works cube from this table and give it a regular relationship with the User dimension and a regular relationship with the Security dimension at the OrderDateYear granularity:

Process, then go back to the role and change the MDX as follows:

union(
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS
, {nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )})
,
exists([Security].[OrderShipDate].[OrderShipDate].MEMBERS
, generate({nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )}
, {linkmember([Security].[Order Date Year].currentmember, [Security].[Ship Date Year])}
)
)
)

This is basically the same as the previous expression but it’s now dynamic and it now supports securing more than one year in the FactSecurity measure group for a single user. Let’s look at parts of this expression:

The following expression returns the set of members on the Order Date Year attribute of the Security dimension that the current user has access to:

nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )

We can use this directly in the first set passed into the Union function, but to find the equivalent Ship Date Years for each Order Date Year we need to use a combination of the Generate and LinkMember functions, as follows:

generate({nonempty([Security].[Order Date Year].[Order Date Year].MEMBERS
, (strtomember("[User].[Security User Name].&[" + Username() + "]", constrained)
, [Measures].[Fact Security Count]) )}
, {linkmember([Security].[Order Date Year].currentmember, [Security].[Ship Date Year])})

This takes the set returned by the previous expression and then loops over it using Generate; for each member in the set we then find the equivalent Ship Date Year by using LinkMember.

In summary, while this might seem quite complicated the MDX used for the equivalent cell security implementation would be equally bad and the performance overhead much worse, so this is a very useful technique to have in your toolkit. Thanks are due to Peter Holzner, Ramon Mueller, Beat Stuenzi, Chris Fleming and Adam Widi for working through all this with me the other week.