Power BI semantic models have the option to set up row level security (RLS). And this includes Direct Lake Power BI semantic models in Microsoft Fabric!
Update 2, (April 2024): RLS UI is now available to set up RLS on Direct Lake semantic models when editing them on the web!
Update 1: Editing a Direct Lake semantic model and editing it on the web can be used interchangeably! Previously any XMLA edit would mean you couldn't edit in the web anymore.
This tutorial goes through the steps in detail!
Prerequisites (link to section). This includes using Microsoft Fabric, setting up a Lakehouse, and a new Direct Lake semantic model. Also, how to turn on XMLA read/write for a capacity.
Before you start steps (link to section). This includes the very important step of setting up your Direct Lake semantic model with a fixed identity. And how to duplicate a semantic model, if you would prefer to try it out without impacting a production semantic model first.
Setting up RLS (link to section). Finally, the meat and potatoes of this post!
Alternative approach of setting up RLS in Tabular Editor (link to section). The XMLA endpoint is just an amazing thing.
Testing your RLS with Test as role in the web (link to section). Did you even know you could do this??
Publishing, sharing as an app, and validating it works (link to section). Know how to set up permissions so the users can only see the data you want them to see.
More links and notes (link to section). The essential gotchas.
Let's get started!
First, some prerequisites:
Turn on your Microsoft Fabric trial or have access to a paid Microsoft Fabric capacity.
Create a workspace and make sure it's in the Trial or Fabric capacity.
Create a Lakehouse and populate delta tables with data. I have a blog post to show you how to do that with generated data!
Create a New semantic model from the Lakehouse or SQL analytics endpoint, or Warehouse. This tutorial is not for the default semantic model, it is for a custom one created from that New semantic model button.
Turn on editing of data (semantic) models for the workspace. Workspace Settings > Power BI > General > Data model settings
6. (Optional to use Tabular Editor 2) Turn on the read/write XMLA endpoint for the trail or Fabric capacity. Top -right cog > Admin Portal (you do not need to be the Power BI tenant admin) > Capacity settings > (close any banner that may show) > Trial > Click on the NAME of the capacity (not the cog) > XMLA Endpoint dropdown should be changed to Read/Write. Also see https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-overview#model-write-support-with-xmla-endpoint.
7. (Optional to use Tabular Editor 2) Get the workspace XMLA endpoint/workspace connection. Workspace settings > Premium > Workspace connection copy.
Second, before you start, a couple things to consider.
If you want to try setting up RLS out on an existing semantic model without impacting your model running in production, you can simply duplicate the semantic model first.
To duplicate the Direct Lake semantic model in Tabular Editor 2:
Open Tabular Editor 2.
Connect to the workspace (use the workspace connection you copied) and choose the semantic model you want to duplicate.
Go to Model > Deploy
You should see the same workspace you specified in step 2, otherwise paste it in again. You can use a different Fabric workspace if you want, but this demo assumes you used the same one.
Give it a new name, I just appended XMLA to the end of the existing name.
Click Deploy.
Go to the Power BI service and navigate to the workspace. You should see it there!
8. Finally, refresh the model to make sure the tables are processed and runs in Direct Lake without falling back to DQ.
A really critical part not to forget is that RLS should be used with a fixed identity.
The default connection from the Direct Lake semantic model to the Lakehouse is single sign-on. This means the person looking at the report accesses the Lakehouse delta table data using their credentials. You can and should change this to be a fixed identity. Fixed identity is when the model owner goes into the semantic model settings and specifies their credentials to be used no matter who is looking at the report. The RLS in the semantic model still knows who is looking at the report and applies the semantic model RLS rules for that user. But the model accesses the Lakehouse table data with the fixed identity credentials to show the visuals in the report.
If you give the user "Viewer" permission to the workspace (not recommended if this is data you want secured) then they can read all the data in the Lakehouse when clicking on the Lakehouse directly. The RLS on the semantic model does not limit their access to the Lakehouse. The data is only limited when viewing reports using that semantic model. This is illustrated in the workspace lineage view by the direction of the arrows.
If you give the user "Viewer" permission when you publish the report as a App (recommended to secure data), then they won't have any access to the underlying Lakehouse. The fixed identity (of someone who can view the Lakehouse data) is required for them to see any data with the semantic model.
This is the same as giving User A permission to your SQL database, then User A creates a semantic model and publishes the model giving Power BI service their credentials to access the data. Then User A shares the reports built from the model with User B (who doesn't have access to the SQL database) to see a only the data User A defined with RLS on the semantic model for User B.
In Direct Lake scenarios the change is now the data source can live in the workspace as the semantic model, so granting workspace permission may be more broad than intended. Just like if User B did have access to the SQL database, then while they may be limited in the reports shared with them by User A, they may not be limited when they go to the SQL database directly.
To change to fixed identity:
Go to the workspace, find the semantic model, and in the context menu choose Settings.
Expand Gateway and cloud connections.
In the drop down you should see "Single Sign-On". Choose New connection.
Enter the name, choose OAuth 2, click Edit credentials (to add your credentials), then Create.
5. That should put you back in the settings page. Important step! Again expand Gateway and cloud connections and change it to the new connection you just created!
6. Click Apply.
7. Finally, go back to the workspace and refresh the semantic model.
Wonderful, now let's add row-level security (RLS)!
1. Go to the Fabric workspace, right-click the semantic model, and choose Open data model. If it's greyed out, be sure to check your workspace settings.
2. To add the RLS, go to Manage roles from the ribbon or right-clicking the Roles section in Model Explorer.
3. Click "New", give the Role a name. Here I called mine RLS for Australia.
4. Click on the table you want to limit by RLS, here I picked the geo table.
5. Now I can specify a column and value to limit it by. I choose Country as the column, then limited it to Australia.
6. I could Switch to DAX editor instead too by clicking that button.
7. Save the Role.
8. To assign who is limited by this RLS, go to the Assign tab. Here I also added Demo account as my audience to be limited to only seeing data for Australia. Then Save and Close.
And that's it! The UI here makes it super easy to set up and assign in one dialog.
Alternative method, added before the RLS UI was available in web modeling. This takes advantage of the XMLA endpoint and external tools, specifically Tabular Editor 2. You can also use Tabular Editor 3 or XMLA directly.
Open Tabular Editor 2.
Connect to the workspace (use the workspace connection you copied) and choose the semantic model you want to add RLS to (for this example I use the duplicate one with XMLA at the end, DemoRLSXMLA).
Right-click Roles and choose New Role.
Give the role a helpful name, for my example I am limiting the geography table to Australia so I name it "RLS for Australia".
In the properties pane, expand Row Level Security and enter the DAX formula for the appropriate table. Here I pick 'geo' table and to limit to Australia I use the DAX formula: [Country] = "Australia".
6. Scroll down a bit more and set the Model Permission to "Read".
7. I can set up the Members (who the RLS will apply to) here but I found I wasn't quite sure what to put for the values so I chose the easier path of using the Power BI service to do that part. I save the model (CTRL+S). Then close Tabular Editor 2.
8. Then I go to the Power BI service workspace and in the context menu of the semantic model (again for my demo it's RLSDemoXMLA) I choose Security. In the Security section I can simply add in another user, in my case my Demo user.
9. If you are curious to see what this Members addition does look like in Tabular Editor 2, like I did, you can simply open Tabular Editor 2 and open the model again. Then you can see how it was populated.
Let's do some testing of the RLS:
The View as is not available in web modeling, but you can still utilize this feature once you have created a report and published it to the same workspace.
1. Go back to the Fabric workspace, right-click the semantic model, then choose Security.
2 Now you should see the Row-Level Security page.
3 It is very subtle, but if you hover over the role, in my case, RLS for Australia (1), ellipsis will appear with the option to Test as role!
4. Click this and if you see an error, then make sure you:
a) Set up the semantic model as fixed identity instead of single-sign on.
b) Have a report created on this semantic model in the workspace.
c) And finally, if those are both ok, simply refresh the page and try again! (small bug!)
5. Now you should see the report filtered correctly!
6. At the top you can change which report you want to check.
7. And you can even change to a different role.
Everything looks like it's working correctly!
Now to share and test this RLS with Demo account:
The way you share the report matters. Giving the audience viewer role on the workspace may be sharing too much, as if that is where the Lakehouse is, they can now go to the Lakehouse and see all the rows of data. They will still be limited when they view the report, but it's essentially a gate without a fence. Giving the audience viewer permission via the organizational app means you can now be specific on what reports in the workspace they can see as well, which gives your gate a nice fence.
I created a report in the Power BI service (I could have created it in Power BI Desktop with a live connection and published the report too).
2. I now share my report as in a Power BI app.
This way I can specify the report or reports.
And share it with just Demo user or the whole organization.
This ensures viewer access to the report only, through the semantic model that I set up the RLS on, and not the Lakehouse directly (where they are not limited on what they can see). For them to see any data in the report, the semantic model should be accessing the data in the Lakehouse using fixed identity as noted above, as the semantic model needs credentials of someone who does have access to the data.
Alternatively, I could add my Demo user account to the workspace as a Viewer. As a viewer, Demo will see the report limited per the RLS rules on the semantic model. In this case, only seeing Australia's data. But especially for Direct Lake, this is not recommended. The Lakehouse is also an artifact in the same workspace. This means Demo can go to the Lakehouse and see data for all countries, if they wanted to. The semantic model only impacts downstream artifacts, such as reports. Upstream artifacts are not limited by the semantic model RLS. Other workspace roles, such as Member, Contributor, and Admin, can edit the workspace semantic models, so users with those roles are also not limited by RLS.
I can give the published app link to my Demo account.
3. Now if I log in as Demo and view the same report using the app link, I see only Australia!
If they instead see this error "You don't have permission to view the content of Direct Lake table", then you may not have changed the semantic model connection to a fixed identity! See this section above.
So that is it! I have set up RLS on my Direct Lake semantic model!
Now some additional things to keep in mind.
The RLS is only applied to the semantic model and its downstream reports (check the lineage view). If I added Demo to the workspace as viewer, they can go to the Lakehouse or SQL analytics endpoint and still see all countries data. I would need to set up additional RLS at that layer to limit what they see there. Alternatively, I should publish the report in an app and give Demo viewer permission only to the report as part of that process, so they can't access the Lakehouse directly.
If RLS is set up for Demo at the upstream layer of the SQL analytics endpoint, then unfortunately Direct Lake will fallback to DirectQuery. Not only can the report be slower but also keep in mind that the RLS/OLS defined at the SQL Analytics Endpoint will apply too, in addition to the semantic model RLS.
The default connection from the Direct Lake semantic model to the Lakehouse is single sign on. This means the person looking at the report will access the Lakehouse tables using their credentials. You can change this to be a fixed identity. See instructions at https://learn.microsoft.com/en-us/power-bi/enterprise/directlake-fixed-identity. Fixed identity is when the model owner goes into the semantic model settings and specifies their credentials to be used no matter who is looking at the report. The RLS in the semantic model will still know who is looking at the report and apply the semantic model RLS rules for that user. But data will be accessed with the fixed identity credentials to load the visuals from the Lakehouse.
In this example I filtered the Geo dimension table, that is, a table that has a relationship to another table in your model. If, after doing that, your visuals show a higher total than expected (the total is not filtered from the other table) then check the relationship between the tables. You want to check the Security Filter Behavior and make sure it is set to One Direction or Both Directions, not None. None will ignore the RLS you just set for some relationship types! You can set the Security Filter Behavior in Tabular Editor by clicking the relationship and looking in the properties section. You can also check it with a DAX query via DAX studio, SSMS, or Tabular Editor 3 for Direct Lake with this INFO.Relationships DAX query. And DAX query view for other semantic models created in Power BI Desktop.