topic Re: RLS in Developer

RLS

Anonymous — Thu, 11 Jan 2024 14:23:28 GMT

Hi,
I am facing some issue while implementing the RLS for below fact table.

main_fact_table

job_no	year
IT123	2023
IT123467	2024
IT235	2023
IT2345	2024
IT345	2023
IT334	2024
IT3647	2023
IT3654	2024
IT6584	2023
IT6574	2024

user_access

user name	job_no	year
sourav	IT12	2023
jeet	IT6	2023
kunal	IT6	2024
sourav	IT3	2024
jeet	IT2	2023
kunal	IT1	2024
rupam	IT3	2024
rupam	IT6	2023

as i mentioned there is two table.

1)main_fact_table (this is the main facr table)
2)user_access (this is the details of user who has access to the perticuler data)

these two table are disconnected table hence they have no relation between them, and we are using the coloumn "job_no" (user_access) to decide the access for a perticuler user but in the "user_access" table column "job_no" dose not contain the full value we need to implement wildcard here for example in user_access one user named "sourav" has access to the row job_no in fact table whose value starts with "IT12" AND "IT3" so sourav should she all the rows in "main_fact_table" whose "job_no" starts with "IT12" AND "IT3" and corrosponding year also should take in considaration

in this case sourav should see in "main_fact_table" as below shown

job_no	year
IT123	2023
IT334	2024
IT3654	2024

Re: RLS

Anonymous — Wed, 10 Jan 2024 12:55:58 GMT

need help kindly find the solution

Re: RLS

Anonymous — Thu, 11 Jan 2024 08:20:54 GMT

Hi @Anonymous ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a fuzy match table

Table = ADDCOLUMNS ( 'user_access', "job_no2", CALCULATE ( MAX ( 'main_fact_table'[job_no] ), FILTER ( 'main_fact_table', IFERROR ( SEARCH ( 'user_access'[job_no], 'main_fact_table'[job_no], 1, 0 ), 0 ) > 0 && 'main_fact_table'[year] = 'user_access'[year] ) ) )

2. Create a role and add a filter with the formula:

[job_no] IN CALCULATETABLE(VALUES('Table'[job_no2]),FILTER('Table','Table'[year]=[year]&&'Table'[user name]=USERNAME()))

Best Regards

Re: RLS

Anonymous — Thu, 11 Jan 2024 14:33:46 GMT

Thanks, for the effort but there is a small changes in the requirement

i want the third row as well for user named "sourav" to have access in the 'main_fact_table' cause in the 'user_access' table "sourav" has access to the "job_no" in 'main_fact_table' whose value starts with IT3 for the 'year'= 2024 as well

Final access for sourav i have attached in the screenshot.