row level security

34 Topics

For Dynamic Row Level Security do I still need to add members on power BI service?
How can I fully implement Dynamic Row-Level Security (RLS) in Power BI without having to manually add users to the role in the Power BI Service under Semantic Model-> More options → Security → Add members? Context: I've implemented Dynamic RLS using a SQL database that contains user emails, and I'm using the USERPRINCIPALNAME() DAX function to filter data based on the logged-in user. This works as expected in Power BI Desktop using the "View as Role" feature. However, in the Power BI Service, some users are unable to see the filtered data unless I manually add them to the RLS role. Since the filtering logic is already dynamic and based on the user's email, I was hoping to avoid this manual step. Is there a recommended workaround or best practice to automate this process and ensure users are recognized by the RLS logic without needing to be explicitly added to the role? under Semantic Model -> More options → Security → Add members?
Solved
ananda31
1 year ago Place Report Server
4KViews
2likes
8Comments
Test Row Level Security with each of multiple reports sharing a dataset in a premium workspace
I have a dataset which draws data from many Excel workbooks which are all saved on sharepoint (we are moving toward having direct access from Power Query - in Excel or PBI, to DWs on our systems, but that's another story). I then use that same dataset for several other reports. In the service where these reports and the dataset are saved to a premium workspace it is possible to test the RLS. However, I have no choice as to which report the RLS is tested with and no way of changing from whatever report the service seems to decide it wants to use to test with to another report. I can probably delete the reports I don't want to test from the workspace so that testing defaults to use the only report there, but, that seems unsatisfactory. Is there a better way?
androo
1 year ago Place Desktop
5.7KViews
1like
10Comments
User IP Address Row Level Security
Hi all, I have a situation where I want to restrict what a user (sales rep) sees based on a site location. For example, if the user logs on from home or Site A, they see all sales; if they log on from Site B, they only see their sales. It's a case where the user is being mentored into a management role, but when at Site B, they don't want prying eyes (open office) to notice that they have more access than everyone else. I was wondering if I could somehow get the IP address where they are logged on, and I could restrict what they see. Any ideas? Thanks Steve
Steve_M
1 year ago Place Report Server
837Views
0likes
1Comment
Manage Security Roles - Row Level Security summary figures
Hi all, My dashboard shows sales and call figures for by postcode, territory, region and national levels. I have assigned Sales reps who can only view sales and calls by their territory, whilsts Regional Sales reps can view sales and calls by their region and the split by territory whilst a select group can view all sales and calls by each region and territory. Is there a way where the Sales rep can still only view their territory but can see the total for their region and the total national figure, and the same for Regional sales reps, can they view their full region breakdown but also the national roll up? The purpose is so that each viewer can see how they are comparing to 1. National Figures and 2. Their Region. Thank you in advance!!
CPDav
2 years ago Place Report Server
747Views
0likes
1Comment
Dynamic RLS with multiple tables
Hi there. I have a reasonably complicated dynamic RLS which i am trying to resolve through dax. Attached is the schema. The premise is the Id in the user table is the USERRPINCIPALNAME The user has a a list of companies they are allowed access to in the USERALLOWED table These companies have a relevant ID called InstanceId-CRMId that gets passed into a site list and on to the report to limit the data. The issue i am haivng is i do not want to have to use bidirectional relationships There iaremany to many relationships Performance is poor I cannot seem to get the RLS to properly work and the sites table isnt filtering when I add ID = USERPRINCIALNAME() in the user table. Can someone suggest specifc dax for this use case please??? I have looked at some RADACAD posts on this example but can't work it out. Thanks in advance!!
Solved
debenaire
2 years ago Place DAX Commands and Tips
2.5KViews
0likes
5Comments
RLS
Hi, Need your assistance. I currently have a RLS that restricts access for viewing employees information out of the base location. Typically the Leads can view details based on Team ID and base location. And now I have a new requirement where I want to share the details of an employee who is out of the location (team id) but share a common work ID. Example: an employee with X team ID and working on a different project out of his base location. So he has 2 work id's say it as A and B where represent the work ID of base location and B out side of base location. Because of my current RLS the Lead M is not ablebto view the details of B and X employee. I tried to create some intermediate tables which work id's and matching team codes. Here if I hard-coded the work ID the RLS is working but not sure how to make it dynamic. So that when a Lead login userprincipalname() should be checked against the Work ID and display the Offshore employees along with base location else the normal RLS will work as is. Can you please help me on this. Thanks, Akash
Solved
akash0800-pbi1
2 years ago Place DAX Commands and Tips
1.3KViews
0likes
3Comments
How to do Row Level Security in PBI Report Server using live connection from SSAS cube
The data in SSAS cube. The cube has 1 flat table. No dimensions. All the relevant data is in this table. The reports are housed in the Power BI Report Server The table has a column called "ClientName" which lists the clients. The ClientName column has values like APPLE, GOOGLE, REDDIT etc. There are a about 8 clients like these. The goal of RLS is that we have a common report to be viewed by all users at all the clients' companies but the people at APPLE should only see data for the rows that have ClientName as Apple, and so on. We created 8 Active Directory groups for the 8 clients. And users from the client companies were added appropriately to them. For the actual RLS these are the steps I have followed. 1. Created Roles on the SSAS cube using SQL Server Management Studio. 2. Assigned only 'Read' permissions to the role 3. In the members tab, added the appropriate AD group to the role. 4. In the Filters tab, assigned the filter using DAX. For example for the Apple role, the expression was ='TABLENAME'[ClientName]="APPLE" A sample report was created with the ClientName column in a matrix visual. This report was uploaded to the Power BI report Server. In internal testing, this worked perfectly. We created a test cube that was identical to the production cube. Added a couple people from our own company to each of these AD groups. We assigned these AD groups to roles in the test cube. They were able to see only the ClientName assigned to them. We then tried testing with actual client users. Created a test report with a single visual as above with the report connected to the actual production cube. But they were able to see all the values in the column. I then tried to repicate the initial test by asking the initial internal users to test the first sample report. (Connected to test cube). But they were able to see all values in the column as well. Whenever I do "Analyse in Excel" in Visual Studio, I get the intended results in all the tests. (RLS is applied) Both on the test cube and the production cube I'm not sure what I'm doing wrong. Some more information: In Manage options for the report, Data Sources > Credentials > Authentication Type is Windows Authentication. And the option "use the following credentials" is selected. A username and password has to be entered in the field there. This was provided by the admin team. I'm not sure what the use of this is but thought I should mention. In Manage options for the report, Data Sources > Connection type is Analysis Services. The Connection String is appropriately edited as below Data Source=SERVERNAME;InitialCAtalog=CUBENAME;Provider=MSOLAP.8;IntegratedSecurity=SSPI;Impersonation Level=Impersonate; As far as I know, none of the users have been added to any of the administrators/developers AD groups. I'm not sure what I'm doing wrong. Please advise. Thanks
Solved
rincewind
3 years ago Place Report Server
3.6KViews
0likes
7Comments
Row Level Security - Multiple Filter Criteria
I am trying to use RLS to filter some data according to a table which stores users territory information. The territory information is a table ("UserTerritories") consisting of both a Country and a Territory. Both of these fields can be wildcarded with a * to indicate they have access to either all countries or all territories within a country. Example "UserTerritories" information table shown here. This would indicate that the first user (User 100) has access to all of Germany plus the West and North territories in Spain. User 200 has access to the East and Paris territories in France. User 300 has access to all data. Username Country Territory 100 Germany * 100 Spain West 100 Spain North 200 France East 200 France Paris 300 * * Example "Account" data to be filtered using RLS: Account Country Territory A001 Germany West A002 Germany Berlin A003 Spain West A004 Spain North A005 Spain South A006 France North A007 France East A008 France West A009 France Paris I have been able to use SELECTCOLUMNS in my RLS rules (as shown below) to be able to filter either country or territory but this treats each of these fields individually rather than as a combination. What this means is that if a user's territory rules has a * for one of the territory fields then it would include all territories for any of the countries they have access to and not just the one specific country. var UserCountrySet = SELECTCOLUMNS( FILTER( 'UserTerritories', [Username] = USERPRINCIPALNAME() ), "Country", [Country] ) var UserTerritorySet = SELECTCOLUMNS( FILTER( 'UserTerritories', [Username] = USERPRINCIPALNAME() ), "Territory", [Territory] ) return ("*" IN UserCountrySet || [Country] IN UserCountrySet) && ("*" IN UserTerritorySet || [Territory] IN UserTerritorySet) Can anyone provide any guidance how I can filter the list of Accounts by seeing if it matches ANY of the row entries (i.e. the Country & Territory combination) in my "UserTerritories" table please? Any help greatly appreciated. Thanks!
Solved
asm495
3 years ago Place DAX Commands and Tips
5.5KViews
0likes
2Comments
Row level Security on multiple dimension alias table
Hello All, We have a current setup of DW tables one fact which is connected to multiple dimension table, for an example lets take Fact Sales which is connected to multiple Dim Agents tables like Dim-Agent(Who sold the product) , Dim-Agent(who billed the product), Dim-Agent(who serviced the product). which is connected to the fact table different columns like agent_sold, agent_billed, agent_servd. The issue happens when i try to setup row level security for the Agent table, so i tried putting the below code to all the agent table in the row filter under Roles. ='Dim - Agent_sold'[Agent NT Login]=MID(USERNAME(),FIND("\",USERNAME(),1)+1,LEN(USERNAME())-FIND("\",USERNAME(),1)+1) when it is applied to only one table it works fine, but when i started applying to all the agent table it is doing like a inner join and showing the result. Kindly let me know how to handle this situation. Let me know if you need further information Thanks
Solved
maharaj_narayan
3 years ago Place DAX Commands and Tips
1.6KViews
0likes
4Comments
Hierarchical Row Level Security with Recursive Lookup
Hello all, in my data model, I am using dynamic row level security in a recursive hierarchy. It checks if the user has access to the organization or any parent organization and, if yes, shows the data record. Unfortunately, I cannot go beyond five levels (formula length limit) but need seven. Is it possible to make loop, recursion or list reduction in the formula, so that it just checks the whole path, regardless of the number of levels? This is my DAX-Formula at the moment: OR(OR(OR(OR( PATHITEMREVERSE(RELATED(Organizations[Org Path]), 1, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname()), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 2, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 3, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 4, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 5, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())) I imagine something like this (mixture of JavaScript and DAX): [calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())].reduce((found, nextId) -> OR(PATHCONTAINS("path", nextId), FALSE) Regards, Fabian
Anonymous
3 years ago Place DAX Commands and Tips
444Views
0likes
0Comments