{"id":259,"date":"2023-03-21T11:16:24","date_gmt":"2023-03-21T10:16:24","guid":{"rendered":"https:\/\/www.syntera.ch\/blog\/?p=259"},"modified":"2024-07-18T15:38:29","modified_gmt":"2024-07-18T13:38:29","slug":"static-and-dynamic-row-level-security-in-power-bi","status":"publish","type":"post","link":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/","title":{"rendered":"Static and dynamic Row-level-Security in Power BI"},"content":{"rendered":"\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-f56f613f wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\"><div class=\"wp-block-post-author has-medium-font-size\"><div class=\"wp-block-post-author__avatar\"><img alt='' src='https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=48&#038;d=mm&#038;r=g' srcset='https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&#038;d=mm&#038;r=g 2x' class='avatar avatar-48 photo' height='48' width='48' \/><\/div><div class=\"wp-block-post-author__content\"><p class=\"wp-block-post-author__byline\">LEAD BUSINESS INTELLIGENCE<\/p><p class=\"wp-block-post-author__name\">Dimitri B\u00fctikofer<\/p><\/div><\/div><\/div>\n\n\n\n<div class=\"wp-block-column is-vertically-aligned-top is-layout-flow wp-block-column-is-layout-flow\">\n<ul class=\"wp-block-outermost-social-sharing alignright has-small-icon-size has-icon-color is-style-logos-only is-content-justification-left is-layout-flex wp-container-outermost-social-sharing-is-layout-fa5e4718 wp-block-outermost-social-sharing-is-layout-flex\"><li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-linkedin has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2023%2F03%2F21%2Fstatic-and-dynamic-row-level-security-in-power-bi%2F&#038;title=Static%20and%20dynamic%20Row-level-Security%20in%20Power%20BI\" aria-label=\"Share on LinkedIn\" rel=\"noopener nofollow\" target=\"_blank\" class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M19.7,3H4.3C3.582,3,3,3.582,3,4.3v15.4C3,20.418,3.582,21,4.3,21h15.4c0.718,0,1.3-0.582,1.3-1.3V4.3 C21,3.582,20.418,3,19.7,3z M8.339,18.338H5.667v-8.59h2.672V18.338z M7.004,8.574c-0.857,0-1.549-0.694-1.549-1.548 c0-0.855,0.691-1.548,1.549-1.548c0.854,0,1.547,0.694,1.547,1.548C8.551,7.881,7.858,8.574,7.004,8.574z M18.339,18.338h-2.669 v-4.177c0-0.996-0.017-2.278-1.387-2.278c-1.389,0-1.601,1.086-1.601,2.206v4.249h-2.667v-8.59h2.559v1.174h0.037 c0.356-0.675,1.227-1.387,2.526-1.387c2.703,0,3.203,1.779,3.203,4.092V18.338z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tShare on LinkedIn\t\t<\/span>\n\t<\/a>\n<\/li>\n\n\n<li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-mail has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"mailto:?subject=Static%20and%20dynamic%20Row-level-Security%20in%20Power%20BI&#038;body=Static%20and%20dynamic%20Row-level-Security%20in%20Power%20BI%20&mdash;%20https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2023%2F03%2F21%2Fstatic-and-dynamic-row-level-security-in-power-bi%2F\" aria-label=\"Email this Page\"  class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M20,4H4C2.895,4,2,4.895,2,6v12c0,1.105,0.895,2,2,2h16c1.105,0,2-0.895,2-2V6C22,4.895,21.105,4,20,4z M20,8.236l-8,4.882 L4,8.236V6h16V8.236z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tEmail this Page\t\t<\/span>\n\t<\/a>\n<\/li>\n\n\n<li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-whatsapp has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"https:\/\/api.whatsapp.com\/send?text=Static%20and%20dynamic%20Row-level-Security%20in%20Power%20BI%20&mdash;%20https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2023%2F03%2F21%2Fstatic-and-dynamic-row-level-security-in-power-bi%2F\" aria-label=\"Share on WhatsApp\" rel=\"noopener nofollow\" target=\"_blank\" class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M 12.011719 2 C 6.5057187 2 2.0234844 6.478375 2.0214844 11.984375 C 2.0204844 13.744375 2.4814687 15.462563 3.3554688 16.976562 L 2 22 L 7.2324219 20.763672 C 8.6914219 21.559672 10.333859 21.977516 12.005859 21.978516 L 12.009766 21.978516 C 17.514766 21.978516 21.995047 17.499141 21.998047 11.994141 C 22.000047 9.3251406 20.962172 6.8157344 19.076172 4.9277344 C 17.190172 3.0407344 14.683719 2.001 12.011719 2 z M 12.009766 4 C 14.145766 4.001 16.153109 4.8337969 17.662109 6.3417969 C 19.171109 7.8517969 20.000047 9.8581875 19.998047 11.992188 C 19.996047 16.396187 16.413812 19.978516 12.007812 19.978516 C 10.674812 19.977516 9.3544062 19.642812 8.1914062 19.007812 L 7.5175781 18.640625 L 6.7734375 18.816406 L 4.8046875 19.28125 L 5.2851562 17.496094 L 5.5019531 16.695312 L 5.0878906 15.976562 C 4.3898906 14.768562 4.0204844 13.387375 4.0214844 11.984375 C 4.0234844 7.582375 7.6067656 4 12.009766 4 z M 8.4765625 7.375 C 8.3095625 7.375 8.0395469 7.4375 7.8105469 7.6875 C 7.5815469 7.9365 6.9355469 8.5395781 6.9355469 9.7675781 C 6.9355469 10.995578 7.8300781 12.182609 7.9550781 12.349609 C 8.0790781 12.515609 9.68175 15.115234 12.21875 16.115234 C 14.32675 16.946234 14.754891 16.782234 15.212891 16.740234 C 15.670891 16.699234 16.690438 16.137687 16.898438 15.554688 C 17.106437 14.971687 17.106922 14.470187 17.044922 14.367188 C 16.982922 14.263188 16.816406 14.201172 16.566406 14.076172 C 16.317406 13.951172 15.090328 13.348625 14.861328 13.265625 C 14.632328 13.182625 14.464828 13.140625 14.298828 13.390625 C 14.132828 13.640625 13.655766 14.201187 13.509766 14.367188 C 13.363766 14.534188 13.21875 14.556641 12.96875 14.431641 C 12.71875 14.305641 11.914938 14.041406 10.960938 13.191406 C 10.218937 12.530406 9.7182656 11.714844 9.5722656 11.464844 C 9.4272656 11.215844 9.5585938 11.079078 9.6835938 10.955078 C 9.7955938 10.843078 9.9316406 10.663578 10.056641 10.517578 C 10.180641 10.371578 10.223641 10.267562 10.306641 10.101562 C 10.389641 9.9355625 10.347156 9.7890625 10.285156 9.6640625 C 10.223156 9.5390625 9.737625 8.3065 9.515625 7.8125 C 9.328625 7.3975 9.131125 7.3878594 8.953125 7.3808594 C 8.808125 7.3748594 8.6425625 7.375 8.4765625 7.375 z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tShare on WhatsApp\t\t<\/span>\n\t<\/a>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Power BI enables developers to implement Row-level-security (RLS) in their reports, which is a powerful feature that allows you to restrict data access at the row level. With RLS, you can ensure that users only see the data that they are authorized to see. This feature is particularly useful in situations where you have sensitive data that should only be accessible to certain users or teams. In this blog, I will give you an introduction on how to create RLS roles, add new users to these roles and what the difference between dynamic and static RLS roles are. But before jumping into more details let&#8217;s have a general look at how RLS in Power BI works and why it is an essential feature for implementing business reporting solutions.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">How does RLS work?<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">At its core, RLS works by applying filters to your data and linking the filter statement to an RLS role. You can define these filter statements in Power BI in the form of DAX statements (e.g. Regions[Region] = &#8220;Switzerland&#8221;) to shrink down your DIM tables, which in turn reduces the rows available in your Fact table. In a second step, you can assign users or groups to your created RLS roles in the Power BI Service.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Why is RLS important?<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The importance of RLS revolves mainly around the possibility to meet security and compliance requirements. The most obvious reason why RLS roles are important is to ensure data security. By implementing RLS roles, you can limit access to sensitive data only to those users who have been granted permission to view it. This ensures that confidential information is not leaked or accessed by unauthorized users, protecting your business from data breaches or other security threats. Furthermore, RLS provides ways to meet compliance standards regarding data privacy regulations. Many industries have strict regulations around data access and privacy.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Implementation of RLS<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In the next few sections, we will create both static and dynamic RLS roles as well as discuss the pros and cons of both options. Furthermore, we will test roles in PBI desktop before publishing the report and assign users to the created RLS roles from within the PBI service. All examples are based on the AdventureWork Sales dataset which can be downloaded <a href=\"https:\/\/github.com\/microsoft\/powerbi-desktop-samples\/blob\/main\/AdventureWorks%20Sales%20Sample\/AdventureWorks%20Sales.pbix\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a> if you want to follow along.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Create a static RLS role<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s imagine the following scenario: We are building a sales report that will be tested in Germany and then rolled out globally. An important requirement is that the head of the respective country should only see the transactions in his country. We can achieve this by creating and assigning a static RLS role for the head of Germany. To do so we need to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <strong>Modeling<\/strong> tab in the <strong>Report view<\/strong> and select <strong>Manage roles<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"897\" height=\"206\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Manage-roles.png\" alt=\"\" class=\"wp-image-262\" style=\"width:897px;height:206px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Manage-roles.png 897w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Manage-roles-300x69.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Manage-roles-768x176.png 768w\" sizes=\"auto, (max-width: 897px) 100vw, 897px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click on <strong>Create<\/strong> and enter a name for the RLS role<\/li>\n\n\n\n<li>Under <strong>Tables<\/strong> go to the table you want to apply a filter statement on, click on the three dots, add filter and select the column you want to apply an RLS filter on. In our case, we used the table Sales Territory and the column Country.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"614\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role-1024x614.png\" alt=\"\" class=\"wp-image-263\" style=\"width:940px;height:563px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role-1024x614.png 1024w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role-300x180.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role-768x461.png 768w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role.png 1185w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Define the DAX statement for the filter you want to apply on your RLS role and click <strong>Save<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"614\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role2-1024x614.png\" alt=\"\" class=\"wp-image-265\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role2-1024x614.png 1024w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role2-300x180.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role2-768x461.png 768w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_RLS_role2.png 1185w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Testing a RLS role<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">It is important to test your RLS role after the creation to verify that it has the intended effect.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <strong>Modeling<\/strong> tab in the <strong>Report view<\/strong> and select <strong>View as<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"897\" height=\"208\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as.png\" alt=\"\" class=\"wp-image-267\" style=\"width:897px;height:208px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as.png 897w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as-300x70.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as-768x178.png 768w\" sizes=\"auto, (max-width: 897px) 100vw, 897px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the RLS role you created, in our case it is the role Head of Germany<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"487\" height=\"490\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_roles.png\" alt=\"\" class=\"wp-image-268\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_roles.png 487w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_roles-298x300.png 298w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_roles-150x150.png 150w\" sizes=\"auto, (max-width: 487px) 100vw, 487px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check the result on a visual, in our case a table showing the sum of sales per country (left picture: before applying RLS, right picture: after applying RLS)<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"224\" height=\"183\" data-id=\"271\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/RLS_test_before-1.png\" alt=\"\" class=\"wp-image-271\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"293\" height=\"218\" data-id=\"270\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/RLS_test_after.png\" alt=\"\" class=\"wp-image-270\"\/><\/figure>\n<\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Create a dynamic RLS role<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Static RLS roles are easy and fast to implement. However, if we want to expand our RLS logic for other countries we have to create a role for each country. In our scenario, the effort is manageable, since there are only six countries but requirements for RLS can grow fast. Perhaps after the rollout for the management we also want to enable all of our salespeople to consume our report. In this case, it would be necessary to restrict the access of each sales employee to only see their own sales transaction. Creating hundreds of RLS roles and especially managing them afterward in the case of position changes is not a viable solution with static RLS roles. Luckily there is an alternative, namely dynamic RLS roles, whereas filter statements can be based on the signed-in user, using their user principal name (UPN) to filter our data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To implement dynamic RLS we first need to create the support table that defines how the data should be filtered based on the currently signed in UPN. This support table can come from whatever source you like, even from the source system of your data. In our example, we will create the table with manually entered values. We will create a support table to enable dynamic RLS for each head of sales of the different countries.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In the <strong>Home<\/strong> tab click on <strong>Enter data<\/strong><\/li>\n\n\n\n<li>Create a table with two columns (CountryName and UPN) and rename the table<\/li>\n\n\n\n<li>Enter the values for the countries you want to implement RLS on<\/li>\n\n\n\n<li>In the UPN column enter the corresponding user principal name (for testing purposes you can enter your own UPN)<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"868\" height=\"499\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_support_table.png\" alt=\"\" class=\"wp-image-274\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_support_table.png 868w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_support_table-300x172.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_support_table-768x442.png 768w\" sizes=\"auto, (max-width: 868px) 100vw, 868px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">After creating the support table we have to define its relationship with the rest of the data model.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Under <strong>Model<\/strong> go to the <strong>Home<\/strong> tab and click on <strong>Manage relationships<\/strong><\/li>\n\n\n\n<li>Click on <strong>New&#8230;<\/strong> and choose the <strong>RLS_Support_Table <\/strong>and mark the CountryName column<\/li>\n\n\n\n<li>For the second table choose <strong>Sales Territory <\/strong>and mark the column Country<\/li>\n\n\n\n<li>Check the cardinality and cross filter direction and click on <strong>OK<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"881\" height=\"758\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_relationship.png\" alt=\"\" class=\"wp-image-275\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_relationship.png 881w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_relationship-300x258.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Create_relationship-768x661.png 768w\" sizes=\"auto, (max-width: 881px) 100vw, 881px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Now that the relationship for the support table is defined we can finally create a dynamic RLS role<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <strong>Modeling<\/strong> tab in the <strong>Report view<\/strong> and select <strong>Manage roles<\/strong><\/li>\n\n\n\n<li>Create a new role, select the RLS_Support_Table and define the DAX statement as follows: [UPN] = USERPRINCIPALNAME()\n<ul class=\"wp-block-list\">\n<li>Note: the DAX function USERPRINCIPALNAME() returns the UPN of the currently signed-in user<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"617\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/create_dynamic_role-1024x617.png\" alt=\"\" class=\"wp-image-276\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/create_dynamic_role-1024x617.png 1024w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/create_dynamic_role-300x181.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/create_dynamic_role-768x463.png 768w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/create_dynamic_role.png 1191w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Testing a dynamic RLS role<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Testing dynamic roles with your own user principal name works in the same way as with static roles. Furthermore, PBI gives you the option to test dynamic roles in the context of other UPNs. Let&#8217;s test if the access of the head of Australia is restricted correctly by testing the dynamic RLS role in the context of the user sarah@advworks.com<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <strong>Modeling<\/strong> tab in the <strong>Report view<\/strong> and select <strong>View as<\/strong><\/li>\n\n\n\n<li>Select the dynamic role you defined<\/li>\n\n\n\n<li>Select the checkbox for <strong>Other user<\/strong> and enter the UPN in the context in which you want to test the dynamic RLS role (in our case: sarah@advworks)<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"495\" height=\"499\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_dynamicrole.png\" alt=\"\" class=\"wp-image-277\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_dynamicrole.png 495w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_dynamicrole-298x300.png 298w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/View_as_dynamicrole-150x150.png 150w\" sizes=\"auto, (max-width: 495px) 100vw, 495px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check the result on a visual, in our case a table showing the sum of sales per country (left picture: before applying RLS, right picture: after applying RLS)<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-2 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"224\" height=\"183\" data-id=\"279\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/RLS_test_before-2.png\" alt=\"\" class=\"wp-image-279\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"406\" height=\"314\" data-id=\"278\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Dynamic_test_after.png\" alt=\"\" class=\"wp-image-278\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Dynamic_test_after.png 406w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Dynamic_test_after-300x232.png 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/figure>\n<\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Assigning RLS roles<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Now that we have created our RLS role the last step is to assign the corresponding users to them. To do so we need to switch to the Power BI Service<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>In Power BI Service go to the dataset of the published report, click on the three dots and then on <strong>Security<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"664\" height=\"578\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Assign_role.png\" alt=\"\" class=\"wp-image-282\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Assign_role.png 664w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/Assign_role-300x261.png 300w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the desired role and enter a user, user group or security group<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"292\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/assign_user.png\" alt=\"\" class=\"wp-image-283\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/assign_user.png 624w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/assign_user-300x140.png 300w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Static vs. dynamic RLS rules<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In our example, both static as well as dynamic RLS rules are viable solutions for our requirements. So you may wonder when to use what type of RLS rule. As always the answer depends heavily on the specific requirements you are dealing with. Let&#8217;s go over the pros and cons of both approaches:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Static RLS roles Pros:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Static RLS is easier to set up and maintain than dynamic row-level security, especially for simple scenarios.<\/li>\n\n\n\n<li>It allows for a simple (static) set of rule based filters that can be applied to all users of the role, making it easier to manage.<\/li>\n\n\n\n<li>It is more suitable for scenarios where the access rules don&#8217;t change frequently.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Static RLS roles Cons:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It is not very flexible and cannot accommodate complex access rules or scenarios.<\/li>\n\n\n\n<li>It requires creating separate roles for each user group, which can be cumbersome for a large number of different RLS roles.<\/li>\n\n\n\n<li>It requires manual updates to role membership when there are changes in user access (for example due to position changes).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Dynamic RLS roles Pros:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dynamic row-level security can handle complex access scenarios and rules.<\/li>\n\n\n\n<li>Due to the possibility to source your support table directly from your source system, you can setup a solution that adapts to changes in user roles and access permissions automatically<\/li>\n\n\n\n<li>It is more scalable than static row-level security for large numbers of different RLS roles.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Dynamic RLS roles Cons:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It requires more effort to set up than static row-level security.<\/li>\n\n\n\n<li>It can be more challenging to troubleshoot when there are issues with access permissions due to higher complexity.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">In conclusion: static RLS is suitable for simple scenarios where the access rules are straightforward and do not change frequently. On the other hand, dynamic row-level security is more appropriate for complex scenarios where the access rules need to be updated frequently and automatically.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power BI enables developers to implement Row-level-security (RLS) in their reports, which is a powerful feature that allows you to restrict data access at the row level. With RLS, you can ensure that users only see the data that they are authorized to see. This feature is particularly useful in situations where you have sensitive [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":286,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,25],"tags":[6,26],"class_list":["post-259","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-security","tag-power-bi","tag-row-level-security"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Static and dynamic Row-level-Security in Power BI - Syntera<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Static and dynamic Row-level-Security in Power BI - Syntera\" \/>\n<meta property=\"og:description\" content=\"Power BI enables developers to implement Row-level-security (RLS) in their reports, which is a powerful feature that allows you to restrict data access at the row level. With RLS, you can ensure that users only see the data that they are authorized to see. This feature is particularly useful in situations where you have sensitive [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/\" \/>\n<meta property=\"og:site_name\" content=\"Syntera\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-21T10:16:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-07-18T13:38:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/titelbild.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1169\" \/>\n\t<meta property=\"og:image:height\" content=\"700\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Dimitri B\u00fctikofer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dimitri B\u00fctikofer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/\",\"url\":\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/\",\"name\":\"Static and dynamic Row-level-Security in Power BI - Syntera\",\"isPartOf\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\"},\"datePublished\":\"2023-03-21T10:16:24+00:00\",\"dateModified\":\"2024-07-18T13:38:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.syntera.ch\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Static and dynamic Row-level-Security in Power BI\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\",\"url\":\"https:\/\/www.syntera.ch\/blog\/\",\"name\":\"Syntera\",\"description\":\"translating data into business value.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034\",\"name\":\"Dimitri B\u00fctikofer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g\",\"caption\":\"Dimitri B\u00fctikofer\"},\"sameAs\":[\"http:\/\/www.syntera.ch\"],\"url\":\"https:\/\/www.syntera.ch\/blog\/author\/dimitri\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Static and dynamic Row-level-Security in Power BI - Syntera","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/","og_locale":"en_US","og_type":"article","og_title":"Static and dynamic Row-level-Security in Power BI - Syntera","og_description":"Power BI enables developers to implement Row-level-security (RLS) in their reports, which is a powerful feature that allows you to restrict data access at the row level. With RLS, you can ensure that users only see the data that they are authorized to see. This feature is particularly useful in situations where you have sensitive [&hellip;]","og_url":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/","og_site_name":"Syntera","article_published_time":"2023-03-21T10:16:24+00:00","article_modified_time":"2024-07-18T13:38:29+00:00","og_image":[{"width":1169,"height":700,"url":"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2023\/03\/titelbild.png","type":"image\/png"}],"author":"Dimitri B\u00fctikofer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Dimitri B\u00fctikofer","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/","url":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/","name":"Static and dynamic Row-level-Security in Power BI - Syntera","isPartOf":{"@id":"https:\/\/www.syntera.ch\/blog\/#website"},"datePublished":"2023-03-21T10:16:24+00:00","dateModified":"2024-07-18T13:38:29+00:00","author":{"@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034"},"breadcrumb":{"@id":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.syntera.ch\/blog\/2023\/03\/21\/static-and-dynamic-row-level-security-in-power-bi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.syntera.ch\/blog\/"},{"@type":"ListItem","position":2,"name":"Static and dynamic Row-level-Security in Power BI"}]},{"@type":"WebSite","@id":"https:\/\/www.syntera.ch\/blog\/#website","url":"https:\/\/www.syntera.ch\/blog\/","name":"Syntera","description":"translating data into business value.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034","name":"Dimitri B\u00fctikofer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g","caption":"Dimitri B\u00fctikofer"},"sameAs":["http:\/\/www.syntera.ch"],"url":"https:\/\/www.syntera.ch\/blog\/author\/dimitri\/"}]}},"_links":{"self":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/comments?post=259"}],"version-history":[{"count":12,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/259\/revisions"}],"predecessor-version":[{"id":712,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/259\/revisions\/712"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media\/286"}],"wp:attachment":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media?parent=259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/categories?post=259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/tags?post=259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}