Dynamic Row Level Security Based on Contract

Question

Hi,I am trying to set up Dynamic Row Level Security for a&nbsp; large dashboard where each contract should only see their reporting metrcis.&nbsp; There are multiple people in a contract so I know it needs to be based on email but everyone should see their own contract only.I have set up a Users Table, Employee Table and obvioulsly data.&nbsp;I cannot seem to get the synxtax right so that I could view each dataset based upon the contract selected only.Can anyone help?&nbsp; I know I need two tables that I have to use the Userprincipalname function but I am struggling with the rest of this.Sales- DesiredNameEmail AddressHire DateContractEmployeeIDUNIQUE CONTRACT AND NAMESalesAA@test.com13-Jun-18ABC1A-ABC10EE@test.com29-Jul-19ABC5E-ABC125FF@test.com16-Dec-19ABC6F-ABC50KK@test.com23-Oct-19ABC11K-ABC75&nbsp;Employee TableNameEmailAddressHire DateEmployeeIDContractUnique IDAA@test.com14-Sep-981ABCA-ABCBB@test.com16-Nov-982CBDB-CBDCC@test.com3-Oct-143DEFC-DEFDD@test.com27-Nov-174GHID-GHIEE@test.com9-Mar-185ABCE-ABCFF@test.com29-Jun-016ABCF-ABC&nbsp;Users TableNameEmail AddressHire DateContractEmployeeIDUNIQUE CONTRACT AND NAMEAA@test.com13-Jun-18ABC1A-ABCBB@test.com18-Jun-18CBD2B-CBDCC@test.com25-Jan-21DEF3C-DEFDD@test.com25-Oct-17GHI4D-GHIEE@test.com29-Jul-19ABC5E-ABCFF@test.com16-Dec-19ABC6F-ABC

lbendlin · Answer

yes, use USERPRINCIPALNAME and apply the RLS on the outermost dimension table of your data model.&nbsp;&nbsp;
&nbsp;
What's the reason for having a user table and an employee table?

anonymous · Answer

I don't think I need both tables either but I had seen other examples where that worked. I can never get the userprincipalname to work.

Are you really just using one filter on the email? Do you have an example? Thanks!

lbendlin · Answer

The usual pitfalls with RLS are&nbsp;
- you have users with workspace roles above viewer =&gt; RLS is ineffective
- your data model links are pointing towards the RLS table =&gt; ditto.
&nbsp;
Other than that USERPRINCIPALNAME works "fine".
&nbsp;
Personal note:&nbsp; Do you really need RLS?&nbsp; Is your data sensitive? Because if not then all you are achieving is to irritate your users.

anonymous · Answer

Data is super sensitive.In this case, how you you set up the formula in the manage roles?&nbsp;&nbsp;

lbendlin · Answer

In the Employees table:
&nbsp;
[Email Address]=USERPRINCIPALNAME()

Forum Discussion

Dynamic Row Level Security Based on Contract

7 Replies

Related content

Dynamic Row level security

Dynamic Row level security issue

Dynamic Object-Level Security (OLS)

Dynamic Row Level Security

Implement Dynamic RLS with Azure Security Group

Name	Email Address	Hire Date	Contract	EmployeeID	UNIQUE CONTRACT AND NAME	Sales
A	[email protected]	13-Jun-18	ABC	1	A-ABC	10
E	[email protected]	29-Jul-19	ABC	5	E-ABC	125
F	[email protected]	16-Dec-19	ABC	6	F-ABC	50
K	[email protected]	23-Oct-19	ABC	11	K-ABC	75

Name	EmailAddress	Hire Date	EmployeeID	Contract	Unique ID
A	[email protected]	14-Sep-98	1	ABC	A-ABC
B	[email protected]	16-Nov-98	2	CBD	B-CBD
C	[email protected]	3-Oct-14	3	DEF	C-DEF
D	[email protected]	27-Nov-17	4	GHI	D-GHI
E	[email protected]	9-Mar-18	5	ABC	E-ABC
F	[email protected]	29-Jun-01	6	ABC	F-ABC